1 package body PV_REFERRALS_PKG as
2 /* $Header: pvreferralb.pls 120.3 2006/04/18 15:39:39 saarumug ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_REFERRAL_ID in NUMBER,
6 X_ACTUAL_CURRENCY_CODE in VARCHAR2,
7 X_BENEFIT_TYPE_CODE in VARCHAR2,
8 X_CUSTOMER_CONTACT_PHONE_TYPE in VARCHAR2,
9 X_CUSTOMER_CONTACT_EMAIL_ADDRE in VARCHAR2,
10 X_CUSTOMER_CONTACT_PHONE_EXT in VARCHAR2,
11 X_DECLINE_REASON_CODE in VARCHAR2,
12 X_ENTITY_TYPE in VARCHAR2,
13 X_ORDER_ID in NUMBER,
14 X_CLAIM_ID in NUMBER,
15 X_CLAIM_NUMBER in VARCHAR2,
16 X_EST_COMPENSATION_AMT in NUMBER,
17 X_CURRENCY_CODE in VARCHAR2,
18 X_ACTUAL_COMPENSATION_AMT in NUMBER,
19 X_STATUS_CHANGE_DATE in DATE,
20 X_DUPLICATE_CUSTOMER_FLAG in VARCHAR2,
21 X_PARTNER_CUST_ACCOUNT_ID in NUMBER,
22 X_CUSTOMER_COUNTRY in VARCHAR2,
23 X_CUSTOMER_CONTACT_TITLE in VARCHAR2,
24 X_CUSTOMER_CONTACT_FIRST_NAME in VARCHAR2,
25 X_CUSTOMER_CONTACT_LAST_NAME in VARCHAR2,
26 X_CUSTOMER_CONTACT_CNTRY_CODE in VARCHAR2,
27 X_CUSTOMER_CONTACT_AREA_CODE in VARCHAR2,
28 X_CUSTOMER_CONTACT_PHONE_NO in VARCHAR2,
29 X_CUSTOMER_PROVINCE in VARCHAR2,
30 X_CUSTOMER_POSTAL_CODE in VARCHAR2,
31 X_CUSTOMER_COUNTY in VARCHAR2,
32 X_ENTITY_ID_LINKED_TO in NUMBER,
33 X_OBJECT_VERSION_NUMBER in NUMBER,
34 X_BENEFIT_ID in NUMBER,
35 X_REFERRAL_CODE in VARCHAR2,
36 X_REFERRAL_STATUS in VARCHAR2,
37 X_PARTNER_ID in NUMBER,
38 X_PARTNER_CONTACT_RESOURCE_ID in NUMBER,
39 X_CUSTOMER_PARTY_ID in NUMBER,
40 X_CUSTOMER_ORG_CONTACT_ID in NUMBER,
41 X_CUSTOMER_CONTACT_PARTY_ID in NUMBER,
42 X_CUSTOMER_PARTY_SITE_ID in NUMBER,
43 X_CUSTOMER_NAME in VARCHAR2,
44 X_CUSTOMER_ADDRESS_TYPE in VARCHAR2,
45 X_CUSTOMER_ADDRESS1 in VARCHAR2,
46 X_CUSTOMER_ADDRESS2 in VARCHAR2,
47 X_CUSTOMER_ADDRESS3 in VARCHAR2,
48 X_CUSTOMER_ADDRESS4 in VARCHAR2,
49 X_ADDRESS_LINES_PHONETIC in VARCHAR2,
50 X_CUSTOMER_CITY in VARCHAR2,
51 X_CUSTOMER_STATE in VARCHAR2,
52 X_REFERRAL_NAME in VARCHAR2,
53 X_DESCRIPTION in VARCHAR2,
54 X_CREATION_DATE in DATE,
55 X_CREATED_BY in NUMBER,
56 X_LAST_UPDATE_DATE in DATE,
57 X_LAST_UPDATED_BY in NUMBER,
58 X_LAST_UPDATE_LOGIN in NUMBER,
59 X_ORG_ID in NUMBER,
60 X_SECURITY_GROUP_ID in NUMBER,
61 X_CUSTOMER_NAME_PRONOUNCIATION in VARCHAR2,
62 X_RETURN_REASON_CODE in VARCHAR2,
63 X_ATTRIBUTE_CATEGORY in VARCHAR2,
64 X_ATTRIBUTE1 in VARCHAR2,
65 X_ATTRIBUTE2 in VARCHAR2,
66 X_ATTRIBUTE3 in VARCHAR2,
67 X_ATTRIBUTE4 in VARCHAR2,
68 X_ATTRIBUTE5 in VARCHAR2,
69 X_ATTRIBUTE6 in VARCHAR2,
70 X_ATTRIBUTE7 in VARCHAR2,
71 X_ATTRIBUTE8 in VARCHAR2,
72 X_ATTRIBUTE9 in VARCHAR2,
73 X_ATTRIBUTE10 in VARCHAR2,
74 X_ATTRIBUTE11 in VARCHAR2,
75 X_ATTRIBUTE12 in VARCHAR2,
76 X_ATTRIBUTE13 in VARCHAR2,
77 X_ATTRIBUTE14 in VARCHAR2,
78 X_ATTRIBUTE15 in VARCHAR2,
79 X_ATTRIBUTE16 in VARCHAR2,
80 X_ATTRIBUTE17 in VARCHAR2,
81 X_ATTRIBUTE18 in VARCHAR2,
82 X_ATTRIBUTE19 in VARCHAR2,
83 X_ATTRIBUTE20 in VARCHAR2,
84 X_ATTRIBUTE21 in VARCHAR2,
85 X_ATTRIBUTE22 in VARCHAR2,
86 X_ATTRIBUTE23 in VARCHAR2,
87 X_ATTRIBUTE24 in VARCHAR2
88 ) is
89 cursor C is select ROWID from PV_REFERRALS_B
90 where REFERRAL_ID = X_REFERRAL_ID
91 ;
92 begin
93 insert into PV_REFERRALS_B (
94 ACTUAL_CURRENCY_CODE,
95 BENEFIT_TYPE_CODE,
96 REFERRAL_ID,
97 CUSTOMER_CONTACT_PHONE_TYPE,
98 CUSTOMER_CONTACT_EMAIL_ADDRESS,
99 CUSTOMER_CONTACT_PHONE_EXT,
100 DECLINE_REASON_CODE,
101 ENTITY_TYPE,
102 ORDER_ID,
103 CLAIM_ID,
104 CLAIM_NUMBER,
105 EST_COMPENSATION_AMT,
106 CURRENCY_CODE,
107 ACTUAL_COMPENSATION_AMT,
108 STATUS_CHANGE_DATE,
109 DUPLICATE_CUSTOMER_FLAG,
110 PARTNER_CUST_ACCOUNT_ID,
111 CUSTOMER_COUNTRY,
112 CUSTOMER_CONTACT_TITLE,
113 CUSTOMER_CONTACT_FIRST_NAME,
114 CUSTOMER_CONTACT_LAST_NAME,
115 CUSTOMER_CONTACT_CNTRY_CODE,
116 CUSTOMER_CONTACT_AREA_CODE,
117 CUSTOMER_CONTACT_PHONE_NO,
118 CUSTOMER_PROVINCE,
119 CUSTOMER_POSTAL_CODE,
120 CUSTOMER_COUNTY,
121 ENTITY_ID_LINKED_TO,
122 OBJECT_VERSION_NUMBER,
123 BENEFIT_ID,
124 REFERRAL_CODE,
125 REFERRAL_STATUS,
126 PARTNER_ID,
127 PARTNER_CONTACT_RESOURCE_ID,
128 CUSTOMER_PARTY_ID,
129 CUSTOMER_ORG_CONTACT_ID,
130 CUSTOMER_CONTACT_PARTY_ID,
131 CUSTOMER_PARTY_SITE_ID,
132 CUSTOMER_NAME,
133 CUSTOMER_ADDRESS_TYPE,
134 CUSTOMER_ADDRESS1,
135 CUSTOMER_ADDRESS2,
136 CUSTOMER_ADDRESS3,
137 CUSTOMER_ADDRESS4,
138 ADDRESS_LINES_PHONETIC,
139 CUSTOMER_CITY,
140 CUSTOMER_STATE,
141 CREATION_DATE,
142 CREATED_BY,
143 LAST_UPDATE_DATE,
144 LAST_UPDATED_BY,
145 LAST_UPDATE_LOGIN,
146 ORG_ID,
147 SECURITY_GROUP_ID,
148 CUSTOMER_NAME_PRONOUNCIATION,
149 RETURN_REASON_CODE,
150 ATTRIBUTE_CATEGORY,
151 ATTRIBUTE1,
152 ATTRIBUTE2,
153 ATTRIBUTE3,
154 ATTRIBUTE4,
155 ATTRIBUTE5,
156 ATTRIBUTE6,
157 ATTRIBUTE7,
158 ATTRIBUTE8,
159 ATTRIBUTE9,
160 ATTRIBUTE10,
161 ATTRIBUTE11,
162 ATTRIBUTE12,
163 ATTRIBUTE13,
164 ATTRIBUTE14,
165 ATTRIBUTE15,
166 ATTRIBUTE16,
167 ATTRIBUTE17,
168 ATTRIBUTE18,
169 ATTRIBUTE19,
170 ATTRIBUTE20,
171 ATTRIBUTE21,
172 ATTRIBUTE22,
173 ATTRIBUTE23,
174 ATTRIBUTE24
175 ) values (
176 X_ACTUAL_CURRENCY_CODE,
177 X_BENEFIT_TYPE_CODE,
178 X_REFERRAL_ID,
179 X_CUSTOMER_CONTACT_PHONE_TYPE,
180 X_CUSTOMER_CONTACT_EMAIL_ADDRE,
181 X_CUSTOMER_CONTACT_PHONE_EXT,
182 X_DECLINE_REASON_CODE,
183 X_ENTITY_TYPE,
184 X_ORDER_ID,
185 X_CLAIM_ID,
186 X_CLAIM_NUMBER,
187 X_EST_COMPENSATION_AMT,
188 X_CURRENCY_CODE,
189 X_ACTUAL_COMPENSATION_AMT,
190 X_STATUS_CHANGE_DATE,
191 X_DUPLICATE_CUSTOMER_FLAG,
192 X_PARTNER_CUST_ACCOUNT_ID,
193 X_CUSTOMER_COUNTRY,
194 X_CUSTOMER_CONTACT_TITLE,
195 X_CUSTOMER_CONTACT_FIRST_NAME,
196 X_CUSTOMER_CONTACT_LAST_NAME,
197 X_CUSTOMER_CONTACT_CNTRY_CODE,
198 X_CUSTOMER_CONTACT_AREA_CODE,
199 X_CUSTOMER_CONTACT_PHONE_NO,
200 X_CUSTOMER_PROVINCE,
201 X_CUSTOMER_POSTAL_CODE,
202 X_CUSTOMER_COUNTY,
203 X_ENTITY_ID_LINKED_TO,
204 X_OBJECT_VERSION_NUMBER,
205 X_BENEFIT_ID,
206 X_REFERRAL_CODE,
207 X_REFERRAL_STATUS,
208 X_PARTNER_ID,
209 X_PARTNER_CONTACT_RESOURCE_ID,
210 X_CUSTOMER_PARTY_ID,
211 X_CUSTOMER_ORG_CONTACT_ID,
212 X_CUSTOMER_CONTACT_PARTY_ID,
213 X_CUSTOMER_PARTY_SITE_ID,
214 X_CUSTOMER_NAME,
215 X_CUSTOMER_ADDRESS_TYPE,
216 X_CUSTOMER_ADDRESS1,
217 X_CUSTOMER_ADDRESS2,
218 X_CUSTOMER_ADDRESS3,
219 X_CUSTOMER_ADDRESS4,
220 X_ADDRESS_LINES_PHONETIC,
221 X_CUSTOMER_CITY,
222 X_CUSTOMER_STATE,
223 X_CREATION_DATE,
224 X_CREATED_BY,
225 X_LAST_UPDATE_DATE,
226 X_LAST_UPDATED_BY,
227 X_LAST_UPDATE_LOGIN,
228 X_ORG_ID,
229 X_SECURITY_GROUP_ID,
230 X_CUSTOMER_NAME_PRONOUNCIATION,
231 X_RETURN_REASON_CODE,
232 X_ATTRIBUTE_CATEGORY,
233 X_ATTRIBUTE1,
234 X_ATTRIBUTE2,
235 X_ATTRIBUTE3,
236 X_ATTRIBUTE4,
237 X_ATTRIBUTE5,
238 X_ATTRIBUTE6,
239 X_ATTRIBUTE7,
240 X_ATTRIBUTE8,
241 X_ATTRIBUTE9,
242 X_ATTRIBUTE10,
243 X_ATTRIBUTE11,
244 X_ATTRIBUTE12,
245 X_ATTRIBUTE13,
246 X_ATTRIBUTE14,
247 X_ATTRIBUTE15,
248 X_ATTRIBUTE16,
249 X_ATTRIBUTE17,
250 X_ATTRIBUTE18,
251 X_ATTRIBUTE19,
252 X_ATTRIBUTE20,
253 X_ATTRIBUTE21,
254 X_ATTRIBUTE22,
255 X_ATTRIBUTE23,
256 X_ATTRIBUTE24
257 );
258
259 insert into PV_REFERRALS_TL (
260 CREATION_DATE,
261 CREATED_BY,
262 REFERRAL_ID,
263 LAST_UPDATE_DATE,
264 LAST_UPDATED_BY,
265 LAST_UPDATE_LOGIN,
266 OBJECT_VERSION_NUMBER,
267 REFERRAL_NAME,
268 DESCRIPTION,
269 LANGUAGE,
270 SOURCE_LANG
271 ) select
272 X_CREATION_DATE,
273 X_CREATED_BY,
274 X_REFERRAL_ID,
275 X_LAST_UPDATE_DATE,
276 X_LAST_UPDATED_BY,
277 X_LAST_UPDATE_LOGIN,
278 X_OBJECT_VERSION_NUMBER,
279 X_REFERRAL_NAME,
280 X_DESCRIPTION,
281 L.LANGUAGE_CODE,
282 userenv('LANG')
283 from FND_LANGUAGES L
284 where L.INSTALLED_FLAG in ('I', 'B')
285 and not exists
286 (select NULL
287 from PV_REFERRALS_TL T
288 where T.REFERRAL_ID = X_REFERRAL_ID
289 and T.LANGUAGE = L.LANGUAGE_CODE);
290
291 open c;
292 fetch c into X_ROWID;
293 if (c%notfound) then
294 close c;
295 raise no_data_found;
296 end if;
297 close c;
298
299 end INSERT_ROW;
300
301 procedure LOCK_ROW (
302 X_REFERRAL_ID in NUMBER,
303 X_ACTUAL_CURRENCY_CODE in VARCHAR2,
304 X_BENEFIT_TYPE_CODE in VARCHAR2,
305 X_CUSTOMER_CONTACT_PHONE_TYPE in VARCHAR2,
306 X_CUSTOMER_CONTACT_EMAIL_ADDRE in VARCHAR2,
307 X_CUSTOMER_CONTACT_PHONE_EXT in VARCHAR2,
308 X_DECLINE_REASON_CODE in VARCHAR2,
309 X_ENTITY_TYPE in VARCHAR2,
310 X_ORDER_ID in NUMBER,
311 X_CLAIM_ID in NUMBER,
312 X_CLAIM_NUMBER in VARCHAR2,
313 X_EST_COMPENSATION_AMT in NUMBER,
314 X_CURRENCY_CODE in VARCHAR2,
315 X_ACTUAL_COMPENSATION_AMT in NUMBER,
316 X_STATUS_CHANGE_DATE in DATE,
317 X_DUPLICATE_CUSTOMER_FLAG in VARCHAR2,
318 X_PARTNER_CUST_ACCOUNT_ID in NUMBER,
319 X_CUSTOMER_COUNTRY in VARCHAR2,
320 X_CUSTOMER_CONTACT_TITLE in VARCHAR2,
321 X_CUSTOMER_CONTACT_FIRST_NAME in VARCHAR2,
322 X_CUSTOMER_CONTACT_LAST_NAME in VARCHAR2,
323 X_CUSTOMER_CONTACT_CNTRY_CODE in VARCHAR2,
324 X_CUSTOMER_CONTACT_AREA_CODE in VARCHAR2,
325 X_CUSTOMER_CONTACT_PHONE_NO in VARCHAR2,
326 X_CUSTOMER_PROVINCE in VARCHAR2,
327 X_CUSTOMER_POSTAL_CODE in VARCHAR2,
328 X_CUSTOMER_COUNTY in VARCHAR2,
329 X_ENTITY_ID_LINKED_TO in NUMBER,
330 X_OBJECT_VERSION_NUMBER in NUMBER,
331 X_BENEFIT_ID in NUMBER,
332 X_REFERRAL_CODE in VARCHAR2,
333 X_REFERRAL_STATUS in VARCHAR2,
334 X_PARTNER_ID in NUMBER,
335 X_PARTNER_CONTACT_RESOURCE_ID in NUMBER,
336 X_CUSTOMER_PARTY_ID in NUMBER,
337 X_CUSTOMER_ORG_CONTACT_ID in NUMBER,
338 X_CUSTOMER_CONTACT_PARTY_ID in NUMBER,
339 X_CUSTOMER_PARTY_SITE_ID in NUMBER,
340 X_CUSTOMER_NAME in VARCHAR2,
341 X_CUSTOMER_ADDRESS_TYPE in VARCHAR2,
342 X_CUSTOMER_ADDRESS1 in VARCHAR2,
343 X_CUSTOMER_ADDRESS2 in VARCHAR2,
344 X_CUSTOMER_ADDRESS3 in VARCHAR2,
345 X_CUSTOMER_ADDRESS4 in VARCHAR2,
346 X_ADDRESS_LINES_PHONETIC in VARCHAR2,
347 X_CUSTOMER_CITY in VARCHAR2,
348 X_CUSTOMER_STATE in VARCHAR2,
349 X_REFERRAL_NAME in VARCHAR2,
350 X_DESCRIPTION in VARCHAR2,
351 X_ORG_ID in NUMBER,
352 X_SECURITY_GROUP_ID in NUMBER,
353 X_CUSTOMER_NAME_PRONOUNCIATION in VARCHAR2,
354 X_RETURN_REASON_CODE in VARCHAR2,
355 X_ATTRIBUTE_CATEGORY in VARCHAR2,
356 X_ATTRIBUTE1 in VARCHAR2,
357 X_ATTRIBUTE2 in VARCHAR2,
358 X_ATTRIBUTE3 in VARCHAR2,
359 X_ATTRIBUTE4 in VARCHAR2,
360 X_ATTRIBUTE5 in VARCHAR2,
361 X_ATTRIBUTE6 in VARCHAR2,
362 X_ATTRIBUTE7 in VARCHAR2,
363 X_ATTRIBUTE8 in VARCHAR2,
364 X_ATTRIBUTE9 in VARCHAR2,
365 X_ATTRIBUTE10 in VARCHAR2,
366 X_ATTRIBUTE11 in VARCHAR2,
367 X_ATTRIBUTE12 in VARCHAR2,
368 X_ATTRIBUTE13 in VARCHAR2,
369 X_ATTRIBUTE14 in VARCHAR2,
370 X_ATTRIBUTE15 in VARCHAR2,
371 X_ATTRIBUTE16 in VARCHAR2,
372 X_ATTRIBUTE17 in VARCHAR2,
373 X_ATTRIBUTE18 in VARCHAR2,
374 X_ATTRIBUTE19 in VARCHAR2,
375 X_ATTRIBUTE20 in VARCHAR2,
376 X_ATTRIBUTE21 in VARCHAR2,
377 X_ATTRIBUTE22 in VARCHAR2,
378 X_ATTRIBUTE23 in VARCHAR2,
379 X_ATTRIBUTE24 in VARCHAR2
380 ) is
381 cursor c is select
382 ACTUAL_CURRENCY_CODE,
383 BENEFIT_TYPE_CODE,
384 CUSTOMER_CONTACT_PHONE_TYPE,
385 CUSTOMER_CONTACT_EMAIL_ADDRESS,
386 CUSTOMER_CONTACT_PHONE_EXT,
387 DECLINE_REASON_CODE,
388 ENTITY_TYPE,
389 ORDER_ID,
390 CLAIM_ID,
391 CLAIM_NUMBER,
392 EST_COMPENSATION_AMT,
393 CURRENCY_CODE,
394 ACTUAL_COMPENSATION_AMT,
395 STATUS_CHANGE_DATE,
396 DUPLICATE_CUSTOMER_FLAG,
397 PARTNER_CUST_ACCOUNT_ID,
398 CUSTOMER_COUNTRY,
399 CUSTOMER_CONTACT_TITLE,
400 CUSTOMER_CONTACT_FIRST_NAME,
401 CUSTOMER_CONTACT_LAST_NAME,
402 CUSTOMER_CONTACT_CNTRY_CODE,
403 CUSTOMER_CONTACT_AREA_CODE,
404 CUSTOMER_CONTACT_PHONE_NO,
405 CUSTOMER_PROVINCE,
406 CUSTOMER_POSTAL_CODE,
407 CUSTOMER_COUNTY,
408 ENTITY_ID_LINKED_TO,
409 OBJECT_VERSION_NUMBER,
410 BENEFIT_ID,
411 REFERRAL_CODE,
412 REFERRAL_STATUS,
413 PARTNER_ID,
414 PARTNER_CONTACT_RESOURCE_ID,
415 CUSTOMER_PARTY_ID,
416 CUSTOMER_ORG_CONTACT_ID,
417 CUSTOMER_CONTACT_PARTY_ID,
418 CUSTOMER_PARTY_SITE_ID,
419 CUSTOMER_NAME,
423 CUSTOMER_ADDRESS3,
420 CUSTOMER_ADDRESS_TYPE,
421 CUSTOMER_ADDRESS1,
422 CUSTOMER_ADDRESS2,
424 CUSTOMER_ADDRESS4,
425 ADDRESS_LINES_PHONETIC,
426 CUSTOMER_CITY,
427 CUSTOMER_STATE,
428 ORG_ID,
429 SECURITY_GROUP_ID,
430 CUSTOMER_NAME_PRONOUNCIATION,
431 RETURN_REASON_CODE,
432 ATTRIBUTE_CATEGORY,
433 ATTRIBUTE1,
434 ATTRIBUTE2,
435 ATTRIBUTE3,
436 ATTRIBUTE4,
437 ATTRIBUTE5,
438 ATTRIBUTE6,
439 ATTRIBUTE7,
440 ATTRIBUTE8,
441 ATTRIBUTE9,
442 ATTRIBUTE10,
443 ATTRIBUTE11,
444 ATTRIBUTE12,
445 ATTRIBUTE13,
446 ATTRIBUTE14,
447 ATTRIBUTE15,
448 ATTRIBUTE16,
449 ATTRIBUTE17,
450 ATTRIBUTE18,
451 ATTRIBUTE19,
452 ATTRIBUTE20,
453 ATTRIBUTE21,
454 ATTRIBUTE22,
455 ATTRIBUTE23,
456 ATTRIBUTE24
457 from PV_REFERRALS_B
458 where REFERRAL_ID = X_REFERRAL_ID
459 for update of REFERRAL_ID nowait;
460 recinfo c%rowtype;
461
462 cursor c1 is select
463 REFERRAL_NAME,
464 DESCRIPTION,
465 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
466 from PV_REFERRALS_TL
467 where REFERRAL_ID = X_REFERRAL_ID
468 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
469 for update of REFERRAL_ID nowait;
470 begin
471 open c;
472 fetch c into recinfo;
473 if (c%notfound) then
474 close c;
475 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
476 app_exception.raise_exception;
477 end if;
478 close c;
479 if ( ((recinfo.ACTUAL_CURRENCY_CODE = X_ACTUAL_CURRENCY_CODE)
480 OR ((recinfo.ACTUAL_CURRENCY_CODE is null) AND (X_ACTUAL_CURRENCY_CODE is null)))
481 AND (recinfo.BENEFIT_TYPE_CODE = X_BENEFIT_TYPE_CODE)
482 AND ((recinfo.CUSTOMER_CONTACT_PHONE_TYPE = X_CUSTOMER_CONTACT_PHONE_TYPE)
483 OR ((recinfo.CUSTOMER_CONTACT_PHONE_TYPE is null) AND (X_CUSTOMER_CONTACT_PHONE_TYPE is null)))
484 AND ((recinfo.CUSTOMER_CONTACT_EMAIL_ADDRESS = X_CUSTOMER_CONTACT_EMAIL_ADDRE)
485 OR ((recinfo.CUSTOMER_CONTACT_EMAIL_ADDRESS is null) AND (X_CUSTOMER_CONTACT_EMAIL_ADDRE is null)))
486 AND ((recinfo.CUSTOMER_CONTACT_PHONE_EXT = X_CUSTOMER_CONTACT_PHONE_EXT)
487 OR ((recinfo.CUSTOMER_CONTACT_PHONE_EXT is null) AND (X_CUSTOMER_CONTACT_PHONE_EXT is null)))
488 AND ((recinfo.DECLINE_REASON_CODE = X_DECLINE_REASON_CODE)
489 OR ((recinfo.DECLINE_REASON_CODE is null) AND (X_DECLINE_REASON_CODE is null)))
490 AND ((recinfo.ENTITY_TYPE = X_ENTITY_TYPE)
491 OR ((recinfo.ENTITY_TYPE is null) AND (X_ENTITY_TYPE is null)))
492 AND ((recinfo.ORDER_ID = X_ORDER_ID)
493 OR ((recinfo.ORDER_ID is null) AND (X_ORDER_ID is null)))
494 AND ((recinfo.CLAIM_ID = X_CLAIM_ID)
495 OR ((recinfo.CLAIM_ID is null) AND (X_CLAIM_ID is null)))
496 AND ((recinfo.CLAIM_NUMBER = X_CLAIM_NUMBER)
497 OR ((recinfo.CLAIM_NUMBER is null) AND (X_CLAIM_NUMBER is null)))
498 AND ((recinfo.EST_COMPENSATION_AMT = X_EST_COMPENSATION_AMT)
499 OR ((recinfo.EST_COMPENSATION_AMT is null) AND (X_EST_COMPENSATION_AMT is null)))
500 AND ((recinfo.CURRENCY_CODE = X_CURRENCY_CODE)
501 OR ((recinfo.CURRENCY_CODE is null) AND (X_CURRENCY_CODE is null)))
502 AND ((recinfo.ACTUAL_COMPENSATION_AMT = X_ACTUAL_COMPENSATION_AMT)
503 OR ((recinfo.ACTUAL_COMPENSATION_AMT is null) AND (X_ACTUAL_COMPENSATION_AMT is null)))
504 AND (recinfo.STATUS_CHANGE_DATE = X_STATUS_CHANGE_DATE)
505 AND ((recinfo.DUPLICATE_CUSTOMER_FLAG = X_DUPLICATE_CUSTOMER_FLAG)
506 OR ((recinfo.DUPLICATE_CUSTOMER_FLAG is null) AND (X_DUPLICATE_CUSTOMER_FLAG is null)))
510 OR ((recinfo.CUSTOMER_CONTACT_TITLE is null) AND (X_CUSTOMER_CONTACT_TITLE is null)))
507 AND (recinfo.PARTNER_CUST_ACCOUNT_ID = X_PARTNER_CUST_ACCOUNT_ID)
508 AND (recinfo.CUSTOMER_COUNTRY = X_CUSTOMER_COUNTRY)
509 AND ((recinfo.CUSTOMER_CONTACT_TITLE = X_CUSTOMER_CONTACT_TITLE)
511 AND ((recinfo.CUSTOMER_CONTACT_FIRST_NAME = X_CUSTOMER_CONTACT_FIRST_NAME)
512 OR ((recinfo.CUSTOMER_CONTACT_FIRST_NAME is null) AND (X_CUSTOMER_CONTACT_FIRST_NAME is null)))
513 AND ((recinfo.CUSTOMER_CONTACT_LAST_NAME = X_CUSTOMER_CONTACT_LAST_NAME)
514 OR ((recinfo.CUSTOMER_CONTACT_LAST_NAME is null) AND (X_CUSTOMER_CONTACT_LAST_NAME is null)))
515 AND ((recinfo.CUSTOMER_CONTACT_CNTRY_CODE = X_CUSTOMER_CONTACT_CNTRY_CODE)
516 OR ((recinfo.CUSTOMER_CONTACT_CNTRY_CODE is null) AND (X_CUSTOMER_CONTACT_CNTRY_CODE is null)))
517 AND ((recinfo.CUSTOMER_CONTACT_AREA_CODE = X_CUSTOMER_CONTACT_AREA_CODE)
518 OR ((recinfo.CUSTOMER_CONTACT_AREA_CODE is null) AND (X_CUSTOMER_CONTACT_AREA_CODE is null)))
519 AND ((recinfo.CUSTOMER_CONTACT_PHONE_NO = X_CUSTOMER_CONTACT_PHONE_NO)
520 OR ((recinfo.CUSTOMER_CONTACT_PHONE_NO is null) AND (X_CUSTOMER_CONTACT_PHONE_NO is null)))
521 AND ((recinfo.CUSTOMER_PROVINCE = X_CUSTOMER_PROVINCE)
522 OR ((recinfo.CUSTOMER_PROVINCE is null) AND (X_CUSTOMER_PROVINCE is null)))
523 AND ((recinfo.CUSTOMER_POSTAL_CODE = X_CUSTOMER_POSTAL_CODE)
524 OR ((recinfo.CUSTOMER_POSTAL_CODE is null) AND (X_CUSTOMER_POSTAL_CODE is null)))
525 AND ((recinfo.CUSTOMER_COUNTY = X_CUSTOMER_COUNTY)
526 OR ((recinfo.CUSTOMER_COUNTY is null) AND (X_CUSTOMER_COUNTY is null)))
527 AND ((recinfo.ENTITY_ID_LINKED_TO = X_ENTITY_ID_LINKED_TO)
528 OR ((recinfo.ENTITY_ID_LINKED_TO is null) AND (X_ENTITY_ID_LINKED_TO is null)))
529 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
530 AND (recinfo.BENEFIT_ID = X_BENEFIT_ID)
531 AND (recinfo.REFERRAL_CODE = X_REFERRAL_CODE)
532 AND (recinfo.REFERRAL_STATUS = X_REFERRAL_STATUS)
533 AND (recinfo.PARTNER_ID = X_PARTNER_ID)
534 AND (recinfo.PARTNER_CONTACT_RESOURCE_ID = X_PARTNER_CONTACT_RESOURCE_ID)
535 AND ((recinfo.CUSTOMER_PARTY_ID = X_CUSTOMER_PARTY_ID)
536 OR ((recinfo.CUSTOMER_PARTY_ID is null) AND (X_CUSTOMER_PARTY_ID is null)))
537 AND ((recinfo.CUSTOMER_ORG_CONTACT_ID = X_CUSTOMER_ORG_CONTACT_ID)
538 OR ((recinfo.CUSTOMER_ORG_CONTACT_ID is null) AND (X_CUSTOMER_ORG_CONTACT_ID is null)))
539 AND ((recinfo.CUSTOMER_CONTACT_PARTY_ID = X_CUSTOMER_CONTACT_PARTY_ID)
540 OR ((recinfo.CUSTOMER_CONTACT_PARTY_ID is null) AND (X_CUSTOMER_CONTACT_PARTY_ID is null)))
541 AND ((recinfo.CUSTOMER_PARTY_SITE_ID = X_CUSTOMER_PARTY_SITE_ID)
542 OR ((recinfo.CUSTOMER_PARTY_SITE_ID is null) AND (X_CUSTOMER_PARTY_SITE_ID is null)))
543 AND (recinfo.CUSTOMER_NAME = X_CUSTOMER_NAME)
544 AND ((recinfo.CUSTOMER_NAME_PRONOUNCIATION = X_CUSTOMER_NAME_PRONOUNCIATION)
545 OR ((recinfo.CUSTOMER_NAME_PRONOUNCIATION is null) AND (X_CUSTOMER_NAME_PRONOUNCIATION is null)))
546 AND ((recinfo.RETURN_REASON_CODE = X_RETURN_REASON_CODE)
547 OR ((recinfo.RETURN_REASON_CODE is null) AND (X_RETURN_REASON_CODE is null)))
548 AND ((recinfo.CUSTOMER_ADDRESS_TYPE = X_CUSTOMER_ADDRESS_TYPE)
549 OR ((recinfo.CUSTOMER_ADDRESS_TYPE is null) AND (X_CUSTOMER_ADDRESS_TYPE is null)))
550 AND ((recinfo.CUSTOMER_ADDRESS1 = X_CUSTOMER_ADDRESS1)
551 OR ((recinfo.CUSTOMER_ADDRESS1 is null) AND (X_CUSTOMER_ADDRESS1 is null)))
552 AND ((recinfo.CUSTOMER_ADDRESS2 = X_CUSTOMER_ADDRESS2)
553 OR ((recinfo.CUSTOMER_ADDRESS2 is null) AND (X_CUSTOMER_ADDRESS2 is null)))
554 AND ((recinfo.CUSTOMER_ADDRESS3 = X_CUSTOMER_ADDRESS3)
555 OR ((recinfo.CUSTOMER_ADDRESS3 is null) AND (X_CUSTOMER_ADDRESS3 is null)))
556 AND ((recinfo.CUSTOMER_ADDRESS4 = X_CUSTOMER_ADDRESS4)
557 OR ((recinfo.CUSTOMER_ADDRESS4 is null) AND (X_CUSTOMER_ADDRESS4 is null)))
558 AND ((recinfo.ADDRESS_LINES_PHONETIC = X_ADDRESS_LINES_PHONETIC)
559 OR ((recinfo.ADDRESS_LINES_PHONETIC is null) AND (X_ADDRESS_LINES_PHONETIC is null)))
560 AND ((recinfo.CUSTOMER_CITY = X_CUSTOMER_CITY)
561 OR ((recinfo.CUSTOMER_CITY is null) AND (X_CUSTOMER_CITY is null)))
562 AND ((recinfo.CUSTOMER_STATE = X_CUSTOMER_STATE)
563 OR ((recinfo.CUSTOMER_STATE is null) AND (X_CUSTOMER_STATE is null)))
564 AND ((recinfo.ORG_ID = X_ORG_ID)
565 OR ((recinfo.ORG_ID is null) AND (X_ORG_ID is null)))
566 AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
567 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
568 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
569 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
570 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
571 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
572 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
573 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
574 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
575 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
576 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
577 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
578 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
579 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
583 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
580 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
581 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
582 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
584 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
585 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
586 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
587 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
588 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
589 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
590 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
591 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
592 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
593 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
594 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
595 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
596 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
597 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
598 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
599 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
600 AND ((recinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
601 OR ((recinfo.ATTRIBUTE16 is null) AND (X_ATTRIBUTE16 is null)))
602 AND ((recinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
603 OR ((recinfo.ATTRIBUTE17 is null) AND (X_ATTRIBUTE17 is null)))
604 AND ((recinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
605 OR ((recinfo.ATTRIBUTE18 is null) AND (X_ATTRIBUTE18 is null)))
606 AND ((recinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
607 OR ((recinfo.ATTRIBUTE19 is null) AND (X_ATTRIBUTE19 is null)))
608 AND ((recinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
609 OR ((recinfo.ATTRIBUTE20 is null) AND (X_ATTRIBUTE20 is null)))
610 AND ((recinfo.ATTRIBUTE21 = X_ATTRIBUTE21)
611 OR ((recinfo.ATTRIBUTE21 is null) AND (X_ATTRIBUTE21 is null)))
612 AND ((recinfo.ATTRIBUTE22 = X_ATTRIBUTE22)
613 OR ((recinfo.ATTRIBUTE22 is null) AND (X_ATTRIBUTE22 is null)))
614 AND ((recinfo.ATTRIBUTE23 = X_ATTRIBUTE23)
615 OR ((recinfo.ATTRIBUTE23 is null) AND (X_ATTRIBUTE23 is null)))
616 AND ((recinfo.ATTRIBUTE24 = X_ATTRIBUTE24)
617 OR ((recinfo.ATTRIBUTE24 is null) AND (X_ATTRIBUTE24 is null)))
618 ) then
619 null;
620 else
621 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
622 app_exception.raise_exception;
623 end if;
624
625 for tlinfo in c1 loop
626 if (tlinfo.BASELANG = 'Y') then
627 if ( (tlinfo.REFERRAL_NAME = X_REFERRAL_NAME)
628 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
629 ) then
630 null;
631 else
632 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
633 app_exception.raise_exception;
634 end if;
635 end if;
636 end loop;
637 return;
638 end LOCK_ROW;
639
640 procedure UPDATE_ROW (
641 X_REFERRAL_ID in NUMBER,
642 X_ACTUAL_CURRENCY_CODE in VARCHAR2,
643 X_BENEFIT_TYPE_CODE in VARCHAR2,
644 X_CUSTOMER_CONTACT_PHONE_TYPE in VARCHAR2,
645 X_CUSTOMER_CONTACT_EMAIL_ADDRE in VARCHAR2,
646 X_CUSTOMER_CONTACT_PHONE_EXT in VARCHAR2,
647 X_DECLINE_REASON_CODE in VARCHAR2,
648 X_ENTITY_TYPE in VARCHAR2,
649 X_ORDER_ID in NUMBER,
650 X_CLAIM_ID in NUMBER,
651 X_CLAIM_NUMBER in VARCHAR2,
652 X_EST_COMPENSATION_AMT in NUMBER,
653 X_CURRENCY_CODE in VARCHAR2,
654 X_ACTUAL_COMPENSATION_AMT in NUMBER,
655 X_STATUS_CHANGE_DATE in DATE,
656 X_DUPLICATE_CUSTOMER_FLAG in VARCHAR2,
657 X_PARTNER_CUST_ACCOUNT_ID in NUMBER,
658 X_CUSTOMER_COUNTRY in VARCHAR2,
659 X_CUSTOMER_CONTACT_TITLE in VARCHAR2,
660 X_CUSTOMER_CONTACT_FIRST_NAME in VARCHAR2,
661 X_CUSTOMER_CONTACT_LAST_NAME in VARCHAR2,
662 X_CUSTOMER_CONTACT_CNTRY_CODE in VARCHAR2,
663 X_CUSTOMER_CONTACT_AREA_CODE in VARCHAR2,
664 X_CUSTOMER_CONTACT_PHONE_NO in VARCHAR2,
665 X_CUSTOMER_PROVINCE in VARCHAR2,
666 X_CUSTOMER_POSTAL_CODE in VARCHAR2,
667 X_CUSTOMER_COUNTY in VARCHAR2,
668 X_ENTITY_ID_LINKED_TO in NUMBER,
669 X_OBJECT_VERSION_NUMBER in NUMBER,
670 X_BENEFIT_ID in NUMBER,
671 X_REFERRAL_CODE in VARCHAR2,
672 X_REFERRAL_STATUS in VARCHAR2,
673 X_PARTNER_ID in NUMBER,
674 X_PARTNER_CONTACT_RESOURCE_ID in NUMBER,
675 X_CUSTOMER_PARTY_ID in NUMBER,
676 X_CUSTOMER_ORG_CONTACT_ID in NUMBER,
677 X_CUSTOMER_CONTACT_PARTY_ID in NUMBER,
678 X_CUSTOMER_PARTY_SITE_ID in NUMBER,
679 X_CUSTOMER_NAME in VARCHAR2,
680 X_CUSTOMER_ADDRESS_TYPE in VARCHAR2,
681 X_CUSTOMER_ADDRESS1 in VARCHAR2,
682 X_CUSTOMER_ADDRESS2 in VARCHAR2,
683 X_CUSTOMER_ADDRESS3 in VARCHAR2,
684 X_CUSTOMER_ADDRESS4 in VARCHAR2,
685 X_ADDRESS_LINES_PHONETIC in VARCHAR2,
686 X_CUSTOMER_CITY in VARCHAR2,
687 X_CUSTOMER_STATE in VARCHAR2,
688 X_REFERRAL_NAME in VARCHAR2,
689 X_DESCRIPTION in VARCHAR2,
690 X_LAST_UPDATE_DATE in DATE,
691 X_LAST_UPDATED_BY in NUMBER,
692 X_LAST_UPDATE_LOGIN in NUMBER,
693 X_ORG_ID in NUMBER,
694 X_SECURITY_GROUP_ID in NUMBER,
695 X_CUSTOMER_NAME_PRONOUNCIATION in VARCHAR2,
696 X_RETURN_REASON_CODE in VARCHAR2,
697 X_ATTRIBUTE_CATEGORY in VARCHAR2,
698 X_ATTRIBUTE1 in VARCHAR2,
699 X_ATTRIBUTE2 in VARCHAR2,
700 X_ATTRIBUTE3 in VARCHAR2,
701 X_ATTRIBUTE4 in VARCHAR2,
702 X_ATTRIBUTE5 in VARCHAR2,
703 X_ATTRIBUTE6 in VARCHAR2,
704 X_ATTRIBUTE7 in VARCHAR2,
705 X_ATTRIBUTE8 in VARCHAR2,
706 X_ATTRIBUTE9 in VARCHAR2,
707 X_ATTRIBUTE10 in VARCHAR2,
708 X_ATTRIBUTE11 in VARCHAR2,
709 X_ATTRIBUTE12 in VARCHAR2,
710 X_ATTRIBUTE13 in VARCHAR2,
711 X_ATTRIBUTE14 in VARCHAR2,
712 X_ATTRIBUTE15 in VARCHAR2,
713 X_ATTRIBUTE16 in VARCHAR2,
714 X_ATTRIBUTE17 in VARCHAR2,
715 X_ATTRIBUTE18 in VARCHAR2,
716 X_ATTRIBUTE19 in VARCHAR2,
717 X_ATTRIBUTE20 in VARCHAR2,
718 X_ATTRIBUTE21 in VARCHAR2,
719 X_ATTRIBUTE22 in VARCHAR2,
720 X_ATTRIBUTE23 in VARCHAR2,
721 X_ATTRIBUTE24 in VARCHAR2
722 ) is
723 begin
724 update PV_REFERRALS_B set
725 ACTUAL_CURRENCY_CODE = X_ACTUAL_CURRENCY_CODE,
726 BENEFIT_TYPE_CODE = X_BENEFIT_TYPE_CODE,
727 CUSTOMER_CONTACT_PHONE_TYPE = X_CUSTOMER_CONTACT_PHONE_TYPE,
728 CUSTOMER_CONTACT_EMAIL_ADDRESS = X_CUSTOMER_CONTACT_EMAIL_ADDRE,
729 CUSTOMER_CONTACT_PHONE_EXT = X_CUSTOMER_CONTACT_PHONE_EXT,
730 DECLINE_REASON_CODE = X_DECLINE_REASON_CODE,
731 ENTITY_TYPE = X_ENTITY_TYPE,
732 ORDER_ID = X_ORDER_ID,
733 CLAIM_ID = X_CLAIM_ID,
734 CLAIM_NUMBER = X_CLAIM_NUMBER,
735 EST_COMPENSATION_AMT = X_EST_COMPENSATION_AMT,
736 CURRENCY_CODE = X_CURRENCY_CODE,
737 ACTUAL_COMPENSATION_AMT = X_ACTUAL_COMPENSATION_AMT,
738 STATUS_CHANGE_DATE = X_STATUS_CHANGE_DATE,
739 DUPLICATE_CUSTOMER_FLAG = X_DUPLICATE_CUSTOMER_FLAG,
740 PARTNER_CUST_ACCOUNT_ID = X_PARTNER_CUST_ACCOUNT_ID,
741 CUSTOMER_COUNTRY = X_CUSTOMER_COUNTRY,
742 CUSTOMER_CONTACT_TITLE = X_CUSTOMER_CONTACT_TITLE,
746 CUSTOMER_CONTACT_AREA_CODE = X_CUSTOMER_CONTACT_AREA_CODE,
743 CUSTOMER_CONTACT_FIRST_NAME = X_CUSTOMER_CONTACT_FIRST_NAME,
744 CUSTOMER_CONTACT_LAST_NAME = X_CUSTOMER_CONTACT_LAST_NAME,
745 CUSTOMER_CONTACT_CNTRY_CODE = X_CUSTOMER_CONTACT_CNTRY_CODE,
747 CUSTOMER_CONTACT_PHONE_NO = X_CUSTOMER_CONTACT_PHONE_NO,
748 CUSTOMER_PROVINCE = X_CUSTOMER_PROVINCE,
749 CUSTOMER_POSTAL_CODE = X_CUSTOMER_POSTAL_CODE,
750 CUSTOMER_COUNTY = X_CUSTOMER_COUNTY,
751 ENTITY_ID_LINKED_TO = X_ENTITY_ID_LINKED_TO,
752 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
753 BENEFIT_ID = X_BENEFIT_ID,
754 REFERRAL_CODE = X_REFERRAL_CODE,
755 REFERRAL_STATUS = X_REFERRAL_STATUS,
756 PARTNER_ID = X_PARTNER_ID,
757 PARTNER_CONTACT_RESOURCE_ID = X_PARTNER_CONTACT_RESOURCE_ID,
758 CUSTOMER_PARTY_ID = X_CUSTOMER_PARTY_ID,
759 CUSTOMER_ORG_CONTACT_ID = X_CUSTOMER_ORG_CONTACT_ID,
760 CUSTOMER_CONTACT_PARTY_ID = X_CUSTOMER_CONTACT_PARTY_ID,
761 CUSTOMER_PARTY_SITE_ID = X_CUSTOMER_PARTY_SITE_ID,
762 CUSTOMER_NAME = X_CUSTOMER_NAME,
763 CUSTOMER_NAME_PRONOUNCIATION = X_CUSTOMER_NAME_PRONOUNCIATION,
764 RETURN_REASON_CODE = X_RETURN_REASON_CODE,
765 CUSTOMER_ADDRESS_TYPE = X_CUSTOMER_ADDRESS_TYPE,
766 CUSTOMER_ADDRESS1 = X_CUSTOMER_ADDRESS1,
767 CUSTOMER_ADDRESS2 = X_CUSTOMER_ADDRESS2,
768 CUSTOMER_ADDRESS3 = X_CUSTOMER_ADDRESS3,
769 CUSTOMER_ADDRESS4 = X_CUSTOMER_ADDRESS4,
770 ADDRESS_LINES_PHONETIC = X_ADDRESS_LINES_PHONETIC,
771 CUSTOMER_CITY = X_CUSTOMER_CITY,
772 CUSTOMER_STATE = X_CUSTOMER_STATE,
773 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
774 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
775 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
776 ORG_ID = X_ORG_ID ,
777 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
778 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
779 ATTRIBUTE1 = X_ATTRIBUTE1,
780 ATTRIBUTE2 = X_ATTRIBUTE2,
781 ATTRIBUTE3 = X_ATTRIBUTE3,
782 ATTRIBUTE4 = X_ATTRIBUTE4,
783 ATTRIBUTE5 = X_ATTRIBUTE5,
784 ATTRIBUTE6 = X_ATTRIBUTE6,
785 ATTRIBUTE7 = X_ATTRIBUTE7,
786 ATTRIBUTE8 = X_ATTRIBUTE8,
787 ATTRIBUTE9 = X_ATTRIBUTE9,
788 ATTRIBUTE10 = X_ATTRIBUTE10,
789 ATTRIBUTE11 = X_ATTRIBUTE11,
790 ATTRIBUTE12 = X_ATTRIBUTE12,
791 ATTRIBUTE13 = X_ATTRIBUTE13,
792 ATTRIBUTE14 = X_ATTRIBUTE14,
793 ATTRIBUTE15 = X_ATTRIBUTE15,
794 ATTRIBUTE16 = X_ATTRIBUTE16,
795 ATTRIBUTE17 = X_ATTRIBUTE17,
796 ATTRIBUTE18 = X_ATTRIBUTE18,
797 ATTRIBUTE19 = X_ATTRIBUTE19,
798 ATTRIBUTE20 = X_ATTRIBUTE20,
799 ATTRIBUTE21 = X_ATTRIBUTE21,
800 ATTRIBUTE22 = X_ATTRIBUTE22,
801 ATTRIBUTE23 = X_ATTRIBUTE23,
802 ATTRIBUTE24 = X_ATTRIBUTE24
803 where REFERRAL_ID = X_REFERRAL_ID;
804
805 if (sql%notfound) then
806 raise no_data_found;
807 end if;
808
809 update PV_REFERRALS_TL set
810 REFERRAL_NAME = X_REFERRAL_NAME,
811 DESCRIPTION = X_DESCRIPTION,
812 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
813 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
814 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
815 SOURCE_LANG = userenv('LANG')
816 where REFERRAL_ID = X_REFERRAL_ID
817 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
818
819 if (sql%notfound) then
820 raise no_data_found;
821 end if;
822 end UPDATE_ROW;
823
824 procedure DELETE_ROW (
825 X_REFERRAL_ID in NUMBER
826 ) is
827 begin
828 delete from PV_REFERRALS_TL
829 where REFERRAL_ID = X_REFERRAL_ID;
830
831 if (sql%notfound) then
832 raise no_data_found;
833 end if;
834
835 delete from PV_REFERRALS_B
836 where REFERRAL_ID = X_REFERRAL_ID;
837
838 if (sql%notfound) then
839 raise no_data_found;
840 end if;
841 end DELETE_ROW;
842
843 procedure ADD_LANGUAGE
844 is
845 begin
846 delete from PV_REFERRALS_TL T
847 where not exists
848 (select NULL
849 from PV_REFERRALS_B B
850 where B.REFERRAL_ID = T.REFERRAL_ID
851 );
852
853 update PV_REFERRALS_TL T set (
854 REFERRAL_NAME,
855 DESCRIPTION
856 ) = (select
857 B.REFERRAL_NAME,
858 B.DESCRIPTION
859 from PV_REFERRALS_TL B
860 where B.REFERRAL_ID = T.REFERRAL_ID
861 and B.LANGUAGE = T.SOURCE_LANG)
862 where (
863 T.REFERRAL_ID,
864 T.LANGUAGE
865 ) in (select
866 SUBT.REFERRAL_ID,
867 SUBT.LANGUAGE
868 from PV_REFERRALS_TL SUBB, PV_REFERRALS_TL SUBT
869 where SUBB.REFERRAL_ID = SUBT.REFERRAL_ID
870 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
871 and (SUBB.REFERRAL_NAME <> SUBT.REFERRAL_NAME
872 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
873 ));
874
875 insert into PV_REFERRALS_TL (
876 CREATION_DATE,
877 CREATED_BY,
878 REFERRAL_ID,
879 LAST_UPDATE_DATE,
880 LAST_UPDATED_BY,
881 LAST_UPDATE_LOGIN,
882 OBJECT_VERSION_NUMBER,
883 REFERRAL_NAME,
884 DESCRIPTION,
885 LANGUAGE,
886 SOURCE_LANG
887 ) select /*+ ORDERED */
888 B.CREATION_DATE,
889 B.CREATED_BY,
890 B.REFERRAL_ID,
891 B.LAST_UPDATE_DATE,
892 B.LAST_UPDATED_BY,
893 B.LAST_UPDATE_LOGIN,
894 B.OBJECT_VERSION_NUMBER,
895 B.REFERRAL_NAME,
896 B.DESCRIPTION,
897 L.LANGUAGE_CODE,
898 B.SOURCE_LANG
899 from PV_REFERRALS_TL B, FND_LANGUAGES L
900 where L.INSTALLED_FLAG in ('I', 'B')
901 and B.LANGUAGE = userenv('LANG')
902 and not exists
903 (select NULL
904 from PV_REFERRALS_TL T
905 where T.REFERRAL_ID = B.REFERRAL_ID
906 and T.LANGUAGE = L.LANGUAGE_CODE);
907 end ADD_LANGUAGE;
908
909 end PV_REFERRALS_PKG;
[Home] [Help]