[Home] [Help]
PACKAGE BODY: APPS.HZ_CUSTOMER_INT
Source
1 PACKAGE BODY hz_customer_int AS
2 /*$Header: ARHCUSIB.pls 120.28.12020000.5 2013/03/28 23:25:16 awu ship $*/
3
4 -- The following varibles and types are added for bug 2460837.
5
6 g_created_by NUMBER;
7 g_last_update_login NUMBER;
8 g_last_updated_by NUMBER;
9 g_request_id NUMBER;
10 g_program_application_id NUMBER;
11 g_program_id NUMBER;
12
13 TYPE t_varchar500 IS TABLE OF VARCHAR2(400);
14 TYPE t_id IS TABLE OF NUMBER(15);
15 TYPE t_flag IS TABLE OF VARCHAR2(1);
16
17 FUNCTION get_cust_account_id (p_orig_system_customer_ref IN VARCHAR2)
18 RETURN NUMBER IS
19 l_cust_account_id NUMBER;
20 CURSOR c1 IS
21 SELECT cust_account_id
22 FROM hz_cust_accounts
23 WHERE orig_system_reference = p_orig_system_customer_ref;
24 BEGIN
25 OPEN c1;
26 FETCH c1 INTO l_cust_account_id;
27 CLOSE c1;
28 RETURN l_cust_account_id;
29 EXCEPTION
30 WHEN NO_DATA_FOUND THEN
31 RETURN NULL;
32 WHEN OTHERS THEN
33 RAISE;
34 END get_cust_account_id;
35
36 /* bug 4454799 - added argument for org_id below. */
37 FUNCTION get_cust_acct_site_id (p_orig_system_address_ref IN VARCHAR2,p_org_id IN NUMBER)
38 RETURN NUMBER IS
39 l_cust_acct_site_id NUMBER;
40 CURSOR c2 IS
41 SELECT cust_acct_site_id
42 FROM hz_cust_acct_sites_all -- bug 4454799
43 WHERE orig_system_reference = p_orig_system_address_ref
44 AND org_id = p_org_id; -- bug 4454799
45 BEGIN
46 OPEN c2;
47 FETCH c2 into l_cust_acct_site_id;
48 CLOSE c2;
49 RETURN l_cust_acct_site_id;
50 EXCEPTION
51 WHEN NO_DATA_FOUND THEN
52 RETURN NULL;
53 WHEN OTHERS THEN
54 RAISE;
55 END get_cust_acct_site_id;
56
57 FUNCTION get_cust_account_role_id (p_orig_system_contact_ref IN VARCHAR2)
58 RETURN NUMBER IS
59 l_cust_account_role_id NUMBER;
60 CURSOR c3 IS
61 SELECT cust_account_role_id
62 FROM hz_cust_account_roles
63 WHERE orig_system_reference = p_orig_system_contact_ref;
64 BEGIN
65 OPEN c3;
66 FETCH c3 INTO l_cust_account_role_id;
67 CLOSE c3;
68 RETURN l_cust_account_role_id;
69 EXCEPTION
70 WHEN NO_DATA_FOUND THEN
71 RETURN NULL;
72 WHEN OTHERS THEN
73 RAISE;
74 END get_cust_account_role_id;
75
76 FUNCTION get_prel_party_id (p_orig_system_contact_ref IN VARCHAR2)
77 RETURN NUMBER IS
78 l_prel_party_id NUMBER;
79 CURSOR c4 IS
80 SELECT party_id
81 FROM hz_cust_account_roles
82 WHERE orig_system_reference = p_orig_system_contact_ref;
83 BEGIN
84 OPEN c4;
85 FETCH c4 INTO l_prel_party_id;
86 CLOSE c4;
87 RETURN l_prel_party_id;
88 EXCEPTION
89 WHEN no_data_found THEN
90 RETURN NULL;
91 WHEN OTHERS THEN
92 RAISE;
93 END get_prel_party_id;
94
95 /* Bug Fix 2596570 */
96 FUNCTION get_contact_name(p_orig_system_contact_ref IN VARCHAR2,
97 p_orig_system_customer_ref IN VARCHAR2
98 )
99 RETURN VARCHAR2 IS
100 l_party_name VARCHAR2(360);
101 l_cont_name VARCHAR2(360);
102 l_org_name VARCHAR2(360);
103 l_party_number VARCHAR2(30);
104
105 CURSOR party_num IS
106 SELECT party_number
107 FROM hz_parties
108 WHERE orig_system_reference = 'PREL-'||p_orig_system_contact_ref;
109
110 CURSOR cont_name IS
111 SELECT party_name
112 FROM hz_parties
113 WHERE orig_system_reference = p_orig_system_contact_ref;
114
115 CURSOR org_name IS
116 SELECT party_name
117 FROM hz_parties
118 WHERE party_id = (select party_id
119 from hz_cust_accounts
120 where orig_system_reference = p_orig_system_customer_ref);
121
122 BEGIN
123 OPEN cont_name;
124 FETCH cont_name INTO l_cont_name;
125 CLOSE cont_name;
126
127 OPEN org_name;
128 FETCH org_name INTO l_org_name;
129 CLOSE org_name;
130
131 OPEN party_num;
132 FETCH party_num INTO l_party_number;
133 CLOSE party_num;
134
135 l_party_name := substrb(l_cont_name || '-'||l_org_name || '-' ||l_party_number,1,360 );
136
137 RETURN l_party_name;
138
139 EXCEPTION
140 WHEN no_data_found THEN
141 RETURN NULL;
142 WHEN OTHERS THEN
143 RAISE;
144 END get_contact_name;
145
146
147 /* Bug Fix 2596570 */
148 PROCEDURE update_party_prel_name(p_party_id IN NUMBER )
149 IS
150
151 l_party_name VARCHAR2(360);
152
153 CURSOR c_party_rels IS
154 SELECT r.party_id, r.object_id, o.party_name, r.subject_id, s.party_name,
155 rel.party_number, rel.party_name
156 FROM hz_relationships r, hz_parties s, hz_parties o, hz_parties rel
157 WHERE (r.subject_id = p_party_id OR r.object_id = p_party_id)
158 AND r.party_id IS NOT NULL
159 AND r.subject_table_name = 'HZ_PARTIES'
160 AND r.object_table_name = 'HZ_PARTIES'
161 AND r.directional_flag = 'F'
162 AND r.subject_id = s.party_id
163 AND r.object_id = o.party_id
164 AND r.party_id = rel.party_id;
165
166 TYPE IDlist IS TABLE OF NUMBER(15);
167 TYPE NAMElist IS TABLE OF HZ_PARTIES.PARTY_NAME%TYPE;
168 TYPE NUMBERlist IS TABLE OF HZ_PARTIES.PARTY_NUMBER%TYPE;
169
170 i_party_id IDlist;
171 i_object_id IDlist;
172 i_object_name NAMElist;
173 i_subject_id IDlist;
174 i_subject_name NAMElist;
175 i_party_number NUMBERlist;
176 i_party_name NAMElist;
177 l_dummy VARCHAR2(1);
178
179 BEGIN
180
181 OPEN c_party_rels;
182 FETCH c_party_rels BULK COLLECT INTO
183 i_party_id,i_object_id,i_object_name,i_subject_id,i_subject_name,
184 i_party_number, i_party_name;
185 CLOSE c_party_rels;
186
187 FOR i IN 1..i_party_id.COUNT LOOP
188 l_party_name := SUBSTRB(i_subject_name(i) || '-' ||
189 i_object_name(i) || '-' ||
190 i_party_number(i), 1, 360);
191
192 IF l_party_name <> i_party_name(i) THEN
193
194 UPDATE hz_parties
195 SET party_name = l_party_name
196 WHERE party_id = i_party_id(i);
197
198 END IF;
199
200 --recursively update those party relationships' name whose
201 --subject or object party might also be a party relationship.
202
203 update_party_prel_name(i_party_id(i));
204
205 END LOOP;
206
207 end update_party_prel_name;
208
209 /* bug 4454799 - added argument for org_id below. */
210 FUNCTION get_party_site_id (p_orig_system_address_ref IN VARCHAR2,p_org_id IN NUMBER)
211 RETURN NUMBER IS
212 l_party_site_id NUMBER;
213 CURSOR c5 IS
214 SELECT party_site_id
215 FROM hz_cust_acct_sites_all -- bug 4454799
216 WHERE orig_system_reference = p_orig_system_address_ref
217 AND org_id = p_org_id; -- bug 4454799
218 BEGIN
219 OPEN c5;
220 FETCH c5 INTO l_party_site_id;
221 CLOSE c5;
222 RETURN l_party_site_id;
223 EXCEPTION
224 WHEN NO_DATA_FOUND THEN
225 RETURN null;
226 WHEN OTHERS THEN
227 RAISE;
228 END get_party_site_id;
229
230 FUNCTION get_party_id(p_orig_system_customer_ref IN VARCHAR2)
231 RETURN NUMBER IS
232 l_party_id NUMBER;
233 CURSOR c6 IS
234 SELECT party_id
235 FROM hz_cust_accounts
236 WHERE orig_system_reference = p_orig_system_customer_ref;
237 BEGIN
238 OPEN c6;
239 FETCH c6 into l_party_id;
240 CLOSE c6;
241 RETURN l_party_id;
242 EXCEPTION
243 WHEN NO_DATA_FOUND THEN
244 RETURN null;
245 WHEN OTHERS THEN
246 RAISE;
247 END get_party_id;
248
249 FUNCTION validate_contact_ref (p_orig_system_contact_ref IN VARCHAR2)
250 RETURN VARCHAR2 IS
251 l_return_code VARCHAR2(5) := '';
252 l_party_id NUMBER;
253 p_customer_ref VARCHAR2(240);
254
255 CURSOR c7 IS
256 SELECT party_id
257 FROM hz_parties
258 WHERE orig_system_reference = p_orig_system_contact_ref;
259
260 CURSOR c2 IS
261 SELECT orig_system_customer_ref
262 FROM ra_customers_interface;
263 BEGIN
264 OPEN c7;
265 FETCH c7 INTO l_party_id;
266 CLOSE c7;
267
268 IF l_party_id IS NOT NULL THEN
269 l_return_code :='G4,';
270 END IF;
271
272 -- Bug 1487004
273 OPEN c2;
274
275 LOOP
276 FETCH c2 into p_customer_ref;
277 EXIT WHEN c2%NOTFOUND;
278 IF p_customer_ref = p_orig_system_contact_ref THEN
279 l_return_code := 'G4,';
280 EXIT;
281 END IF;
282 END LOOP;
283 CLOSE c2;
284
285 RETURN l_return_code;
286 EXCEPTION
287 WHEN NO_DATA_FOUND THEN
288 RETURN null;
289 WHEN OTHERS THEN
290 RAISE;
291 END validate_contact_ref;
292
293 FUNCTION get_language_code (p_language IN VARCHAR2) RETURN VARCHAR2 IS
294 l_language_code VARCHAR2(4);
295 CURSOR language_code IS
296 SELECT language_code
297 FROM fnd_languages
298 WHERE nls_language = p_language;
299 BEGIN
300 OPEN language_code;
301 FETCH language_code INTO l_language_code;
302 CLOSE language_code;
303 RETURN l_language_code;
304 EXCEPTION
305 WHEN NO_DATA_FOUND THEN
306 RETURN null;
307 WHEN OTHERS THEN
308 RAISE;
309 END get_language_code;
310
311 PROCEDURE validate_ccid (p_request_id NUMBER) IS
312 CURSOR auto_acc IS
313 SELECT site_use_code, i.gl_id_rec, i.gl_id_rev, i.gl_id_tax,
314 i.gl_id_freight, i.gl_id_clearing, i.gl_id_unbilled,
315 i.gl_id_unearned, i.interface_status
316 FROM ra_customers_interface i
317 WHERE i.request_id = p_request_id
318 AND nvl(i.validated_flag,'N') <> 'Y'
319 AND (gl_id_rec IS NOT NULL
320 OR gl_id_rev IS NOT NULL
321 OR gl_id_tax IS NOT NULL
322 OR gl_id_freight IS NOT NULL
323 OR gl_id_clearing IS NOT NULL
324 OR gl_id_unbilled IS NOT NULL
325 OR gl_id_unearned IS NOT NULL)
326 FOR UPDATE;
327
328 CURSOR boe IS
329 SELECT i.site_use_code, i.gl_id_unpaid_rec, i.gl_id_remittance,
330 i.gl_id_factor, i.interface_status
331 FROM ra_customers_interface i
332 WHERE i.request_id = p_request_id
333 AND nvl(i.validated_flag,'N') <> 'Y'
334 AND (gl_id_unpaid_rec IS NOT NULL
335 OR gl_id_remittance IS NOT NULL
336 OR gl_id_factor IS NOT NULL)
337 FOR UPDATE;
338
339 p_site_use_code VARCHAR2(30);
340 p_gl_id_rec NUMBER;
341 p_gl_id_rev NUMBER;
342 p_gl_id_tax NUMBER;
343 p_gl_id_freight NUMBER;
344 p_gl_id_clearing NUMBER;
345 p_gl_id_unbilled NUMBER;
346 p_gl_id_unearned NUMBER;
347 p_gl_id_unpaid_rec NUMBER;
348 p_gl_id_remittance NUMBER;
349 p_gl_id_factor NUMBER;
350 p_interface_status VARCHAR2(240);
351
352 BEGIN
353 OPEN auto_acc;
354 LOOP
355 FETCH auto_acc
356 INTO p_site_use_code, p_gl_id_rec, p_gl_id_rev, p_gl_id_tax,
357 p_gl_id_freight, p_gl_id_clearing, p_gl_id_unbilled,
358 p_gl_id_unearned, p_interface_status;
359
360 EXIT WHEN auto_acc%NOTFOUND;
361
362 IF p_site_use_code <> 'BILL_TO' THEN
363 p_interface_status := p_interface_status || 'm1,';
364 END IF;
365
366 IF p_gl_id_rec IS NOT NULL THEN
367 IF NOT fnd_flex_keyval.validate_ccid(
368 appl_short_name => 'SQLGL',
369 key_flex_code => 'GL#',
370 structure_number => arp_global.chart_of_accounts_id,
371 combination_id => p_gl_id_rec) THEN
372
373 p_interface_status := p_interface_status || 'm2,';
374
375 END IF;
376 END IF;
377
378 IF p_gl_id_rev IS NOT NULL THEN
379 IF NOT fnd_flex_keyval.validate_ccid(
380 appl_short_name => 'SQLGL',
381 key_flex_code => 'GL#',
382 structure_number => arp_global.chart_of_accounts_id,
383 combination_id => p_gl_id_rev) THEN
384
385 p_interface_status := p_interface_status || 'm3,';
386
387 END IF;
388 END IF;
389
390 IF p_gl_id_tax IS NOT NULL THEN
391 IF NOT fnd_flex_keyval.validate_ccid(
392 appl_short_name => 'SQLGL',
393 key_flex_code => 'GL#',
394 structure_number => arp_global.chart_of_accounts_id,
395 combination_id => p_gl_id_tax) THEN
396
397 p_interface_status := p_interface_status || 'm2,';
398
399 END IF;
400 END IF;
401
402 IF p_gl_id_freight IS NOT NULL THEN
403 IF NOT fnd_flex_keyval.validate_ccid(
404 appl_short_name => 'SQLGL',
405 key_flex_code => 'GL#',
406 structure_number => arp_global.chart_of_accounts_id,
407 combination_id => p_gl_id_freight) THEN
408
409 p_interface_status := p_interface_status || 'm2,';
410
411 END IF;
412 END IF;
413
414 IF p_gl_id_clearing IS NOT NULL THEN
415 IF NOT fnd_flex_keyval.validate_ccid(
416 appl_short_name => 'SQLGL',
417 key_flex_code => 'GL#',
418 structure_number => arp_global.chart_of_accounts_id,
419 combination_id => p_gl_id_clearing) THEN
420
421 p_interface_status := p_interface_status || 'm2,';
422
423 END IF;
424 END IF;
425
426 IF p_gl_id_unbilled IS NOT NULL THEN
427 IF NOT fnd_flex_keyval.validate_ccid(
428 appl_short_name => 'SQLGL',
429 key_flex_code => 'GL#',
430 structure_number => arp_global.chart_of_accounts_id,
431 combination_id => p_gl_id_unbilled) THEN
432
433 p_interface_status := p_interface_status || 'm2,';
434
435 END IF;
436 END IF;
437
438 IF p_gl_id_unearned IS NOT NULL THEN
439 IF NOT fnd_flex_keyval.validate_ccid(
440 appl_short_name => 'SQLGL',
441 key_flex_code => 'GL#',
442 structure_number => arp_global.chart_of_accounts_id,
443 combination_id => p_gl_id_unearned) THEN
444
445 p_interface_status := p_interface_status || 'm2,';
446
447 END IF;
448 END IF;
449
450 UPDATE ra_customers_interface_all
451 SET interface_status = p_interface_status
452 WHERE CURRENT OF auto_acc;
453
454 END LOOP;
455 CLOSE auto_acc;
456
457 OPEN boe;
458 LOOP
459 FETCH boe
460 INTO p_site_use_code,p_gl_id_unpaid_rec,p_gl_id_remittance,
461 p_gl_id_factor,p_interface_status;
462
463 EXIT WHEN boe%NOTFOUND;
464
465 IF p_site_use_code <> 'DRAWEE' THEN
466 p_interface_status := p_interface_status || 'u1,';
467 END IF;
468
469 IF p_gl_id_unpaid_rec IS NOT NULL THEN
470 IF NOT fnd_flex_keyval.validate_ccid(
471 appl_short_name => 'SQLGL',
472 key_flex_code => 'GL#',
473 structure_number => arp_global.chart_of_accounts_id,
474 combination_id => p_gl_id_unpaid_rec) THEN
475
476 p_interface_status := p_interface_status || 'u2,';
477
478 END IF;
479 END IF;
480
481 IF p_gl_id_remittance IS NOT NULL THEN
482 IF NOT fnd_flex_keyval.validate_ccid(
483 appl_short_name => 'SQLGL',
484 key_flex_code => 'GL#',
485 structure_number => arp_global.chart_of_accounts_id,
486 combination_id => p_gl_id_remittance) THEN
487
488 p_interface_status := p_interface_status || 'u3,';
489
490 END IF;
491 END IF;
492
493 IF p_gl_id_factor IS NOT NULL THEN
494 IF NOT fnd_flex_keyval.validate_ccid(
495 appl_short_name => 'SQLGL',
496 key_flex_code => 'GL#',
497 structure_number => arp_global.chart_of_accounts_id,
498 combination_id => p_gl_id_factor) THEN
499
500 p_interface_status := p_interface_status || 'u4,';
501
502 END IF;
503 END IF;
504
505 UPDATE ra_customers_interface_all
506 SET interface_status = p_interface_status
507 WHERE CURRENT OF boe;
508 END LOOP;
509 CLOSE boe;
510 END validate_ccid;
511
512 FUNCTION validate_ref_party (p_orig_system_customer_ref IN VARCHAR2,
513 p_insert_update_flag IN VARCHAR2)
514 RETURN VARCHAR2 IS
515 l_return_code VARCHAR2(5) := 'A3,';
516 l_party_id NUMBER;
517 CURSOR c7 IS
518 SELECT party_id
519 FROM hz_parties
520 WHERE orig_system_reference = p_orig_system_customer_ref;
521 BEGIN
522 IF p_insert_update_flag = 'I' THEN
523 OPEN c7;
524 FETCH c7 INTO l_party_id;
525 CLOSE c7;
526 IF l_party_id IS NOT NULL THEN
527 RETURN l_return_code;
528 ELSE
529 RETURN null;
530 END IF;
531 ELSE
532 NULL;
533 END IF;
534 EXCEPTION
535 WHEN NO_DATA_FOUND THEN
536 RETURN null;
537 WHEN OTHERS THEN
538 RAISE;
539 END validate_ref_party;
540
541
542 FUNCTION get_cust_party_id (p_orig_system_customer_ref IN VARCHAR2,
543 p_request_id IN NUMBER)
544 RETURN NUMBER IS
545 l_party_id NUMBER;
546
547 CURSOR c8 IS
548 SELECT party_id
549 FROM hz_parties
550 WHERE orig_system_reference = p_orig_system_customer_ref
551 AND party_id = p_request_id /* Bug Fix : 5214454 */
552 -- AND request_id = p_request_id; /* Bug Fix : 1891773 */
553 AND rownum = 1;
554 BEGIN
555 OPEN c8;
556 FETCH c8 INTO l_party_id;
557 CLOSE c8;
558 RETURN l_party_id;
559
560 EXCEPTION
561 WHEN NO_DATA_FOUND THEN
562 RETURN null;
563 WHEN OTHERS THEN
564 RAISE;
565 END get_cust_party_id;
566
567 FUNCTION get_account_party_id (p_orig_system_customer_ref IN VARCHAR2,
568 p_person_flag IN VARCHAR2 DEFAULT 'N',
569 p_ref_flag IN VARCHAR2 DEFAULT 'C')
570 RETURN NUMBER IS
571 l_party_id NUMBER;
572 l_party_type VARCHAR2(30) ;
573 -- For orig_system_party_ref which is having an account
574 CURSOR party_ref_cur IS
575 SELECT party_id
576 FROM hz_parties party
577 WHERE party.orig_system_reference = p_orig_system_customer_ref
578 AND party.PARTY_TYPE = decode(p_person_flag, 'Y', 'PERSON','ORGANIZATION')
579 AND exists (select 'X' from hz_cust_accounts where party_id = party.party_id)
580 AND rownum = 1;
581 --For orig_system_customer_ref
582 CURSOR cust_ref_cur IS
583 SELECT party_id
584 FROM hz_cust_accounts
585 WHERE orig_system_reference = p_orig_system_customer_ref;
586 --For any orig_system_party_ref
587 CURSOR any_party_ref_cur IS
588 SELECT party_id
589 FROM hz_parties
590 WHERE orig_system_reference = p_orig_system_customer_ref
591 AND PARTY_TYPE = decode(p_person_flag, 'Y', 'PERSON','ORGANIZATION')
592 AND status in ('A','I')
593 AND rownum = 1;
594
595 BEGIN
596 IF p_ref_flag ='P' THEN -- For orig_system_party_ref having an account
597 OPEN party_ref_cur;
598 FETCH party_ref_cur INTO l_party_id;
599 CLOSE party_ref_cur;
600 RETURN l_party_id;
601 ELSIF p_ref_flag ='A' THEN --For any orig_system_party_ref
602 OPEN any_party_ref_cur;
603 FETCH any_party_ref_cur INTO l_party_id;
604 CLOSE any_party_ref_cur;
605 RETURN l_party_id;
606 ELSE -- For orig_system_customer_ref
607 OPEN cust_ref_cur;
608 FETCH cust_ref_cur INTO l_party_id;
609 CLOSE cust_ref_cur;
610 RETURN l_party_id;
611 END IF;
612
613 EXCEPTION
614 WHEN NO_DATA_FOUND THEN
615 RETURN null;
616 WHEN OTHERS THEN
617 RAISE;
618 END get_account_party_id;
619
620 FUNCTION get_subject_id (p_orig_system_contact_ref IN VARCHAR2,
621 p_request_id IN NUMBER)
622 RETURN NUMBER IS
623 l_party_id NUMBER;
624
625 -- bug 2098243 - fixed to use a cursor instead of a direct select.
626 CURSOR c9 IS
627 SELECT c.party_id
628 FROM hz_parties c
629 WHERE c.orig_system_reference = p_orig_system_contact_ref
630 AND c.request_id = p_request_id
631 AND NOT EXISTS
632 (SELECT 'X'
633 FROM hz_cust_accounts y
634 WHERE y.orig_system_reference = p_orig_system_contact_ref
635 AND y.party_id = c.party_id);
636 BEGIN
637 OPEN c9;
638 FETCH c9 INTO l_party_id;
639 CLOSE c9;
640 RETURN l_party_id;
641 EXCEPTION
642 WHEN NO_DATA_FOUND THEN
643 RETURN NULL;
644 WHEN OTHERS THEN
645 RAISE;
646 END get_subject_id;
647
648
649 FUNCTION get_prel_party_id (p_orig_system_contact_ref IN VARCHAR2,
650 p_request_id IN NUMBER)
651 RETURN NUMBER IS
652 l_party_id NUMBER;
653 CURSOR c IS
654 SELECT rel.party_id
655 FROM hz_relationships rel,
656 hz_org_contacts cont
657 WHERE cont.request_id = p_request_id
658 AND cont.orig_system_reference = p_orig_system_contact_ref
659 AND cont.party_relationship_id = rel.relationship_id
660 AND rel.subject_table_name = 'HZ_PARTIES'
661 AND rel.object_table_name = 'HZ_PARTIES';
662 /* AND rel.directional_flag = 'F'; */ /* Bug No : 2359461 */
663 BEGIN
664 OPEN c;
665 FETCH c INTO l_party_id;
666 CLOSE c;
667
668 RETURN l_party_id;
669 EXCEPTION
670 WHEN OTHERS THEN
671 RAISE;
672 END get_prel_party_id;
673
674 FUNCTION get_prel_id (p_orig_system_contact_ref IN VARCHAR2,
675 p_request_id IN NUMBER)
676 RETURN NUMBER IS
677 l_prel_id NUMBER;
678 CURSOR c is
679 SELECT rel.relationship_id
680 FROM hz_relationships rel,
681 hz_org_contacts cont
682 WHERE cont.request_id = p_request_id
683 AND cont.orig_system_reference = p_orig_system_contact_ref
684 AND cont.party_relationship_id = rel.relationship_id
685 AND rel.subject_table_name = 'HZ_PARTIES'
686 AND rel.object_table_name = 'HZ_PARTIES';
687 /* AND rel.directional_flag = 'F'; */ /* Bug No : 2359461 */
688 BEGIN
689 OPEN c;
690 FETCH c INTO l_prel_id;
691 CLOSE c;
692
693 RETURN l_prel_id;
694 EXCEPTION
695 WHEN OTHERS THEN
696 RAISE;
697 END get_prel_id;
698
699 /* bug 4454799 - added argument for org_id below. */
700 FUNCTION val_bill_to_orig_address_ref(p_orig_system_customer_ref IN VARCHAR2,
701 p_orig_system_address_ref IN VARCHAR2,
702 p_bill_to_orig_address_ref IN VARCHAR2,
703 p_orig_system_parent_ref IN VARCHAR2,
704 p_org_id IN NUMBER,
705 req_id IN NUMBER) RETURN VARCHAR2 is
706 err_msg VARCHAR2(3) := 'e3,';
707 l_dummy VARCHAR2(3);
708
709 --Bug 1829164
710 --Rewrote the code using CURSORS to take care of the following conditions also
711 --1> relationship already exists and referencing the
712 -- Bill_to_orig_address_ref of the related customer.
713 --2> relationship is created now, with the SHIP_TO referencing the
714 -- Bill_to_orig_address_ref of the customer who will be related to this
715 -- customer.
716 /*
717 BEGIN
718
719 BEGIN
720 select 'X'
721 into l_count
722 from hz_cust_accounts cust
723 ,hz_cust_acct_sites site
724 ,hz_cust_site_uses su
725 where site.orig_system_reference = p_bill_to_orig_address_ref
726 and site.cust_acct_site_id = su.cust_acct_site_id
727 and site.cust_account_id = cust.cust_account_id
728 and cust.orig_system_reference = p_orig_system_customer_ref
729 and su.site_use_code = 'BILL_TO'
730 and su.status = 'A'
731 and site.status = 'A';
732
733 EXCEPTION
734 WHEN NO_DATA_FOUND THEN
735 BEGIN
736 select 'X'
737 into l_count
738 from ra_customers_interface i,
739 ra_customers_interface i1
740 where
741 i.request_id = req_id
742 and i.bill_to_orig_address_ref is not NULL
743 and i1.site_use_code = 'BILL_TO'
744 and i.bill_to_orig_address_ref = i1.orig_system_address_ref
745 and i.orig_system_address_ref = p_orig_system_address_ref
746 and i.orig_system_customer_ref = p_orig_system_customer_ref
747 and i.rowid <> i1.rowid
748 and i.interface_status is null
749 and rownum = 1;
750 EXCEPTION
751 WHEN NO_DATA_FOUND THEN
752 return err_msg;
753 END;
754 END;
755
756 return null;
757 */
758
759 CURSOR c IS -- Checkif the customer or the related customer already exists
760 SELECT 'x'
761 FROM hz_cust_accounts cust,
762 hz_cust_acct_sites_all site, -- bug 4454799
763 hz_cust_site_uses_all su -- bug 4454799
764 WHERE cust.orig_system_reference = p_orig_system_customer_ref
765 AND site.orig_system_reference = p_bill_to_orig_address_ref
766 AND site.org_id = p_org_id -- bug 4454799
767 AND site.cust_account_id = cust.cust_account_id
768 AND site.cust_acct_site_id = su.cust_acct_site_id
769 AND site.org_id = su.org_id -- bug 4454799
770 AND su.site_use_code = 'BILL_TO'
771 AND su.status = 'A'
772 AND site.status = 'A'
773 UNION ALL
774 SELECT 'x'
775 FROM hz_cust_accounts cust,
776 hz_cust_acct_sites_all site, -- bug 4454799
777 hz_cust_site_uses_all su -- bug 4454799
778 WHERE cust.orig_system_reference = p_orig_system_parent_ref
779 AND site.orig_system_reference = p_bill_to_orig_address_ref
780 AND site.org_id = p_org_id -- bug 4454799
781 AND site.cust_account_id = cust.cust_account_id
782 AND site.cust_acct_site_id = su.cust_acct_site_id
783 AND site.org_id = su.org_id -- bug 4454799
784 AND su.site_use_code = 'BILL_TO'
785 AND su.status = 'A'
786 AND site.status = 'A';
787
788 CURSOR c2 IS -- Checking if the relationship already exists
789 SELECT 'x'
790 FROM hz_cust_accounts cust,
791 hz_cust_acct_relate_all rel, -- bug 4454799
792 hz_cust_acct_sites_all site, -- bug 4454799
793 hz_cust_site_uses_all su -- bug 4454799
794 WHERE cust.orig_system_reference = p_orig_system_customer_ref
795 AND rel.related_cust_account_id = cust.cust_account_id
796 AND rel.bill_to_flag = 'Y'
797 AND site.cust_account_id = rel.cust_account_id
798 AND site.orig_system_reference = p_bill_to_orig_address_ref
799 AND site.org_id = p_org_id -- bug 4454799
800 AND site.org_id = su.org_id -- bug 4454799
801 AND site.org_id = rel.org_id -- bug 4454799
802 AND site.cust_acct_site_id = su.cust_acct_site_id
803 AND su.site_use_code = 'BILL_TO'
804 AND su.status = 'A'
805 AND site.status = 'A';
806
807 CURSOR c1 IS -- If the customer or related customer do not exist THEN Check for their existence in the interface table
808 SELECT 'x'
809 FROM ra_customers_interface i,
810 ra_customers_interface_all i1 -- bug 4454799
811 WHERE i.request_id = req_id
812 AND i.bill_to_orig_address_ref is not NULL
813 AND i.orig_system_customer_ref = p_orig_system_customer_ref
814 AND i.orig_system_address_ref = p_orig_system_address_ref
815 AND i.bill_to_orig_address_ref = i1.orig_system_address_ref
816 AND i.org_id = p_org_id -- bug 4454799
817 AND i.org_id = i1.org_id -- bug 4454799
818 AND i1.site_use_code = 'BILL_TO'
819 AND i.rowid <> i1.rowid
820 AND i1.interface_status is null
821 AND rownum = 1
822 UNION ALL
823 SELECT 'x'
824 FROM ra_customers_interface i,
825 ra_customers_interface_all i1 -- bug 4454799
826 WHERE i.request_id = req_id
827 AND i.bill_to_orig_address_ref is not NULL
828 AND i.orig_system_customer_ref = p_orig_system_customer_ref
829 AND i1.orig_system_customer_ref = i.orig_system_parent_ref
830 AND i.orig_system_address_ref = p_orig_system_address_ref
831 AND i.org_id = p_org_id -- bug 4454799
832 AND i.org_id = i1.org_id -- bug 4454799
833 AND i.bill_to_orig_address_ref = i1.orig_system_address_ref
834 AND i1.site_use_code = 'BILL_TO'
835 AND i.rowid <> i1.rowid
836 AND i1.interface_status is null
837 AND rownum = 1;
838
839 BEGIN
840
841 OPEN c;
842 FETCH c INTO l_dummy;
843 IF c%NOTFOUND THEN
844 OPEN c1;
845 FETCH c1 INTO l_dummy;
846 IF c1%NOTFOUND THEN
847 OPEN c2;
848 FETCH c2 INTO l_dummy;
849 IF c2%NOTFOUND THEN
850 CLOSE c;
851 CLOSE c1;
852 CLOSE c2;
853 RETURN err_msg;
854 END IF;
855 END IF;
856 END IF;
857
858 IF c%ISOPEN THEN
859 CLOSE c;
860 END IF;
861
862 IF c1%ISOPEN THEN
863 CLOSE c1;
864 END IF;
865
866 IF c2%ISOPEN THEN
867 CLOSE c2;
868 END IF;
869 RETURN NULL;
870
871 EXCEPTION
872 WHEN OTHERS THEN
873 RETURN err_msg;
874 END val_bill_to_orig_address_ref;
875
876
877 /*===========================================================================+
878 | FUNCTION
879 | get_ultimate_parent_party_ref
880 |
881 | DESCRIPTION
882 | get ultimate parent party system reference
883 |
884 | SCOPE - PUBLIC
885 |
886 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
887 |
888 | ARGUMENTS : IN:
889 | p_orig_system_customer_ref
890 | OUT:
891 | IN/ OUT:
892 |
893 | RETURNS : Y/N
894 |
895 | NOTES
896 |
897 | MODIFICATION HISTORY
898 |
899 | Jianying Huang 30-APR-01 Created.
900 | Jianying Huang 19-SEP-01 Renamed procedure and returned ultimate parent
901 | party reference.
902 |
903 +===========================================================================*/
904
905 FUNCTION get_ultimate_parent_party_ref (p_orig_system_customer_ref VARCHAR2)
906 RETURN VARCHAR2 IS
907
908 l_orig_system_party_ref ra_customers_interface.orig_system_party_ref%TYPE;
909 l_orig_system_parent_ref ra_customers_interface.orig_system_parent_ref%TYPE;
910
911 -- bug 2098243 - fixed to use a cursor instead of a direct select.
912 CURSOR c10 IS
913 SELECT orig_system_parent_ref, orig_system_party_ref
914 FROM ra_customers_interface
915 WHERE orig_system_customer_ref = p_orig_system_customer_ref
916 AND ROWNUM = 1;
917 BEGIN
918 OPEN c10;
919 FETCH c10 INTO l_orig_system_parent_ref, l_orig_system_party_ref;
920 CLOSE c10;
921
922 IF l_orig_system_parent_ref IS NULL THEN
923 RETURN NVL(l_orig_system_party_ref, p_orig_system_customer_ref);
924 ELSE
925 RETURN get_ultimate_parent_party_ref( l_orig_system_parent_ref );
926 END IF;
927
928 EXCEPTION
929 WHEN NO_DATA_FOUND THEN
930 RETURN p_orig_system_customer_ref;
931 END get_ultimate_parent_party_ref;
932
933 /*===========================================================================+
934 | FUNCTION
935 | check_assigned_worker
936 |
937 | DESCRIPTION
938 | Computer worker number based on passed-in string and
939 | compare the computed worker number with passed-in worker
940 | number.
941 |
942 | SCOPE - PUBLIC
943 |
944 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
945 |
946 | ARGUMENTS : IN:
947 | p_string
948 | p_total_workers
949 | p_worker
950 | OUT:
951 | IN/ OUT:
952 |
953 | RETURNS : Y/N
954 |
955 | NOTES
956 |
957 | MODIFICATION HISTORY
958 |
959 | Jianying Huang 30-APR-01 Created.
960 |
961 +===========================================================================*/
962
963 FUNCTION check_assigned_worker (p_string VARCHAR2,
964 p_total_workers NUMBER,
965 p_worker NUMBER)
966 RETURN VARCHAR2 IS
967
968 len NUMBER;
969 val NUMBER := 0;
970 ret VARCHAR2(1);
971 i NUMBER := 1;
972
973 BEGIN
974 IF p_total_workers = 1 THEN
975 IF p_worker = 1 THEN
976 RETURN ('Y');
977 ELSE
978 RETURN ('N');
979 END IF;
980 END IF;
981
982 len := LENGTHB(p_string);
983 IF (len = 0) THEN
984 RETURN ('N');
985 END IF;
986
987 WHILE (i <= len)
988 LOOP
989 val := val + ASCII(SUBSTRB(p_string, i, 1));
990 i := i + 1;
991 END LOOP;
992
993 IF ((MOD( val, p_total_workers ) + 1) = p_worker) THEN
994 ret := 'Y';
995 ELSE
996 ret := 'N';
997 END IF;
998
999 RETURN (ret);
1000
1001 END check_assigned_worker;
1002
1003 /*===========================================================================+
1004 | PROCEDURE
1005 | conc_main
1006 |
1007 | DESCRIPTION
1008 | Procedure called by concurrent executable - minus create_reciprocal
1009 | _flag
1010 | SCOPE - PUBLIC
1011 |
1012 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1013 |
1014 | ARGUMENTS : IN:
1015 | OUT:
1016 | errbuf
1017 | retcode
1018 | IN/ OUT:
1019 |
1020 | RETURNS : NONE
1021 |
1022 | NOTES
1023 |
1024 | MODIFICATION HISTORY
1025 |
1026 | J. del Callar 06-NOV-01 Created for backward compatibility, refer to
1027 | Bug 2092530.
1028 |
1029 +===========================================================================*/
1030
1031 PROCEDURE conc_main (errbuf OUT NOCOPY VARCHAR2,
1032 retcode OUT NOCOPY VARCHAR2) IS
1033 BEGIN
1034 conc_main(errbuf, retcode, 'No' , 0);
1035 END conc_main;
1036
1037 /*===========================================================================+
1038 | PROCEDURE
1039 | conc_main
1040 |
1041 | DESCRIPTION
1042 | Procedure called by concurrent executable.
1043 |
1044 | SCOPE - PUBLIC
1045 |
1046 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1047 |
1048 | ARGUMENTS : IN:
1049 | create_reciprocal_flag - create reciprocal customer flag
1050 | OUT:
1051 | errbuf
1052 | retcode
1053 | IN/ OUT:
1054 |
1055 | RETURNS : NONE
1056 |
1057 | NOTES
1058 |
1059 | MODIFICATION HISTORY
1060 |
1061 | Jianying Huang 30-APR-00 Created.
1062 | J. del Callar 06-NOV-01 Bug 2092530: added create_reciprocal_flag arg
1063 |
1064 +===========================================================================*/
1065
1066 PROCEDURE conc_main (errbuf OUT NOCOPY VARCHAR2,
1067 retcode OUT NOCOPY VARCHAR2,
1068 p_create_reciprocal_flag IN VARCHAR2,
1069 p_org_id IN NUMBER := 0 ) IS
1070
1071 m_request_id NUMBER;
1072
1073 l_profile VARCHAR2(30);
1074 l_num_of_workers NUMBER;
1075 l_sub_request_id NUMBER;
1076
1077 l_conc_phase VARCHAR2(80);
1078 l_conc_status VARCHAR2(80);
1079 l_conc_dev_phase VARCHAR2(30);
1080 l_conc_dev_status VARCHAR2(30);
1081 l_message VARCHAR2(240);
1082
1083 l_posi1 NUMBER;
1084 l_posi2 NUMBER;
1085 l_times NUMBER;
1086 l_request_data VARCHAR2(240);
1087 l_sub_request_ids VARCHAR2(200);
1088
1089 i NUMBER;
1090
1091 BEGIN
1092
1093 -- Read the value from REQUEST_DATA. If this is the
1094 -- first run of the program, the return value will
1095 -- be null. Otherwise, the return value will be what
1096 -- we passed to SET_REG_GLOBALS on the previous run.
1097
1098 -- First run: Do global validation
1099 -- Second run: Do parallel validation
1100 -- Third run: Do global insert/update
1101 -- Fourth run: Do parallel insert/update
1102
1103 l_request_data := FND_CONC_GLOBAL.REQUEST_DATA;
1104
1105 -- this is not the first run
1106 IF l_request_data IS NOT NULL THEN
1107
1108 l_posi1 := INSTRB(l_request_data, ' ', 1, 1);
1109 l_posi2 := INSTRB(l_request_data, ' ', 1, 2);
1110 l_times := TO_NUMBER(SUBSTRB(l_request_data, 1, l_posi1 - 1));
1111 m_request_id := TO_NUMBER(SUBSTRB(l_request_data, l_posi1 + 1,
1112 l_posi2 - l_posi1 ) );
1113 l_sub_request_ids := SUBSTRB(l_request_data, l_posi2 + 1);
1114
1115 WHILE l_sub_request_ids IS NOT NULL LOOP
1116 l_posi1 := INSTRB(l_sub_request_ids, ' ', 1, 1);
1117 l_sub_request_id := TO_NUMBER(SUBSTRB(l_sub_request_ids, 1, l_posi1-1));
1118
1119 -- Check return status of validation request.
1120 IF (FND_CONCURRENT.GET_REQUEST_STATUS(
1121 request_id => l_sub_request_id,
1122 phase => l_conc_phase,
1123 status => l_conc_status,
1124 dev_phase => l_conc_dev_phase,
1125 dev_status => l_conc_dev_status,
1126 message => l_message)) THEN
1127 IF l_conc_dev_phase <> 'COMPLETE'
1128 OR l_conc_dev_status <> 'NORMAL' THEN
1129 retcode := 2;
1130
1131 FND_FILE.PUT_LINE(FND_FILE.LOG,
1132 TO_CHAR( l_sub_request_id ) ||
1133 ' : ' || l_conc_phase || ':' || l_conc_status ||
1134 ' (' || l_message || ').' );
1135 RETURN;
1136
1137 END IF;
1138 ELSE
1139 retcode := 2;
1140
1141 RETURN;
1142 END IF;
1143
1144 l_sub_request_ids := SUBSTRB( l_sub_request_ids, l_posi1 + 1 );
1145 END LOOP;
1146 ELSE
1147 m_request_id := FND_GLOBAL.CONC_REQUEST_ID;
1148 l_times := 0;
1149 END IF;
1150
1151 l_times := l_times + 1;
1152
1153 -- if this is the first run, we need to do global validation
1154 -- if this is the third run, we need to do global insert/update
1155 IF l_times IN ( 1, 3, 5 ) THEN
1156 IF p_org_id <> 0 THEN
1157 FND_REQUEST.SET_ORG_ID(p_org_id);
1158 END IF;
1159 -- Bug 2092530: added create_reciprocal_flag arg
1160 l_sub_request_id := FND_REQUEST.SUBMIT_REQUEST(
1161 'AR', 'RACUSTSB', '',
1162 TO_CHAR( SYSDATE, 'DD-MON-YY HH24:MI:SS' ), --Bug 3175928
1163 TRUE,
1164 1, 1, TO_CHAR(l_times), p_create_reciprocal_flag, p_org_id);
1165
1166 IF l_sub_request_id = 0 THEN
1167
1168 FND_MESSAGE.SET_NAME('AR', 'AR_CUST_CONC_ERROR');
1169 FND_MESSAGE.RETRIEVE(l_message);
1170
1171 FND_FILE.PUT_LINE(FND_FILE.LOG, l_message);
1172 retcode := 2;
1173 RETURN;
1174
1175 ELSE
1176
1177 FND_MESSAGE.SET_NAME('AR', 'AR_CUST_SHOW_UPD_REQID');
1178 FND_MESSAGE.RETRIEVE(l_message);
1179
1180 FND_FILE.PUT_LINE(FND_FILE.LOG, l_message);
1181 retcode := 0;
1182
1183 END IF;
1184
1185 IF l_times = 5 THEN
1186 retcode := 0;
1187 RETURN;
1188 END IF;
1189
1190 l_request_data := TO_CHAR( l_times ) || ' ' ||
1191 TO_CHAR( m_request_id ) || ' ' ||
1192 TO_CHAR( l_sub_request_id ) || ' ';
1193
1194 -- if this is third run, do parallel validation
1195 -- if this is fouth run, do parallel insert/update
1196 ELSIF l_times IN ( 2, 4 ) THEN
1197 -- read profile option.
1198 l_profile := 'HZ_CINTERFACE_NUM_OF_WORKERS';
1199
1200 l_num_of_workers := NVL(FND_PROFILE.VALUE(l_profile), 1);
1201
1202 FND_FILE.PUT_LINE(FND_FILE.LOG,
1203 l_profile || ' = ' || TO_CHAR(l_num_of_workers));
1204
1205 l_request_data := '';
1206
1207 -- submit sub-requests to insert/update customers.
1208 -- do NOT do validation.
1209 FOR i IN 1..l_num_of_workers LOOP
1210 IF p_org_id <> 0 THEN
1211 FND_REQUEST.SET_ORG_ID(p_org_id);
1212 END IF;
1213 -- Bug 2092530: added create_reciprocal_flag arg
1214 l_sub_request_id := FND_REQUEST.SUBMIT_REQUEST(
1215 'AR', 'RACUSTSB', '',
1216 TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS'), --Bug 3175928
1217 TRUE,
1218 TO_CHAR(l_num_of_workers),
1219 TO_CHAR(i), TO_CHAR(l_times),
1220 p_create_reciprocal_flag, p_org_id);
1221
1222 IF l_sub_request_id = 0 THEN
1223
1224 FND_MESSAGE.SET_NAME('AR', 'AR_CUST_CONC_ERROR');
1225 FND_MESSAGE.RETRIEVE(l_message );
1226
1227 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(i) || ' : ' || l_message);
1228 retcode := 2;
1229
1230 ELSE
1231
1232 FND_MESSAGE.SET_NAME('AR', 'AR_CUST_SHOW_UPD_REQID');
1233 FND_MESSAGE.RETRIEVE(l_message);
1234
1235 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(i) || ' : ' || l_message);
1236 retcode := 0;
1237
1238 l_request_data := l_request_data || TO_CHAR(l_sub_request_id) || ' ';
1239 END IF;
1240
1241 END LOOP;
1242
1243 l_request_data := TO_CHAR(l_times) || ' ' ||
1244 TO_CHAR(m_request_id) || ' ' ||
1245 l_request_data;
1246 END IF;
1247
1248 FND_CONC_GLOBAL.SET_REQ_GLOBALS(
1249 conc_status => 'PAUSED',
1250 request_data => l_request_data);
1251
1252 EXCEPTION
1253 WHEN OTHERS THEN
1254 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Oracle Error : ' || SQLERRM );
1255 retcode := 2;
1256 RETURN;
1257 END conc_main;
1258
1259 /*===========================================================================+
1260 | FUNCTION
1261 | validate_profile
1262 |
1263 | DESCRIPTION
1264 | Function called by concurrent executable.
1265 |
1266 | SCOPE - PUBLIC
1267 |
1268 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1269 |
1270 | ARGUMENTS : IN:
1271 | errbuf
1272 | retcode
1273 | OUT:
1274 | IN/ OUT:
1275 |
1276 | RETURNS : Error Code/NULL
1277 |
1278 | NOTES
1279 |
1280 | MODIFICATION HISTORY
1281 |
1282 | Gautam/Chelvi 26-May-01 Created for Bug# 1791059.
1283 |
1284 +===========================================================================*/
1285
1286 /* bug 4454799 - added argument for org_id below. */
1287 FUNCTION validate_profile(v_insert_update_flag IN VARCHAR,
1288 v_orig_system_customer_ref IN VARCHAR,
1289 v_orig_system_address_ref IN VARCHAR,
1290 v_org_id IN NUMBER,
1291 v_request_id IN NUMBER)
1292 RETURN VARCHAR2 AS
1293
1294 l_dummy VARCHAR(1);
1295
1296 CURSOR c1 IS -- Check if Cust record exists for ninsertion
1297 SELECT 'x'
1298 FROM ra_customers_interface
1299 WHERE orig_system_customer_ref = v_orig_system_customer_ref
1300 AND interface_status is null
1301 AND request_id = v_request_id;
1302
1303 CURSOR c2 IS -- Checking if Profile exists at Cust Level , thats why site_use_id is null
1304 SELECT 'x'
1305 FROM hz_customer_profiles p ,hz_cust_accounts c
1306 WHERE c.orig_system_reference = v_orig_system_customer_ref
1307 AND p.cust_account_id = c.cust_account_id
1308 AND p.site_use_id is null;
1309
1310 CURSOR c3 IS
1311 SELECT 'x' -- The address ref should exist as Bill To and defined for the customer
1312 FROM hz_cust_acct_sites_all ra, hz_cust_site_uses_all rsu, hz_cust_accounts rc -- bug 4454799
1313 WHERE ra.orig_system_reference = v_orig_system_address_ref
1314 AND ra.org_id = v_org_id -- bug 4454799
1315 AND rc.orig_system_reference = v_orig_system_customer_ref
1316 AND rc.cust_account_id = ra.cust_account_id
1317 AND ra.cust_acct_site_id = rsu.cust_acct_site_id
1318 AND ra.org_id = rsu.org_id -- bug 4454799
1319 AND rsu.status = 'A'
1320 AND rsu.site_use_code in ('BILL_TO','DUN','STMTS')
1321 UNION ALL
1322 SELECT 'x' -- If not already defined, THEN address rec should
1323 -- exist in interface table with Bill To
1324 FROM ra_customers_interface
1325 WHERE orig_system_customer_ref = v_orig_system_customer_ref
1326 AND interface_status is null
1327 AND orig_system_address_ref = v_orig_system_address_ref
1328 AND org_id = v_org_id -- bug 4454799
1329 AND request_id = v_request_id
1330 --Bug fix 2473275
1331 AND site_use_code in ('BILL_TO','DUN','STMTS');
1332
1333 CURSOR c4 IS -- Checking if Profile exists at Site Use Level
1334 SELECT 'x'
1335 FROM hz_customer_profiles p,
1336 hz_cust_acct_sites_all ra, -- bug 4454799
1337 hz_cust_site_uses_all rsu -- but 4454799
1338 WHERE ra.orig_system_reference = v_orig_system_address_ref
1339 AND ra.org_id = v_org_id -- bug 4454799
1340 AND ra.org_id = rsu.org_id -- bug 4454799
1341 AND ra.cust_acct_site_id = rsu.cust_acct_site_id
1342 AND rsu.status = 'A'
1343 --Bug fix 2473275
1344 AND rsu.site_use_code in ('BILL_TO','DUN','STMTS')
1345 AND rsu.site_use_id = p.site_use_id;
1346
1347 BEGIN
1348
1349 IF v_insert_update_flag NOT IN ('I','U') THEN
1350 RETURN 'J8,';
1351 END IF;
1352
1353 -- Profile at the Cust Level validations
1354
1355 IF v_orig_system_address_ref IS NULL THEN
1356
1357 IF v_insert_update_flag = 'I' THEN -- Insert New Profile
1358 -- check if the customer ref is valid. if not, reject with status = S1
1359 OPEN c1;
1360 FETCH c1 INTO l_dummy;
1361 IF c1%NOTFOUND THEN
1362 RETURN 'S1,';
1363 END IF;
1364 CLOSE c1;
1365
1366 -- This customer should not have a profile defined already.
1367 -- if defined reject with status = 'a3'
1368 OPEN c2;
1369 FETCH c2 INTO l_dummy;
1370 IF c2%FOUND THEN
1371 RETURN 'a3,';
1372 END IF;
1373 CLOSE c2;
1374 END IF;
1375
1376 IF v_insert_update_flag = 'U' THEN -- Updating existing profile
1377 -- This customer should have a profile defined already.
1378 -- if not defined reject with status = 'a4'
1379 OPEN c2;
1380 FETCH c2 INTO l_dummy;
1381 IF c2%NOTFOUND THEN
1382 RETURN 'a4,';
1383 END IF;
1384 CLOSE c2;
1385 END IF;
1386 END IF;
1387
1388 -- Profile at the Address Level Validations
1389
1390 IF v_orig_system_address_ref IS NOT NULL THEN
1391 IF v_insert_update_flag = 'I' THEN -- Insert New Profile
1392
1393 -- First check if the address has been already created
1394 -- as a Bill_TO,DUNNING or STATEMENTS or if not created THEN should be in the
1395 -- interface table with no error AND should be BILL_TO,DUNNING or STATEMENTS.
1396 OPEN c3;
1397 FETCH c3 INTO l_dummy;
1398 IF c3%NOTFOUND THEN
1399 RETURN 'S7,';
1400 END IF;
1401 CLOSE c3;
1402
1403 -- This Site should not have a profile defined already.
1404 -- if defined reject with status = 'a3'
1405 OPEN c4;
1406 FETCH c4 INTO l_dummy;
1407 IF c4%FOUND THEN
1408 RETURN 'a3,';
1409 END IF;
1410 CLOSE c4;
1411 END IF;
1412
1413 IF v_insert_update_flag = 'U' THEN -- Updating Existing Profile
1414 -- This Site should have a profile defined already.
1415 -- if not defined reject with status = 'a4'
1416 OPEN c4;
1417 FETCH c4 INTO l_dummy;
1418 IF c4%NOTFOUND THEN
1419 RETURN 'a4,';
1420 END if;
1421 CLOSE c4;
1422 END IF;
1423 END IF;
1424
1425 -- If all validations pass THEN return null
1426 RETURN NULL;
1427
1428 END validate_profile;
1429
1430 FUNCTION validate_address(p_location_structure_id IN NUMBER,
1431 p_creation_date IN DATE,
1432 p_state IN VARCHAR2,
1433 p_city IN VARCHAR2,
1434 p_county IN VARCHAR2,
1435 p_postal_code IN VARCHAR2,
1436 p_province IN VARCHAR2 default null) RETURN VARCHAR2 IS
1437 l_dummy VARCHAR2(1);
1438 --Bug Fix 2684136
1439 l_struct VARCHAR2(200);
1440 l_no_segments NUMBER;
1441 l_child VARCHAR2(20);
1442 l_parent VARCHAR2(20);
1443 l_child_value VARCHAR2(100);
1444 l_parent_value VARCHAR2(100);
1445
1446 --FOR postal_code.city.county.state
1447
1448 CURSOR C IS
1449 SELECT 'X'
1450 FROM ar_location_values v,
1451 ar_location_values pv,
1452 ar_location_values gv,
1453 ar_location_values ggv,
1454 ar_location_rates r
1455 WHERE v.location_structure_id = p_location_structure_id
1456 AND v.location_segment_id = r.location_segment_id
1457 AND v.location_segment_value = UPPER(p_postal_code)
1458 AND v.location_segment_qualifier = 'POSTAL_CODE'
1459 AND TRUNC(p_creation_date)
1460 BETWEEN TRUNC(r.start_date)
1461 AND NVL(TRUNC(r.end_date), TRUNC(p_creation_date))
1462 AND p_postal_code BETWEEN r.from_postal_code AND r.to_postal_code
1463 AND v.parent_segment_id = pv.location_segment_id(+)
1464 AND pv.parent_segment_id = gv.location_segment_id(+)
1465 AND gv.parent_segment_id = ggv.location_segment_id(+)
1466 AND (pv.location_segment_value = UPPER(p_city)
1467 OR p_city IS NULL)
1468 AND (gv.location_segment_value = UPPER(p_county)
1469 OR p_county IS NULL)
1470 AND (ggv.location_segment_value = UPPER(p_state)
1471 OR p_state IS NULL );
1472 --For city.county.state structure
1473
1474 CURSOR C1 IS
1475 SELECT 'X'
1476 FROM ar_location_values v,
1477 ar_location_values pv,
1478 ar_location_values gv,
1479 ar_location_rates r
1480 WHERE v.location_structure_id = p_location_structure_id
1481 AND v.location_segment_id = r.location_segment_id
1482 AND v.location_segment_value = UPPER(p_city)
1483 AND v.location_segment_qualifier = 'CITY'
1484 AND TRUNC(p_creation_date)
1485 BETWEEN TRUNC(r.start_date)
1486 AND NVL(TRUNC(r.end_date), TRUNC(p_creation_date))
1487 AND p_postal_code BETWEEN r.from_postal_code AND r.to_postal_code
1488 AND v.parent_segment_id = pv.location_segment_id(+)
1489 AND pv.parent_segment_id = gv.location_segment_id(+)
1490 AND (pv.location_segment_value = UPPER(p_county)
1491 OR p_county IS NULL)
1492 AND (gv.location_segment_value = UPPER(p_state)
1493 OR p_state IS NULL);
1494
1495 --For city.province and city.state structure
1496
1497 CURSOR C2(p_child VARCHAR2,p_parent_value VARCHAR2,p_child_value VARCHAR2) IS
1498 SELECT 'X'
1499 FROM ar_location_values v,
1500 ar_location_values gv,
1501 ar_location_rates r
1502 WHERE v.location_structure_id = p_location_structure_id
1503 AND v.location_segment_id = r.location_segment_id
1504 AND v.location_segment_value = UPPER(p_child_value)
1505 AND v.location_segment_qualifier = p_child
1506 AND TRUNC(p_creation_date)
1507 BETWEEN TRUNC(r.start_date)
1508 AND nvl(TRUNC(r.end_date), TRUNC(p_creation_date))
1509 AND p_postal_code BETWEEN r.from_postal_code AND r.to_postal_code
1510 AND v.parent_segment_id = gv.location_segment_id(+)
1511 AND (gv.location_segment_value = UPPER(p_parent_value)
1512 OR p_state IS NULL);
1513
1514 --For city and province Structure
1515
1516 CURSOR C3(p_segment VARCHAR2,p_value VARCHAR2) IS
1517 SELECT 'X'
1518 FROM ar_location_values v, ar_location_rates r
1519 WHERE v.location_structure_id = p_location_structure_id
1520 AND v.location_segment_id = r.location_segment_id
1521 AND v.location_segment_value = UPPER(p_value)
1522 AND v.location_segment_qualifier = p_segment
1523 AND TRUNC(p_creation_date)
1524 BETWEEN TRUNC(r.start_date)
1525 AND nvl(TRUNC(r.end_date), TRUNC(p_creation_date))
1526 AND p_postal_code BETWEEN r.from_postal_code AND r.to_postal_code
1527 ;
1528 BEGIN
1529
1530 l_struct := (replace (replace (replace (arp_flex.expand(arp_flex.location,'ALLREV','.','%QUALIFIER%'),'EXEMPT_LEVEL',null),'TAX_ACCOUNT',null),' ',null));
1531
1532 l_no_segments := arp_flex.active_segments(arp_flex.location);
1533
1534 IF l_no_segments = 1 THEN
1535 IF l_struct = 'CITY' THEN
1536 l_child_value := p_city;
1537 ELSIF l_struct = 'PROVINCE' THEN
1538 l_child_value := p_province;
1539 ELSIF l_struct = 'COUNTRY' THEN
1540 RETURN (null);
1541 ELSE
1542 RETURN('Y');
1543 END IF;
1544
1545 OPEN C3( l_struct, l_child_value );
1546
1547 FETCH C3 INTO l_dummy;
1548 IF C3%NOTFOUND THEN
1549 CLOSE C3;
1550 RETURN('Y');
1551 END IF;
1552
1553 ELSIF l_no_segments = 2 THEN
1554
1555 select
1556 rtrim(substr(l_struct,1,instr(l_struct,'.')),'.'),ltrim(substr(l_struct,instr(l_struct,'.')),'.') into l_child,l_parent
1557 from dual;
1558
1559 IF (l_struct = 'CITY.STATE') then
1560 l_child_value := p_city;
1561 l_parent_value := p_state;
1562 elsif ( l_struct = 'CITY.PROVINCE') THEN
1563 l_child_value := p_city;
1564 l_parent_value := p_province;
1565 END IF;
1566
1567
1568 OPEN C2( l_child,l_parent_value,l_child_value);
1569 FETCH C2 INTO l_dummy;
1570 IF C2%NOTFOUND THEN
1571 CLOSE C2;
1572 RETURN('Y');
1573 END IF;
1574
1575 ELSIF l_no_segments = 3 THEN
1576 OPEN C1 ;
1577 FETCH C1 INTO l_dummy;
1578 IF C1%NOTFOUND THEN
1579 CLOSE C1;
1580 RETURN('Y');
1581 END IF;
1582
1583 ELSE
1584 OPEN C;
1585 FETCH C INTO l_dummy;
1586 IF C%NOTFOUND THEN
1587 CLOSE C;
1588 RETURN('Y');
1589 END IF;
1590 END IF;
1591
1592 IF C%ISOPEN THEN
1593 CLOSE C;
1594 END IF;
1595 IF C1%ISOPEN THEN
1596 CLOSE C1;
1597 END IF;
1598 IF C2%ISOPEN THEN
1599 CLOSE C2;
1600 END IF;
1601 IF C3%ISOPEN THEN
1602 CLOSE C3;
1603 END IF;
1604
1605 RETURN(null);
1606
1607 EXCEPTION
1608 WHEN OTHERS THEN
1609 RETURN('E');
1610 END validate_address;
1611 /*===========================================================================+
1612 | FUNCTION
1613 | validate_party_number
1614 |
1615 | DESCRIPTION
1616 | Function called by concurrent executable.
1617 |
1618 | SCOPE - PUBLIC
1619 |
1620 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1621 |
1622 | ARGUMENTS : IN:
1623 | orig_system_customer_ref
1624 | orig_system_party_ref
1625 | party_number
1626 | rowid
1627 | request_id
1628 | OUT:
1629 | IN/ OUT:
1630 |
1631 | RETURNS : Error Code/NULL
1632 |
1633 | NOTES
1634 |
1635 | MODIFICATION HISTORY
1636 |
1637 | V.Srinivasan 04-MAR-01 Created for Bug# 2222791.
1638 |
1639 +===========================================================================*/
1640 FUNCTION val_party_number( p_orig_system_customer_ref IN VARCHAR2,
1641 p_orig_system_party_ref IN VARCHAR2,
1642 p_party_number IN VARCHAR2,
1643 p_rowid IN ROWID,
1644 req_id IN NUMBER) RETURN VARCHAR2 is
1645 err_msg VARCHAR2(3) := 'Y3,';
1646 l_dummy VARCHAR2(3);
1647
1648
1649 CURSOR c IS -- Check if the party_number already exists
1650 SELECT decode(party.orig_system_reference,p_orig_system_party_ref,'','Y3,')
1651 FROM hz_parties party
1652 WHERE party.party_number = p_party_number;
1653
1654 CURSOR c1 IS -- Check if the party_number already exists
1655 SELECT decode(party.orig_system_reference,p_orig_system_customer_ref,'','Y3,')
1656 FROM hz_parties party
1657 WHERE party.party_number = p_party_number;
1658
1659 CURSOR c2 IS -- Check if the party_number already exists in interface table
1660 SELECT decode(NVL(i.orig_system_party_ref,i.orig_system_customer_ref),p_orig_system_party_ref,'','Y3,')
1661 FROM ra_customers_interface_all i
1662 WHERE i.party_number = p_party_number
1663 AND i.request_id = req_id
1664 AND i.rowid <> p_rowid ;
1665
1666 CURSOR c3 IS -- Check if the party_number already exists in interface table
1667 SELECT decode(NVL(i.orig_system_party_ref,i.orig_system_customer_ref),p_orig_system_customer_ref,'','Y3,')
1668 FROM ra_customers_interface_all i
1669 WHERE i.party_number = p_party_number
1670 AND i.request_id = req_id
1671 AND i.rowid <> p_rowid ;
1672
1673
1674 BEGIN
1675
1676 if p_orig_system_party_ref is NOT NULL then
1677 OPEN c;
1678 FETCH c INTO l_dummy;
1679 IF c%NOTFOUND THEN
1680 OPEN c2;
1681 FETCH c2 INTO l_dummy;
1682
1683 IF c2%NOTFOUND THEN
1684 CLOSE c;
1685 CLOSE c2;
1686 ELSE RETURN l_dummy;
1687 END IF;
1688
1689 ELSE RETURN l_dummy;
1690 END IF;
1691
1692 IF c%ISOPEN THEN
1693 CLOSE c;
1694 END IF;
1695
1696 IF c2%ISOPEN THEN
1697 CLOSE c2;
1698 END IF;
1699
1700 RETURN NULL;
1701
1702 else
1703
1704 OPEN c1;
1705 FETCH c1 INTO l_dummy;
1706 IF c1%NOTFOUND THEN
1707 OPEN c3;
1708 FETCH c3 INTO l_dummy;
1709
1710 IF c3%NOTFOUND THEN
1711 CLOSE c1;
1712 CLOSE c3;
1713 ELSE RETURN l_dummy;
1714 END IF;
1715
1716 ELSE RETURN l_dummy;
1717 END IF;
1718
1719 IF c1%ISOPEN THEN
1720 CLOSE c1;
1721 END IF;
1722
1723 IF c3%ISOPEN THEN
1724 CLOSE c3;
1725 END IF;
1726
1727 RETURN NULL;
1728
1729 end if;
1730
1731 EXCEPTION
1732 WHEN OTHERS THEN
1733 RETURN l_dummy;
1734 END val_party_number;
1735 /*===========================================================================+
1736 | FUNCTION
1737 | validate_party_numb_ref
1738 |
1739 | DESCRIPTION
1740 | Function called by concurrent executable.
1741 |
1742 | SCOPE - PUBLIC
1743 |
1744 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1745 |
1746 | ARGUMENTS : IN:
1747 | orig_system_customer_ref
1748 | orig_system_party_ref
1749 | party_number
1750 | rowid
1751 | request_id
1752 | OUT:
1753 | IN/ OUT:
1754 |
1755 | RETURNS : Error Code/NULL
1756 |
1757 | NOTES
1758 |
1759 | MODIFICATION HISTORY
1760 |
1761 | V.Srinivasan 04-MAR-01 Created for Bug# 2222791.
1762 +===========================================================================*/
1763 FUNCTION val_party_numb_ref(p_orig_system_customer_ref IN VARCHAR2,
1764 p_orig_system_party_ref IN VARCHAR2,
1765 p_party_number IN VARCHAR2,
1766 p_rowid IN ROWID,
1767 req_id IN NUMBER) RETURN VARCHAR2 is
1768 err_msg VARCHAR2(3) := 'Y4,';
1769 l_dummy VARCHAR2(3);
1770
1771
1772 CURSOR c IS -- Check if the same party has a diff party_number
1773 SELECT decode(i.party_number,p_party_number,'','Y4,')
1774 FROM ra_customers_interface_all i
1775 WHERE i.orig_system_party_ref = p_orig_system_party_ref
1776 AND i.request_id = req_id
1777 AND i.rowid <> p_rowid ;
1778
1779 CURSOR c1 IS -- Check if the same party has a diff party_number and the orig_system_party_ref is null
1780 SELECT decode(i.party_number,p_party_number,'','Y4,')
1781 FROM ra_customers_interface_all i
1782 WHERE i.orig_system_customer_ref = p_orig_system_party_ref
1783 AND i.orig_system_party_ref is null
1784 AND i.request_id = req_id
1785 AND i.rowid <> p_rowid ;
1786
1787 CURSOR c2 IS -- Check if the same party has a diff party_number and the orig_system_party_ref passed is null
1788 SELECT decode(i.party_number,p_party_number,'','Y4,')
1789 FROM ra_customers_interface_all i
1790 WHERE i.orig_system_party_ref = p_orig_system_customer_ref
1791 AND i.request_id = req_id
1792 AND i.rowid <> p_rowid ;
1793
1794 CURSOR c3 IS -- Check if the same party has a diff party_number and the orig_system_party_ref passed is null and the record in the interface table also has orig_system_party_ref as null
1795 SELECT decode(i.party_number,p_party_number,'','Y4,')
1796 FROM ra_customers_interface_all i
1797 WHERE i.orig_system_customer_ref = p_orig_system_customer_ref
1798 AND i.orig_system_party_ref is null
1799 AND i.request_id = req_id
1800 AND i.rowid <> p_rowid ;
1801
1802 BEGIN
1803
1804 if p_orig_system_party_ref is NOT NULL then
1805 OPEN c;
1806 FETCH c INTO l_dummy;
1807 IF c%NOTFOUND THEN
1808 OPEN c1;
1809 FETCH c1 INTO l_dummy;
1810
1811 IF c1%NOTFOUND THEN
1812 CLOSE c;
1813 CLOSE c1;
1814 ELSE RETURN l_dummy;
1815 END IF;
1816
1817 ELSE RETURN l_dummy;
1818 END IF;
1819
1820 IF c%ISOPEN THEN
1821 CLOSE c;
1822 END IF;
1823
1824 IF c1%ISOPEN THEN
1825 CLOSE c1;
1826 END IF;
1827
1828 RETURN NULL;
1829
1830 else
1831
1832 OPEN c2;
1833 FETCH c2 INTO l_dummy;
1834 IF c2%NOTFOUND THEN
1835 OPEN c3;
1836 FETCH c3 INTO l_dummy;
1837
1838 IF c3%NOTFOUND THEN
1839 CLOSE c2;
1840 CLOSE c3;
1841 ELSE RETURN l_dummy;
1842 END IF;
1843
1844 ELSE RETURN l_dummy;
1845 END IF;
1846
1847 IF c2%ISOPEN THEN
1848 CLOSE c2;
1849 END IF;
1850
1851 IF c3%ISOPEN THEN
1852 CLOSE c3;
1853 END IF;
1854
1855 RETURN NULL;
1856
1857 end if;
1858
1859 EXCEPTION
1860 WHEN OTHERS THEN
1861 RETURN l_dummy;
1862 END val_party_numb_ref;
1863 /*===========================================================================+
1864 | FUNCTION
1865 | val_cust_number
1866 |
1867 | DESCRIPTION
1868 | Function called by concurrent executable.
1869 |
1870 | SCOPE - PUBLIC
1871 |
1872 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1873 |
1874 | ARGUMENTS : IN:
1875 | orig_system_customer_ref
1876 | customer_number
1877 | rowid
1878 | request_id
1879 | OUT:
1880 | IN/ OUT:
1881 |
1882 | RETURNS : Error Code/NULL
1883 |
1884 | NOTES
1885 |
1886 | MODIFICATION HISTORY
1887 |
1888 | V.Srinivasan 04-MAR-01 Created for Bug# 2222791.
1889 |
1890 +===========================================================================*/
1891 FUNCTION val_cust_number(p_orig_system_customer_ref IN VARCHAR2,
1892 p_customer_number IN VARCHAR2,
1893 p_rowid IN ROWID,
1894 req_id IN NUMBER) RETURN VARCHAR2 is
1895 err_msg VARCHAR2(3) := 'A5,';
1896 l_dummy VARCHAR2(3);
1897
1898
1899 CURSOR c IS -- Check if the customer_number already exists
1900 SELECT decode(cust.orig_system_reference,p_orig_system_customer_ref,'','A5,')
1901 FROM hz_cust_accounts cust
1902 WHERE cust.account_number = p_customer_number;
1903
1904 CURSOR c1 IS -- Check if the customer_number already exists in the interface table
1905 SELECT decode(i.orig_system_customer_ref,p_orig_system_customer_ref,'','A5,')
1906 FROM ra_customers_interface_all i
1907 WHERE i.customer_number = p_customer_number
1908 AND i.request_id = req_id
1909 AND i.rowid <> p_rowid ;
1910
1911 BEGIN
1912
1913 OPEN c;
1914 FETCH c INTO l_dummy;
1915 IF c%NOTFOUND THEN
1916 OPEN c1;
1917 FETCH c1 INTO l_dummy;
1918
1919 IF c1%NOTFOUND THEN
1920 CLOSE c;
1921 CLOSE c1;
1922 ELSE RETURN l_dummy;
1923 END IF;
1924
1925 ELSE RETURN l_dummy;
1926 END IF;
1927
1928 IF c%ISOPEN THEN
1929 CLOSE c;
1930 END IF;
1931
1932 IF c1%ISOPEN THEN
1933 CLOSE c1;
1934 END IF;
1935
1936 RETURN NULL;
1937
1938 EXCEPTION
1939 WHEN OTHERS THEN
1940 RETURN l_dummy;
1941 END val_cust_number;
1942 /*===========================================================================+
1943 | FUNCTION
1944 | validate_party_site_number
1945 |
1946 | DESCRIPTION
1947 | Function called by concurrent executable.
1948 |
1949 | SCOPE - PUBLIC
1950 |
1951 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1952 |
1953 | ARGUMENTS : IN:
1954 | orig_system_address_ref
1955 | party_site_number
1956 | rowid
1957 | request_id
1958 | OUT:
1959 | IN/ OUT:
1960 |
1961 | RETURNS : Error Code/NULL
1962 |
1963 | NOTES
1964 |
1965 | MODIFICATION HISTORY
1966 |
1967 | V.Srinivasan 04-MAR-01 Created for Bug# 2222791.
1968 |
1969 +===========================================================================*/
1970 -- bug 4454799
1971 FUNCTION val_party_site_number( p_orig_system_address_ref IN VARCHAR2,
1972 p_party_site_number IN VARCHAR2,
1973 p_rowid IN ROWID,
1974 p_org_id IN NUMBER,
1975 req_id IN NUMBER) RETURN VARCHAR2 is
1976 err_msg VARCHAR2(3) := 'Y6,';
1977 l_dummy VARCHAR2(3);
1978
1979
1980 CURSOR c IS -- Check if the party_site_number already exists
1981 SELECT decode(cust_site.orig_system_reference,p_orig_system_address_ref,'','Y6,')
1982 FROM hz_party_sites site, hz_cust_acct_sites_all cust_site -- bug 4454799
1983 WHERE site.party_site_number = p_party_site_number
1984 AND cust_site.org_id = p_org_id -- bug 4454799
1985 AND site.party_site_id = cust_site.party_site_id;
1986
1987 CURSOR c1 IS -- Check if the party_site_number exists in the interface table
1988 SELECT decode(i.orig_system_address_ref,p_orig_system_address_ref,'','Y6,')
1989 FROM ra_customers_interface_all i
1990 WHERE i.party_site_number = p_party_site_number
1991 AND i.org_id = p_org_id -- bug 4454799
1992 AND i.request_id = req_id
1993 AND i.rowid <> p_rowid ;
1994
1995 BEGIN
1996
1997 OPEN c;
1998 FETCH c INTO l_dummy;
1999 IF c%NOTFOUND THEN
2000 OPEN c1;
2001 FETCH c1 INTO l_dummy;
2002
2003 IF c1%NOTFOUND THEN
2004 CLOSE c;
2005 CLOSE c1;
2006 ELSE RETURN l_dummy;
2007 END IF;
2008
2009 ELSE RETURN l_dummy;
2010 END IF;
2011
2012 IF c%ISOPEN THEN
2013 CLOSE c;
2014 END IF;
2015
2016 IF c1%ISOPEN THEN
2017 CLOSE c1;
2018 END IF;
2019
2020 RETURN NULL;
2021
2022 EXCEPTION
2023 WHEN OTHERS THEN
2024 RETURN l_dummy;
2025 END val_party_site_number;
2026
2027 /*===========================================================================+
2028 | FUNCTION
2029 | validate_tax_location
2030 |
2031 | DESCRIPTION
2032 | Function called by concurrent executable.
2033 |
2034 | SCOPE - PUBLIC
2035 |
2036 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2037 |
2038 | ARGUMENTS : IN:
2039 | orig_system_address_ref
2040 | country
2041 | city
2042 | state
2043 | county
2044 | province
2045 | postal_code
2046 | OUT:
2047 | IN/ OUT:
2048 |
2049 | RETURNS : Error Code/NULL
2050 |
2051 | NOTES
2052 |
2053 | MODIFICATION HISTORY
2054 |
2055 | P.Suresh 05/14/2002 Bug No : 2347408. Created.
2056 |
2057 +===========================================================================*/
2058 /* bug 4454799 - added argument for org_id below. */
2059 FUNCTION validate_tax_location( p_orig_system_address_ref IN VARCHAR2,
2060 p_country IN VARCHAR2,
2061 p_city IN VARCHAR2,
2062 p_state IN VARCHAR2,
2063 p_county IN VARCHAR2,
2064 p_province IN VARCHAR2,
2065 p_postal_code IN VARCHAR2,
2066 p_org_id IN NUMBER
2067 ) RETURN VARCHAR2 is
2068
2069 err_msg VARCHAR2(3) := 'y7,';
2070 l_dummy VARCHAR2(3);
2071
2072 l_location_id NUMBER;
2073 l_city VARCHAR2(60);
2074 l_state VARCHAR2(60);
2075 l_country VARCHAR2(60);
2076 l_county VARCHAR2(60);
2077 l_province VARCHAR2(60);
2078 l_postal_code VARCHAR2(60);
2079
2080
2081 l_loc_assignment_exist VARCHAR2(1) := 'N';
2082 l_is_remit_to_location VARCHAR2(1) := 'N';
2083 CURSOR C1 IS
2084 SELECT loc.location_id,loc.country,loc.city, loc.state,
2085 loc.county, loc.province, loc.postal_code
2086 FROM hz_cust_acct_sites_all cs, -- bug 4454799
2087 hz_party_sites ps,
2088 hz_locations loc
2089 WHERE cs.orig_system_reference = p_orig_system_address_ref
2090 AND cs.org_id = p_org_id -- bug 4454799
2091 AND ps.party_site_id = cs.party_site_id
2092 AND ps.location_id = loc.location_id;
2093
2094 BEGIN
2095 -- tax location validation:
2096 OPEN C1;
2097 FETCH C1 INTO l_location_id,l_country, l_city, l_state,
2098 l_county, l_province, l_postal_code;
2099 IF c1%NOTFOUND THEN
2100 CLOSE c1;
2101 END IF;
2102 -- check if the location is only used by prospect customers
2103
2104 BEGIN
2105 SELECT 'Y'
2106 INTO l_loc_assignment_exist
2107 FROM DUAL
2108 WHERE EXISTS (SELECT 1
2109 FROM hz_loc_assignments la
2110 WHERE la.location_id = l_location_id
2111 );
2112 SELECT 'Y'
2113 INTO l_is_remit_to_location
2114 FROM DUAL
2115 WHERE EXISTS (SELECT 1
2116 FROM hz_party_sites ps
2117 WHERE ps.location_id = l_location_id
2118 AND ps.party_id = -1
2119 );
2120 EXCEPTION
2121 WHEN NO_DATA_FOUND THEN
2122 NULL;
2123 END;
2124
2125 IF l_is_remit_to_location = 'N' and l_loc_assignment_exist = 'Y' THEN
2126 -- check if the taxable components are changed
2127 IF ( p_country <> l_country
2128 OR p_city <> l_city
2129 OR p_state <> l_state
2130 OR p_county <> l_county
2131 OR p_province <> l_province
2132 OR p_postal_code <> l_postal_code
2133 )
2134 THEN
2135 IF ARH_ADDR_PKG.check_tran_for_all_accts(l_location_id)
2136 THEN
2137 return err_msg;
2138 ELSE
2139 return NULL;
2140
2141 END IF;
2142 END IF;
2143 END IF; -- remit and loc
2144 RETURN NULL;
2145 EXCEPTION
2146 WHEN OTHERS THEN
2147 RETURN err_msg;
2148 END validate_tax_location;
2149
2150 /*===========================================================================+
2151 | PROCEDURE
2152 | reset_who
2153 |
2154 | DESCRIPTION
2155 | Cache who columns.
2156 |
2157 | SCOPE - PRIVATE
2158 |
2159 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2160 |
2161 | ARGUMENTS : IN:
2162 | OUT:
2163 | IN/ OUT:
2164 | NOTES
2165 |
2166 | MODIFICATION HISTORY
2167 |
2168 | Jianying Huang 07/12/2002 Bug No : 2460837. Created.
2169 |
2170 +===========================================================================*/
2171
2172 PROCEDURE reset_who IS
2173 BEGIN
2174 g_created_by := hz_utility_v2pub.created_by;
2175 g_last_update_login := hz_utility_v2pub.last_update_login;
2176 g_last_updated_by := hz_utility_v2pub.last_updated_by;
2177 g_request_id := hz_utility_v2pub.request_id;
2178 g_program_application_id := hz_utility_v2pub.program_application_id;
2179 g_program_id := hz_utility_v2pub.program_id;
2180 END reset_who;
2181
2182 /*===========================================================================+
2183 | PROCEDURE
2184 | update_exception_table
2185 |
2186 | DESCRIPTION
2187 | Update win source exception table when mix-n-match is seted up.
2188 |
2189 | SCOPE - PRIVATE
2190 |
2191 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2192 |
2193 | ARGUMENTS : IN:
2194 | p_i_party_id
2195 | p_entity_attr_id
2196 | p_value
2197 | p_ue_ranking
2198 | p_sst_is_null
2199 | OUT:
2200 | IN/ OUT:
2201 | NOTES
2202 |
2203 | MODIFICATION HISTORY
2204 |
2205 | Jianying Huang 07/12/2002 Bug No : 2460837. Created.
2206 |
2207 +===========================================================================*/
2208
2209 PROCEDURE update_exception_table (
2210 p_i_party_id IN t_id,
2211 p_entity_attr_id IN NUMBER,
2212 p_value IN t_varchar500,
2213 p_ue_ranking IN NUMBER,
2214 p_sst_is_null IN t_flag
2215 ) IS
2216 i_party_id t_id := t_id();
2217 i_flag t_flag := t_flag();
2218 total NUMBER := 0;
2219 BEGIN
2220 i_party_id.extend(p_i_party_id.COUNT);
2221 i_flag.extend(p_i_party_id.COUNT);
2222
2223 FOR i IN 1..p_i_party_id.COUNT LOOP
2224 IF p_value(i) IS NOT NULL
2225 THEN
2226 total := total + 1;
2227 i_party_id(total) := p_i_party_id(i);
2228 i_flag(total) := p_sst_is_null(i);
2229 END IF;
2230 END LOOP;
2231
2232 IF p_ue_ranking = 1 THEN
2233 FORALL i IN 1..total
2234 DELETE hz_win_source_exceps
2235 WHERE party_id = i_party_id(i)
2236 AND entity_attr_id = p_entity_attr_id;
2237 ELSE
2238 FORALL i IN 1..total
2239 UPDATE hz_win_source_exceps exp
2240 SET content_source_type = 'USER_ENTERED',
2241 exception_type = (
2242 SELECT DECODE(sign(s.ranking-p_ue_ranking), 0, exp.exception_type,
2243 1, 'Migration', -1, 'Exception')
2244 FROM hz_select_data_sources s
2245 WHERE entity_attr_id = p_entity_attr_id
2246 AND content_source_type = exp.content_source_type),
2247 last_updated_by = g_last_updated_by,
2248 last_update_login = g_last_update_login,
2249 last_update_date = SYSDATE,
2250 request_id = g_request_id,
2251 program_application_id = g_program_application_id,
2252 program_id = g_program_id,
2253 program_update_date = SYSDATE
2254 WHERE party_id = i_party_id(i)
2255 AND entity_attr_id = p_entity_attr_id;
2256
2257 FORALL i IN 1..total
2258 INSERT INTO hz_win_source_exceps (
2259 party_id,
2260 entity_attr_id,
2261 content_source_type,
2262 exception_type,
2263 created_by,
2264 creation_date,
2265 last_update_login,
2266 last_update_date,
2267 last_updated_by,
2268 request_id,
2269 program_application_id,
2270 program_id,
2271 program_update_date
2272 ) SELECT
2273 i_party_id(i),
2274 p_entity_attr_id,
2275 'USER_ENTERED',
2276 decode(i_flag(i), '', 'Migration', 'Exception'),
2277 g_created_by,
2278 SYSDATE,
2279 g_last_update_login,
2280 SYSDATE,
2281 g_last_updated_by,
2282 g_request_id,
2283 g_program_application_id,
2284 g_program_id,
2285 SYSDATE
2286 FROM dual
2287 WHERE NOT EXISTS (
2288 SELECT 'Y'
2289 FROM hz_win_source_exceps
2290 WHERE party_id = i_party_id(i)
2291 AND entity_attr_id = p_entity_attr_id );
2292 END IF;
2293 END update_exception_table;
2294
2295 /*===========================================================================+
2296 | PROCEDURE
2297 | update_org_ue_profile
2298 |
2299 | DESCRIPTION
2300 | The procedure will be called in racudc.lpc to sync. user-entered profile
2301 | and sst profile when mix-n-match is seted up.
2302 |
2303 | SCOPE - PUBLIC
2304 |
2305 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2306 |
2307 | ARGUMENTS : IN:
2308 | p_request_id
2309 | OUT:
2310 | IN/ OUT:
2311 | NOTES
2312 |
2313 | MODIFICATION HISTORY
2314 |
2315 | Jianying Huang 07/12/2002 Bug No : 2460837. Created.
2316 | Sisir 05/07/2003 Bug No : 2970763;Before create/update
2317 | the profile hz_profile_versioning is
2318 | checked and added version_number in
2319 | insert/update clause.
2320 +===========================================================================*/
2321
2322 PROCEDURE update_org_ue_profile (
2323 p_request_id IN NUMBER
2324 ) IS
2325
2326 -- The cursor is used to select interface related value for user-entered
2327 -- profile.
2328 CURSOR c_entity IS
2329 SELECT /* decode(trunc(org.effective_start_date),trunc(sysdate),'U','C') create_update_flag,*/
2330 decode(fnd_profile.value ('HZ_PROFILE_VERSION'),'NEW_VERSION','C','NO_VERSION','U',
2331 decode(trunc(org.effective_start_date),trunc(sysdate),'U','C')) create_update_flag,
2332 org.organization_profile_id,
2333 org.party_id,
2334 -- User NVL for bug 1404725. We do not need NVL on
2335 -- customer name because it is a not-null column.
2336 -- However, for some reason we did not do NVL on
2337 -- customer name phonetic. Please see racudc.lpc.
2338 -- If we decide to do NVL on phoneic also, we need
2339 -- to modify both racudc.lpc and this procedure.
2340 -- By selecting non-NVL value here is to differentiate
2341 -- when customer is updating the column by passing
2342 -- value and when he/she does not want to update the
2343 -- column by setting the column to null. This information
2344 -- will be used when update data source exception table.
2345 nvl(i.jgzz_fiscal_code, org.jgzz_fiscal_code),
2346 i.jgzz_fiscal_code,
2347 decode(sst.jgzz_fiscal_code, '', 'Y', 'N'),
2348 i.customer_name,
2349 decode(sst.organization_name, '', 'Y', 'N'),
2350 i.customer_name_phonetic,
2351 decode(sst.organization_name_phonetic, '', 'Y', 'N'),
2352 nvl(i.cust_tax_reference, org.tax_reference),
2353 i.cust_tax_reference,
2354 decode(sst.tax_reference, '', 'Y', 'N'),nvl(org.version_number,1)+1
2355 FROM hz_organization_profiles org,
2356 hz_organization_profiles sst,
2357 ra_customers_interface_all i, -- Bug 4956131
2358 hz_cust_accounts cust,
2359 (SELECT min(i1.rowid) myrowid
2360 FROM ra_customers_interface_all i1 -- Bug 4956131
2361 WHERE i1.request_id = p_request_id
2362 AND i1.interface_status IS NULL
2363 AND i1.insert_update_flag='U'
2364 AND NVL(i1.person_flag,'N') = 'N'
2365 GROUP BY i1.orig_system_customer_ref) temp
2366 WHERE i.rowid = temp.myrowid
2367 AND i.request_id = p_request_id
2368 AND i.orig_system_customer_ref = cust.orig_system_reference
2369 AND cust.party_id = org.party_id
2370 AND org.effective_end_date is null
2371 AND org.actual_content_source = 'USER_ENTERED'
2372 AND sst.party_id = org.party_id
2373 AND sst.effective_end_date is null
2374 AND sst.actual_content_source = 'SST'
2375 ORDER BY create_update_flag;
2376
2377 i_create_update_flag t_flag;
2378 i_ue_profile_id t_id;
2379 i_party_id t_id;
2380 i_jgzz_fiscal_code t_varchar500;
2381 i1_jgzz_fiscal_code t_varchar500;
2382 ss_jgzz_fiscal_code t_flag;
2383 id_jgzz_fiscal_code NUMBER;
2384 rk_jgzz_fiscal_code NUMBER;
2385 i_organization_name t_varchar500;
2386 ss_organization_name t_flag;
2387 id_organization_name NUMBER;
2388 rk_organization_name NUMBER;
2389 i_organization_name_phonetic t_varchar500;
2390 ss_organization_name_phonetic t_flag;
2391 id_organization_name_phonetic NUMBER;
2392 rk_organization_name_phonetic NUMBER;
2393 i_tax_reference t_varchar500;
2394 i1_tax_reference t_varchar500;
2395 ss_tax_reference t_flag;
2396 id_tax_reference NUMBER;
2397 rk_tax_reference NUMBER;
2398 i_version_number t_id;
2399
2400 -- The cursor is used to return entity_attr_id for
2401 -- an attribute and the ranking of user-entered data
2402 -- source.
2403 CURSOR c_attributes (
2404 p_attribute_name VARCHAR2
2405 ) IS
2406 SELECT s.entity_attr_id, s.ranking
2407 FROM hz_entity_attributes e,
2408 hz_select_data_sources s
2409 WHERE e.attribute_name = UPPER(p_attribute_name)
2410 AND e.entity_attr_id = s.entity_attr_id
2411 AND s.content_source_type = 'USER_ENTERED';
2412
2413 l_entity_attr_id NUMBER;
2414 l_enabled VARCHAR2(1);
2415 subtotal NUMBER := 0;
2416 create_start NUMBER := 0;
2417 create_end NUMBER := 0;
2418 update_start NUMBER := 0;
2419 update_end NUMBER := 0;
2420 rows NUMBER := 500;
2421 l_last_fetch BOOLEAN := FALSE;
2422
2423 BEGIN
2424
2425 -- check if mix-n-match is set up on hz_organization_profiles.
2426 l_enabled :=
2427 HZ_MIXNM_UTILITY.isMixNMatchEnabled(
2428 'HZ_ORGANIZATION_PROFILES', l_entity_attr_id);
2429 IF l_enabled = 'N' THEN
2430 RETURN;
2431 END IF;
2432
2433 -- disable policy function.
2434 hz_common_pub.disable_cont_source_security;
2435
2436 -- reset who-column.
2437 reset_who;
2438
2439 l_last_fetch := FALSE;
2440
2441 OPEN c_entity;
2442 LOOP
2443 <<myfetch>>
2444 FETCH c_entity BULK COLLECT INTO
2445 i_create_update_flag,
2446 i_ue_profile_id,
2447 i_party_id,
2448 i_jgzz_fiscal_code,
2449 i1_jgzz_fiscal_code,
2450 ss_jgzz_fiscal_code,
2451 i_organization_name,
2452 ss_organization_name,
2453 i_organization_name_phonetic,
2454 ss_organization_name_phonetic,
2455 i_tax_reference,
2456 i1_tax_reference,
2457 ss_tax_reference,
2458 i_version_number LIMIT rows;
2459
2460 subtotal := i_party_id.COUNT;
2461 IF c_entity%NOTFOUND THEN
2462 l_last_fetch := TRUE;
2463 END IF;
2464 IF subtotal = 0 AND l_last_fetch THEN
2465 EXIT;
2466 END IF;
2467 IF subtotal = 0 THEN
2468 GOTO myfetch;
2469 END IF;
2470
2471 -- split parties for which we need to create new user-entered
2472 -- profiles for date tracking purpose and for which we need to
2473 -- update existing user-entered profiles.
2474
2475 create_start := 0; create_end := -1;
2476 update_start := 0; update_end := -1;
2477
2478 FOR i IN 1..subtotal LOOP
2479 IF i_create_update_flag(i) = 'C' THEN
2480 IF create_start = 0 THEN create_start := i; END IF;
2481 ELSE
2482 IF update_start = 0 THEN
2483 update_start := i;
2484 IF create_start > 0 THEN create_end := i-1; END IF;
2485 END IF;
2486 EXIT;
2487 END IF;
2488 END LOOP;
2489 IF create_start > 0 AND create_end = -1 THEN create_end := subtotal; END IF;
2490 IF update_start > 0 AND update_end = -1 THEN update_end := subtotal; END IF;
2491
2492 -- end-dated user-entered profiles for which we need to create new.
2493
2494 FORALL i IN create_start..create_end
2495 UPDATE hz_organization_profiles
2496 SET effective_end_date = decode(trunc(effective_start_date),trunc(sysdate),trunc(sysdate),TRUNC(SYSDATE-1))
2497 WHERE organization_profile_id = i_ue_profile_id(i);
2498
2499 -- create new user entered profiles
2500
2501 FORALL i IN create_start..create_end
2502 INSERT INTO hz_organization_profiles (
2503 created_by,
2504 creation_date,
2505 last_update_login,
2506 last_update_date,
2507 last_updated_by,
2508 request_id,
2509 program_application_id,
2510 program_id,
2511 program_update_date,
2512 content_source_type,
2513 actual_content_source,
2514 created_by_module,
2515 application_id,
2516 organization_profile_id,
2517 party_id,
2518 effective_start_date,
2519 object_version_number,
2520 jgzz_fiscal_code,
2521 organization_name,
2522 organization_name_phonetic,
2523 tax_reference,
2524 version_number
2525 ) VALUES (
2526 g_created_by,
2527 SYSDATE,
2528 g_last_update_login,
2529 SYSDATE,
2530 g_last_updated_by,
2531 g_request_id,
2532 g_program_application_id,
2533 g_program_id,
2534 SYSDATE,
2535 'USER_ENTERED',
2536 'USER_ENTERED',
2537 'TCA-CUSTOMER-INTERFACE',
2538 222,
2539 hz_organization_profiles_s.nextval,
2540 i_party_id(i),
2541 SYSDATE,
2542 1,
2543 i_jgzz_fiscal_code(i),
2544 i_organization_name(i),
2545 i_organization_name_phonetic(i),
2546 i_tax_reference(i),
2547 i_version_number(i)
2548 );
2549
2550 -- update user entered profiles
2551
2552 FORALL i IN update_start..update_end
2553 UPDATE hz_organization_profiles
2554 SET
2555 last_updated_by = g_last_updated_by,
2556 last_update_login = g_last_update_login,
2557 last_update_date = SYSDATE,
2558 request_id = g_request_id,
2559 program_application_id = g_program_application_id,
2560 program_id = g_program_id,
2561 program_update_date = SYSDATE,
2562 jgzz_fiscal_code = i_jgzz_fiscal_code(i),
2563 organization_name = i_organization_name(i),
2564 organization_name_phonetic = i_organization_name_phonetic(i),
2565 tax_reference = i_tax_reference(i),
2566 version_number = nvl(version_number,1)+1
2567 WHERE organization_profile_id = i_ue_profile_id(i);
2568
2569 -- update exception for jgzz_fiscal_code
2570 OPEN c_attributes('JGZZ_FISCAL_CODE');
2571 FETCH c_attributes INTO id_jgzz_fiscal_code, rk_jgzz_fiscal_code;
2572 IF c_attributes%FOUND THEN
2573 update_exception_table(i_party_id,id_jgzz_fiscal_code,i1_jgzz_fiscal_code,rk_jgzz_fiscal_code,ss_jgzz_fiscal_code);
2574 END IF;
2575 CLOSE c_attributes;
2576
2577 -- update exception for organization_name
2578 OPEN c_attributes('ORGANIZATION_NAME');
2579 FETCH c_attributes INTO id_organization_name, rk_organization_name;
2580 IF c_attributes%FOUND THEN
2581 update_exception_table(i_party_id,id_organization_name,i_organization_name,rk_organization_name,ss_organization_name);
2582 END IF;
2583 CLOSE c_attributes;
2584
2585 -- update exception for organization_name_phonetic
2586 OPEN c_attributes('ORGANIZATION_NAME_PHONETIC');
2587 FETCH c_attributes INTO id_organization_name_phonetic, rk_organization_name_phonetic;
2588 IF c_attributes%FOUND THEN
2589 update_exception_table(i_party_id,id_organization_name_phonetic,i_organization_name_phonetic,rk_organization_name_phonetic,ss_organization_name_phonetic);
2590 END IF;
2591 CLOSE c_attributes;
2592
2593 -- update exception for tax_reference
2594 OPEN c_attributes('TAX_REFERENCE');
2595 FETCH c_attributes INTO id_tax_reference, rk_tax_reference;
2596 IF c_attributes%FOUND THEN
2597 update_exception_table(i_party_id,id_tax_reference,i1_tax_reference,rk_tax_reference,ss_tax_reference);
2598 END IF;
2599 CLOSE c_attributes;
2600
2601 IF l_last_fetch = TRUE THEN
2602 EXIT;
2603 END IF;
2604 END LOOP;
2605 CLOSE c_entity;
2606
2607 -- enable policy function.
2608 hz_common_pub.enable_cont_source_security;
2609
2610 END update_org_ue_profile;
2611
2612 /*===========================================================================+
2613 | PROCEDURE
2614 | update_per_ue_profile
2615 |
2616 | DESCRIPTION
2617 | The procedure will be called in racudc.lpc to sync. user-entered profile
2618 | and sst profile when mix-n-match is seted up.
2619 |
2620 | SCOPE - PUBLIC
2621 |
2622 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2623 |
2624 | ARGUMENTS : IN:
2625 | p_request_id
2626 | OUT:
2627 | IN/ OUT:
2628 | NOTES
2629 |
2630 | MODIFICATION HISTORY
2631 |
2632 | Jianying Huang 07/12/2002 Bug No : 2460837. Created.
2633 | Sisir 06/09/2003 Bug No : 2970763 Added additional
2634 | clause of hz_profile_version in decode
2635 | stmt and also added version_number
2636 | column in the select clause.
2637 +===========================================================================*/
2638
2639 PROCEDURE update_per_ue_profile (
2640 p_request_id IN NUMBER
2641 ) IS
2642
2643 -- The cursor is used to select interface related value for user-entered
2644 -- profile.
2645 CURSOR c_entity IS
2646 SELECT /* decode(trunc(per.effective_start_date),trunc(sysdate),'U','C') create_update_flag, */
2647 decode(fnd_profile.value ('HZ_PROFILE_VERSION'),'NEW_VERSION','C','NO_VERSION','U',
2648 decode(trunc(per.effective_start_date),trunc(sysdate),'U','C')) create_update_flag,
2649 per.person_profile_id,
2650 per.party_id,
2651 -- User NVL for bug 1404725 when we have to take
2652 -- value from hz_person_profiles. By selecting
2653 -- non-NVL value here is to differentiate when
2654 -- customer is updating the column by passing value
2655 -- and when he/she does not want to update the column
2656 -- by setting the column to null. This information
2657 -- will be used when update data source exception table.
2658 nvl(i.jgzz_fiscal_code, per.jgzz_fiscal_code),
2659 i.jgzz_fiscal_code,
2660 decode(sst.jgzz_fiscal_code, '', 'Y', 'N'),
2661 i.customer_name,
2662 decode(sst.person_name, '', 'Y', 'N'),
2663 decode(i.person_first_name,'',decode(i.person_last_name,'',substrb(i.customer_name,1,150),''),i.person_first_name),
2664 decode(sst.person_first_name, '', 'Y', 'N'),
2665 nvl(i.person_last_name, per.person_last_name),
2666 i.person_last_name,
2667 decode(sst.person_last_name, '', 'Y', 'N'),
2668 nvl(i.customer_name_phonetic, per.person_name_phonetic),
2669 i.customer_name_phonetic,
2670 decode(sst.person_name_phonetic, '', 'Y', 'N'),
2671 decode(nvl(i.person_flag,'N'),'Y',i.customer_name_phonetic,''),
2672 decode(sst.person_first_name_phonetic, '', 'Y', 'N'),
2673 decode(nvl(i.person_flag,'N'),'Y',i.customer_name_phonetic,''),
2674 decode(sst.person_last_name_phonetic, '', 'Y', 'N'),
2675 nvl(i.cust_tax_reference, per.tax_reference),
2676 i.cust_tax_reference,
2677 decode(sst.tax_reference, '', 'Y', 'N'),nvl(per.version_number,1)+1
2678 FROM hz_person_profiles per,
2679 hz_person_profiles sst,
2680 ra_customers_interface_all i, -- Bug 4956131
2681 hz_cust_accounts cust,
2682 (SELECT min(i1.rowid) myrowid
2683 FROM ra_customers_interface_all i1 -- Bug 4956131
2684 WHERE i1.request_id = p_request_id
2685 AND i1.interface_status IS NULL
2686 AND i1.insert_update_flag='U'
2687 AND i1.person_flag = 'Y'
2688 GROUP BY i1.orig_system_customer_ref) temp
2689 WHERE i.rowid = temp.myrowid
2690 AND i.request_id = p_request_id
2691 AND i.orig_system_customer_ref = cust.orig_system_reference
2692 AND cust.party_id = per.party_id
2693 AND per.effective_end_date is null
2694 AND per.actual_content_source = 'USER_ENTERED'
2695 AND sst.party_id = per.party_id
2696 AND sst.effective_end_date is null
2697 AND sst.actual_content_source = 'SST'
2698 ORDER BY create_update_flag;
2699
2700 i_create_update_flag t_flag;
2701 i_ue_profile_id t_id;
2702 i_party_id t_id;
2703 i_jgzz_fiscal_code t_varchar500;
2704 i1_jgzz_fiscal_code t_varchar500;
2705 ss_jgzz_fiscal_code t_flag;
2706 id_jgzz_fiscal_code NUMBER;
2707 rk_jgzz_fiscal_code NUMBER;
2708 i_person_name t_varchar500;
2709 ss_person_name t_flag;
2710 id_person_name NUMBER;
2711 rk_person_name NUMBER;
2712 i_person_first_name t_varchar500;
2713 ss_person_first_name t_flag;
2714 id_person_first_name NUMBER;
2715 rk_person_first_name NUMBER;
2716 i_person_last_name t_varchar500;
2717 i1_person_last_name t_varchar500;
2718 ss_person_last_name t_flag;
2719 id_person_last_name NUMBER;
2720 rk_person_last_name NUMBER;
2721 i_person_name_phonetic t_varchar500;
2722 i1_person_name_phonetic t_varchar500;
2723 ss_person_name_phonetic t_flag;
2724 id_person_name_phonetic NUMBER;
2725 rk_person_name_phonetic NUMBER;
2726 i_person_first_name_phonetic t_varchar500;
2727 ss_person_first_name_phonetic t_flag;
2728 id_person_first_name_phonetic NUMBER;
2729 rk_person_first_name_phonetic NUMBER;
2730 i_person_last_name_phonetic t_varchar500;
2731 ss_person_last_name_phonetic t_flag;
2732 id_person_last_name_phonetic NUMBER;
2733 rk_person_last_name_phonetic NUMBER;
2734 i_tax_reference t_varchar500;
2735 i1_tax_reference t_varchar500;
2736 ss_tax_reference t_flag;
2737 id_tax_reference NUMBER;
2738 rk_tax_reference NUMBER;
2739 i_version_number t_id;
2740
2741 -- The cursor is used to return entity_attr_id for
2742 -- an attribute and the ranking of user-entered data
2743 -- source.
2744 CURSOR c_attributes (
2745 p_attribute_name VARCHAR2
2746 ) IS
2747 SELECT s.entity_attr_id, s.ranking
2748 FROM hz_entity_attributes e,
2749 hz_select_data_sources s
2750 WHERE e.attribute_name = UPPER(p_attribute_name)
2751 AND e.entity_attr_id = s.entity_attr_id
2752 AND s.content_source_type = 'USER_ENTERED';
2753
2754 l_entity_attr_id NUMBER;
2755 l_enabled VARCHAR2(1);
2756 subtotal NUMBER := 0;
2757 create_start NUMBER := 0;
2758 create_end NUMBER := 0;
2759 update_start NUMBER := 0;
2760 update_end NUMBER := 0;
2761 rows NUMBER := 500;
2762 l_last_fetch BOOLEAN := FALSE;
2763
2764 BEGIN
2765
2766 -- check if mix-n-match is set up on hz_person_profiles.
2767 l_enabled :=
2768 HZ_MIXNM_UTILITY.isMixNMatchEnabled(
2769 'HZ_PERSON_PROFILES', l_entity_attr_id);
2770 IF l_enabled = 'N' THEN
2771 RETURN;
2772 END IF;
2773
2774 -- disable policy function.
2775 hz_common_pub.disable_cont_source_security;
2776
2777 -- reset who-column.
2778 reset_who;
2779
2780 l_last_fetch := FALSE;
2781
2782 OPEN c_entity;
2783 LOOP
2784 <<myfetch>>
2785 FETCH c_entity BULK COLLECT INTO
2786 i_create_update_flag,
2787 i_ue_profile_id,
2788 i_party_id,
2789 i_jgzz_fiscal_code,
2790 i1_jgzz_fiscal_code,
2791 ss_jgzz_fiscal_code,
2792 i_person_name,
2793 ss_person_name,
2794 i_person_first_name,
2795 ss_person_first_name,
2796 i_person_last_name,
2797 i1_person_last_name,
2798 ss_person_last_name,
2799 i_person_name_phonetic,
2800 i1_person_name_phonetic,
2801 ss_person_name_phonetic,
2802 i_person_first_name_phonetic,
2803 ss_person_first_name_phonetic,
2804 i_person_last_name_phonetic,
2805 ss_person_last_name_phonetic,
2806 i_tax_reference,
2807 i1_tax_reference,
2808 ss_tax_reference,
2809 i_version_number LIMIT rows;
2810
2811 subtotal := i_party_id.COUNT;
2812 IF c_entity%NOTFOUND THEN
2813 l_last_fetch := TRUE;
2814 END IF;
2815 IF subtotal = 0 AND l_last_fetch THEN
2816 EXIT;
2817 END IF;
2818 IF subtotal = 0 THEN
2819 GOTO myfetch;
2820 END IF;
2821
2822 -- split parties for which we need to create new user-entered
2823 -- profiles for date tracking purpose and for which we need to
2824 -- update existing user-entered profiles.
2825
2826 create_start := 0; create_end := -1;
2827 update_start := 0; update_end := -1;
2828
2829 FOR i IN 1..subtotal LOOP
2830 IF i_create_update_flag(i) = 'C' THEN
2831 IF create_start = 0 THEN create_start := i; END IF;
2832 ELSE
2833 IF update_start = 0 THEN
2834 update_start := i;
2835 IF create_start > 0 THEN create_end := i-1; END IF;
2836 END IF;
2837 EXIT;
2838 END IF;
2839 END LOOP;
2840 IF create_start > 0 AND create_end = -1 THEN create_end := subtotal; END IF;
2841 IF update_start > 0 AND update_end = -1 THEN update_end := subtotal; END IF;
2842
2843 -- end-dated user-entered profiles for which we need to create new.
2844
2845 FORALL i IN create_start..create_end
2846 UPDATE hz_person_profiles
2847 SET effective_end_date = decode(trunc(effective_start_date),trunc(sysdate),trunc(sysdate),TRUNC(SYSDATE-1))
2848 WHERE person_profile_id = i_ue_profile_id(i);
2849
2850 -- create new user entered profiles
2851
2852 FORALL i IN create_start..create_end
2853 INSERT INTO hz_person_profiles (
2854 created_by,
2855 creation_date,
2856 last_update_login,
2857 last_update_date,
2858 last_updated_by,
2859 request_id,
2860 program_application_id,
2861 program_id,
2862 program_update_date,
2863 content_source_type,
2864 actual_content_source,
2865 created_by_module,
2866 application_id,
2867 person_profile_id,
2868 party_id,
2869 effective_start_date,
2870 object_version_number,
2871 jgzz_fiscal_code,
2872 person_name,
2873 person_first_name,
2874 person_last_name,
2875 person_name_phonetic,
2876 person_first_name_phonetic,
2877 person_last_name_phonetic,
2878 tax_reference,
2879 version_number
2880 ) VALUES (
2881 g_created_by,
2882 SYSDATE,
2883 g_last_update_login,
2884 SYSDATE,
2885 g_last_updated_by,
2886 g_request_id,
2887 g_program_application_id,
2888 g_program_id,
2889 SYSDATE,
2890 'USER_ENTERED',
2891 'USER_ENTERED',
2892 'TCA-CUSTOMER-INTERFACE',
2893 222,
2894 hz_person_profiles_s.nextval,
2895 i_party_id(i),
2896 SYSDATE,
2897 1,
2898 i_jgzz_fiscal_code(i),
2899 i_person_name(i),
2900 i_person_first_name(i),
2901 i_person_last_name(i),
2902 i_person_name_phonetic(i),
2903 i_person_first_name_phonetic(i),
2904 i_person_last_name_phonetic(i),
2905 i_tax_reference(i),
2906 i_version_number(i)
2907 );
2908
2909 -- update user entered profiles
2910
2911 FORALL i IN update_start..update_end
2912 UPDATE hz_person_profiles
2913 SET
2914 last_updated_by = g_last_updated_by,
2915 last_update_login = g_last_update_login,
2916 last_update_date = SYSDATE,
2917 request_id = g_request_id,
2918 program_application_id = g_program_application_id,
2919 program_id = g_program_id,
2920 program_update_date = SYSDATE,
2921 jgzz_fiscal_code = i_jgzz_fiscal_code(i),
2922 person_name = i_person_name(i),
2923 person_first_name = i_person_first_name(i),
2924 person_last_name = i_person_last_name(i),
2925 person_name_phonetic = i_person_name_phonetic(i),
2926 person_first_name_phonetic = i_person_first_name_phonetic(i),
2927 person_last_name_phonetic = i_person_last_name_phonetic(i),
2928 tax_reference = i_tax_reference(i),
2929 version_number = nvl(version_number,1)+1
2930 WHERE person_profile_id = i_ue_profile_id(i);
2931
2932 -- update exception for jgzz_fiscal_code
2933 OPEN c_attributes('JGZZ_FISCAL_CODE');
2934 FETCH c_attributes INTO id_jgzz_fiscal_code, rk_jgzz_fiscal_code;
2935 IF c_attributes%FOUND THEN
2936 update_exception_table(i_party_id,id_jgzz_fiscal_code,i1_jgzz_fiscal_code,rk_jgzz_fiscal_code,ss_jgzz_fiscal_code);
2937 END IF;
2938 CLOSE c_attributes;
2939
2940 -- update exception for person_name
2941 OPEN c_attributes('PERSON_NAME');
2942 FETCH c_attributes INTO id_person_name, rk_person_name;
2943 IF c_attributes%FOUND THEN
2944 update_exception_table(i_party_id,id_person_name,i_person_name,rk_person_name,ss_person_name);
2945 END IF;
2946 CLOSE c_attributes;
2947
2948 -- update exception for person_first_name
2949 OPEN c_attributes('PERSON_FIRST_NAME');
2950 FETCH c_attributes INTO id_person_first_name, rk_person_first_name;
2951 IF c_attributes%FOUND THEN
2952 update_exception_table(i_party_id,id_person_first_name,i_person_first_name,rk_person_first_name,ss_person_first_name);
2953 END IF;
2954 CLOSE c_attributes;
2955
2956 -- update exception for person_last_name
2957 OPEN c_attributes('PERSON_LAST_NAME');
2958 FETCH c_attributes INTO id_person_last_name, rk_person_last_name;
2959 IF c_attributes%FOUND THEN
2960 update_exception_table(i_party_id,id_person_last_name,i1_person_last_name,rk_person_last_name,ss_person_last_name);
2961 END IF;
2962 CLOSE c_attributes;
2963
2964 -- update exception for person_name_phonetic
2965 OPEN c_attributes('PERSON_NAME_PHONETIC');
2966 FETCH c_attributes INTO id_person_name_phonetic, rk_person_name_phonetic;
2967 IF c_attributes%FOUND THEN
2968 update_exception_table(i_party_id,id_person_name_phonetic,i1_person_name_phonetic,rk_person_name_phonetic,ss_person_name_phonetic);
2969 END IF;
2970 CLOSE c_attributes;
2971
2972 -- update exception for person_first_name_phonetic
2973 OPEN c_attributes('PERSON_FIRST_NAME_PHONETIC');
2974 FETCH c_attributes INTO id_person_first_name_phonetic, rk_person_first_name_phonetic;
2975 IF c_attributes%FOUND THEN
2976 update_exception_table(i_party_id,id_person_first_name_phonetic,i_person_first_name_phonetic,rk_person_first_name_phonetic,ss_person_first_name_phonetic);
2977 END IF;
2978 CLOSE c_attributes;
2979
2980 -- update exception for person_last_name_phonetic
2981 OPEN c_attributes('PERSON_LAST_NAME_PHONETIC');
2982 FETCH c_attributes INTO id_person_last_name_phonetic, rk_person_last_name_phonetic;
2983 IF c_attributes%FOUND THEN
2984 update_exception_table(i_party_id,id_person_last_name_phonetic,i_person_last_name_phonetic,rk_person_last_name_phonetic,ss_person_last_name_phonetic);
2985 END IF;
2986 CLOSE c_attributes;
2987
2988 -- update exception for tax_reference
2989 OPEN c_attributes('TAX_REFERENCE');
2990 FETCH c_attributes INTO id_tax_reference, rk_tax_reference;
2991 IF c_attributes%FOUND THEN
2992 update_exception_table(i_party_id,id_tax_reference,i1_tax_reference,rk_tax_reference,ss_tax_reference);
2993 END IF;
2994 CLOSE c_attributes;
2995
2996 IF l_last_fetch = TRUE THEN
2997 EXIT;
2998 END IF;
2999 END LOOP;
3000 CLOSE c_entity;
3001
3002 -- enable policy function.
3003 hz_common_pub.enable_cont_source_security;
3004
3005 END update_per_ue_profile;
3006
3007 /*===========================================================================+
3008 | FUNCTION
3009 | validate_primary_flag
3010 |
3011 | DESCRIPTION
3012 | Validating Primary site use flag.
3013 |
3014 | SCOPE - PRIVATE
3015 |
3016 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3017 |
3018 | ARGUMENTS : IN: p_orig_system_customer_ref
3019 p_site_use_code
3020 | OUT:
3021 | IN/ OUT:
3022 | NOTES
3023 |
3024 | MODIFICATION HISTORY
3025 |
3026 | Rajeshwari P 03/21/2003 Bug No : 2802126. Created.
3027 | Rajesh Jose 04/06/2004 Bug 3535808. The cursor returns
3028 | incorrect data when a 'DUN', 'LEGAL' or
3029 | 'STMTS' site has been inactivated.
3030 |
3031 +===========================================================================*/
3032 /* bug 4454799 - added argument for org_id below. */
3033 FUNCTION validate_primary_flag( p_orig_system_customer_ref IN VARCHAR2,
3034 p_site_use_code IN VARCHAR2,
3035 p_org_id IN NUMBER )
3036 RETURN VARCHAR2 IS
3037
3038 primaryflag VARCHAR2(100);
3039
3040 /* 3535808. Modified cursor. */
3041 /* 4588090. Modified site use condition in the cursor. */
3042 CURSOR pflag is
3043 SELECT su.primary_flag
3044 FROM hz_cust_accounts cust,
3045 hz_cust_acct_sites_all site, -- bug 4454799
3046 hz_cust_site_uses_all su -- bug 4454799
3047 WHERE cust.orig_system_reference = p_orig_system_customer_ref
3048 and cust.cust_account_id = site.cust_account_id
3049 and site.cust_acct_site_id = su.cust_acct_site_id
3050 and site.org_id = p_org_id -- bug 4454799
3051 and site.org_id = su.org_id -- bug 4454799
3052 and su.site_use_code in ('STMTS','DUN','LEGAL')
3053 and su.site_use_code = p_site_use_code
3054 and su.status = 'A'
3055 and rownum = 1;
3056
3057
3058 BEGIN
3059 open pflag;
3060 fetch pflag into primaryflag;
3061 close pflag;
3062 RETURN primaryflag;
3063 EXCEPTION
3064 WHEN NO_DATA_FOUND THEN
3065 RETURN null;
3066 WHEN OTHERS THEN
3067 RAISE;
3068
3069 END validate_primary_flag;
3070 /*===========================================================================+
3071 | FUNCTION
3072 | set_primary_flag
3073 |
3074 | DESCRIPTION
3075 | Updating Primary site use flag appropriately.
3076 |
3077 | SCOPE - PRIVATE
3078 |
3079 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3080 |
3081 | ARGUMENTS : IN: p_orig_system_customer_ref
3082 p_site_use_code
3083 | OUT:
3084 | IN/ OUT:
3085 | NOTES
3086 |
3087 | MODIFICATION HISTORY
3088 |
3089 | Kalyan 10/30/2005 Bug No : 4588090. Created.
3090 |
3091 +===========================================================================*/
3092 PROCEDURE set_primary_flag( p_orig_system_customer_ref IN VARCHAR2,
3093 p_site_use_code IN VARCHAR2,
3094 p_org_id IN NUMBER)
3095 IS
3096 l_acct_site_id hz_cust_acct_sites_all.cust_acct_site_id%TYPE;
3097 l_site_use_id hz_cust_site_uses_all.site_use_id%TYPE;
3098
3099 CURSOR pflag is
3100 SELECT site.cust_acct_site_id,suse.site_use_id
3101 FROM hz_cust_accounts cust,
3102 hz_cust_acct_sites_all site,
3103 hz_cust_site_uses_all suse
3104 WHERE cust.orig_system_reference = p_orig_system_customer_ref
3105 and cust.cust_account_id = site.cust_account_id
3106 and site.cust_acct_site_id = suse.cust_acct_site_id
3107 and site.org_id = p_org_id -- bug 4454799
3108 and site.org_id = suse.org_id -- bug 4454799
3109 and suse.site_use_code = p_site_use_code
3110 and suse.status = 'A'
3111 and suse.primary_flag = 'Y'
3112 and rownum = 1;
3113
3114 BEGIN
3115 open pflag;
3116 fetch pflag into l_acct_site_id,l_site_use_id;
3117 IF pflag%FOUND THEN
3118 update hz_cust_site_uses_all
3119 set primary_flag = 'N',
3120 last_update_login = hz_utility_v2pub.last_update_login,
3121 last_update_date = SYSDATE,
3122 last_updated_by = hz_utility_v2pub.last_updated_by,
3123 request_id = hz_utility_v2pub.request_id,
3124 program_application_id = hz_utility_v2pub.program_application_id,
3125 program_id = hz_utility_v2pub.program_id
3126 where site_use_id = l_site_use_id;
3127
3128 update hz_cust_acct_sites_all
3129 set BILL_TO_FLAG = decode(p_site_use_code,'BILL_TO','Y',BILL_TO_FLAG),
3130 SHIP_TO_FLAG = decode(p_site_use_code,'SHIP_TO','Y',SHIP_TO_FLAG),
3131 MARKET_FLAG = decode(p_site_use_code,'MARKET','Y',MARKET_FLAG),
3132 last_update_login = hz_utility_v2pub.last_update_login,
3133 last_update_date = SYSDATE,
3134 last_updated_by = hz_utility_v2pub.last_updated_by,
3135 request_id = hz_utility_v2pub.request_id,
3136 program_application_id = hz_utility_v2pub.program_application_id,
3137 program_id = hz_utility_v2pub.program_id
3138 where cust_acct_site_id = l_acct_site_id;
3139
3140 END IF;
3141 close pflag;
3142
3143 EXCEPTION
3144 WHEN OTHERS THEN
3145 RAISE;
3146 END set_primary_flag;
3147
3148 PROCEDURE sync_tax_profile
3149 (
3150 p_request_id IN NUMBER
3151 )
3152 IS
3153
3154 BEGIN
3155
3156 -- Import Party
3157 MERGE INTO ZX_PARTY_TAX_PROFILE PTP
3158 USING
3159 (SELECT 'THIRD_PARTY' PARTY_TYPE_CODE,
3160 party.party_id PARTY_ID,
3161 party.country COUNTRY_CODE, --4742586
3162 FND_GLOBAL.Login_ID PROGRAM_LOGIN_ID ,
3163 party.tax_reference TAX_REFERENCE,
3164 SYSDATE CREATION_DATE,
3165 FND_GLOBAL.User_ID CREATED_BY,
3166 SYSDATE LAST_UPDATE_DATE,
3167 FND_GLOBAL.User_ID LAST_UPDATED_BY,
3168 FND_GLOBAL.Login_ID LAST_UPDATE_LOGIN
3169 FROM HZ_PARTIES party, ra_customers_interface_all rci -- Bug 4956131
3170 WHERE party.orig_system_reference = nvl(rci.orig_system_party_ref, rci.orig_system_customer_ref)
3171 AND party.request_id = p_request_id
3172 AND rci.interface_status is null
3173 AND rci.request_id = p_request_id
3174 AND rci.insert_update_flag = 'I'
3175 AND (rci.rowid = ( SELECT min(i2.rowid)
3176 FROM ra_customers_interface_all i2 -- Bug 4956131
3177 WHERE i2.orig_system_customer_ref = rci.orig_system_customer_ref
3178 AND rci.orig_system_party_ref is null
3179 AND i2.interface_status is null
3180 AND i2.request_id = p_request_id
3181 AND i2.insert_update_flag = 'I') OR
3182 rci.rowid = ( SELECT min(i2.rowid)
3183 FROM ra_customers_interface_all i2 -- Bug 4956131
3184 WHERE i2.orig_system_party_ref = rci.orig_system_party_ref
3185 AND i2.interface_status is null
3186 AND i2.request_id = p_request_id
3187 AND i2.insert_update_flag = 'I'))
3188 AND (party.party_type ='ORGANIZATION' OR party.party_type ='PERSON')) PTY
3189 ON (PTY.PARTY_ID = PTP.PARTY_ID AND PTP.PARTY_TYPE_CODE = 'THIRD_PARTY')
3190 WHEN MATCHED THEN
3191 UPDATE SET
3192 PTP.REP_REGISTRATION_NUMBER = PTY.TAX_REFERENCE,
3193 PTP.LAST_UPDATE_DATE=PTY.LAST_UPDATE_DATE,
3194 PTP.LAST_UPDATED_BY=PTY.LAST_UPDATED_BY,
3195 PTP.LAST_UPDATE_LOGIN=PTY.LAST_UPDATE_LOGIN,
3196 PTP.PROGRAM_ID = hz_utility_v2pub.program_id,
3197 PTP.PROGRAM_APPLICATION_ID = hz_utility_v2pub.program_application_id,
3198 PTP.REQUEST_ID = p_request_id,
3199 PTP.OBJECT_VERSION_NUMBER = PTP.OBJECT_VERSION_NUMBER +1
3200 WHEN NOT MATCHED THEN
3201 INSERT (PARTY_TYPE_CODE,
3202 PARTY_TAX_PROFILE_ID,
3203 PARTY_ID,
3204 PROGRAM_LOGIN_ID,
3205 REP_REGISTRATION_NUMBER,
3206 CREATION_DATE,
3207 CREATED_BY,
3208 LAST_UPDATE_DATE,
3209 LAST_UPDATED_BY,
3210 LAST_UPDATE_LOGIN,
3211 PROGRAM_ID,
3212 PROGRAM_APPLICATION_ID,
3213 REQUEST_ID,
3214 OBJECT_VERSION_NUMBER,
3215 COUNTRY_CODE)--4742586
3216 VALUES (PTY.PARTY_TYPE_CODE,
3217 ZX_PARTY_TAX_PROFILE_S.NEXTVAL,
3218 PTY.PARTY_ID,
3219 PTY.PROGRAM_LOGIN_ID,
3220 PTY.TAX_REFERENCE,
3221 PTY.CREATION_DATE,
3222 PTY.CREATED_BY,
3223 PTY.LAST_UPDATE_DATE,
3224 PTY.LAST_UPDATED_BY,
3225 PTY.LAST_UPDATE_LOGIN,
3226 hz_utility_v2pub.program_id,
3227 hz_utility_v2pub.program_application_id,
3228 p_request_id,
3229 1,
3230 PTY.COUNTRY_CODE );--4742586
3231
3232 -- Bug 6682585
3233 -- Insert Contact Person Parties
3234 MERGE INTO ZX_PARTY_TAX_PROFILE PTP
3235 USING
3236 (SELECT 'THIRD_PARTY' PARTY_TYPE_CODE,
3237 party.party_id PARTY_ID,
3238 party.country COUNTRY_CODE,
3239 FND_GLOBAL.Login_ID PROGRAM_LOGIN_ID ,
3240 party.tax_reference TAX_REFERENCE,
3241 SYSDATE CREATION_DATE,
3242 FND_GLOBAL.User_ID CREATED_BY,
3243 SYSDATE LAST_UPDATE_DATE,
3244 FND_GLOBAL.User_ID LAST_UPDATED_BY,
3245 FND_GLOBAL.Login_ID LAST_UPDATE_LOGIN
3246 FROM HZ_PARTIES party, RA_CONTACT_PHONES_INT_ALL rcpi
3247 WHERE party.orig_system_reference = rcpi.orig_system_contact_ref
3248 AND party.request_id = p_request_id
3249 AND rcpi.interface_status is null
3250 AND rcpi.request_id = p_request_id
3251 AND rcpi.insert_update_flag = 'I'
3252 AND rcpi.rowid = ( SELECT min(i2.rowid)
3253 FROM RA_CONTACT_PHONES_INT_ALL i2
3254 WHERE i2.orig_system_contact_ref = rcpi.orig_system_contact_ref
3255 AND i2.interface_status is null
3256 AND i2.request_id = p_request_id
3257 AND i2.insert_update_flag = 'I')
3258 AND party.party_type ='PERSON') PTY
3259 ON (PTY.PARTY_ID = PTP.PARTY_ID AND PTP.PARTY_TYPE_CODE = 'THIRD_PARTY')
3260 WHEN MATCHED THEN
3261 UPDATE SET
3262 PTP.REP_REGISTRATION_NUMBER = PTY.TAX_REFERENCE,
3263 PTP.LAST_UPDATE_DATE=PTY.LAST_UPDATE_DATE,
3264 PTP.LAST_UPDATED_BY=PTY.LAST_UPDATED_BY,
3265 PTP.LAST_UPDATE_LOGIN=PTY.LAST_UPDATE_LOGIN,
3266 PTP.PROGRAM_ID = hz_utility_v2pub.program_id,
3267 PTP.PROGRAM_APPLICATION_ID = hz_utility_v2pub.program_application_id,
3268 PTP.REQUEST_ID = p_request_id,
3269 PTP.OBJECT_VERSION_NUMBER = PTP.OBJECT_VERSION_NUMBER +1
3270 WHEN NOT MATCHED THEN
3271 INSERT (PARTY_TYPE_CODE,
3272 PARTY_TAX_PROFILE_ID,
3273 PARTY_ID,
3274 PROGRAM_LOGIN_ID,
3275 REP_REGISTRATION_NUMBER,
3276 CREATION_DATE,
3277 CREATED_BY,
3278 LAST_UPDATE_DATE,
3279 LAST_UPDATED_BY,
3280 LAST_UPDATE_LOGIN,
3281 PROGRAM_ID,
3282 PROGRAM_APPLICATION_ID,
3283 REQUEST_ID,
3284 OBJECT_VERSION_NUMBER,
3285 COUNTRY_CODE)
3286 VALUES (PTY.PARTY_TYPE_CODE,
3287 ZX_PARTY_TAX_PROFILE_S.NEXTVAL,
3288 PTY.PARTY_ID,
3289 PTY.PROGRAM_LOGIN_ID,
3290 PTY.TAX_REFERENCE,
3291 PTY.CREATION_DATE,
3292 PTY.CREATED_BY,
3293 PTY.LAST_UPDATE_DATE,
3294 PTY.LAST_UPDATED_BY,
3295 PTY.LAST_UPDATE_LOGIN,
3296 hz_utility_v2pub.program_id,
3297 hz_utility_v2pub.program_application_id,
3298 p_request_id,
3299 1,
3300 PTY.COUNTRY_CODE );
3301
3302
3303
3304 -- Import Party Sites
3305 MERGE INTO ZX_PARTY_TAX_PROFILE PTP
3306 USING
3307 (SELECT 'THIRD_PARTY_SITE' PARTY_TYPE_CODE,
3308 ps.party_site_id PARTY_ID,
3309 loc.country COUNTRY_CODE,--4742586
3310 FND_GLOBAL.Login_ID PROGRAM_LOGIN_ID ,
3311 NULL TAX_REFERENCE,
3312 SYSDATE CREATION_DATE,
3313 FND_GLOBAL.User_ID CREATED_BY,
3314 SYSDATE LAST_UPDATE_DATE,
3315 FND_GLOBAL.User_ID LAST_UPDATED_BY,
3316 FND_GLOBAL.Login_ID LAST_UPDATE_LOGIN
3317 FROM HZ_PARTY_SITES ps, ra_customers_interface_all rci, -- Bug 4956131
3318 HZ_LOCATIONS loc --4742586
3319 WHERE ps.orig_system_reference = rci.orig_system_address_ref
3320 AND loc.location_id = ps.location_id --4742586
3321 AND ps.request_id = p_request_id
3322 AND rci.interface_status is null
3323 AND rci.request_id = p_request_id
3324 AND rci.insert_update_flag = 'I'
3325 AND (rci.rowid = ( SELECT min(i2.rowid)
3326 FROM ra_customers_interface_all i2 -- Bug 4956131
3327 WHERE i2.orig_system_address_ref = rci.orig_system_address_ref
3328 AND i2.interface_status is null
3329 AND i2.request_id = p_request_id
3330 AND i2.insert_update_flag = 'I'))) PTY
3331 ON (PTY.PARTY_ID = PTP.PARTY_ID AND PTP.PARTY_TYPE_CODE = 'THIRD_PARTY_SITE')
3332 WHEN MATCHED THEN
3333 UPDATE SET
3334 PTP.LAST_UPDATE_DATE=PTY.LAST_UPDATE_DATE,
3335 PTP.LAST_UPDATED_BY=PTY.LAST_UPDATED_BY,
3336 PTP.LAST_UPDATE_LOGIN=PTY.LAST_UPDATE_LOGIN,
3337 PTP.PROGRAM_ID = hz_utility_v2pub.program_id,
3338 PTP.PROGRAM_APPLICATION_ID = hz_utility_v2pub.program_application_id,
3339 PTP.REQUEST_ID = p_request_id,
3340 PTP.OBJECT_VERSION_NUMBER = PTP.OBJECT_VERSION_NUMBER +1
3341 WHEN NOT MATCHED THEN
3342 INSERT (
3343 PARTY_TYPE_CODE,
3344 PARTY_TAX_PROFILE_ID,
3345 PARTY_ID,
3346 PROGRAM_LOGIN_ID,
3347 REP_REGISTRATION_NUMBER,
3348 CREATION_DATE,
3349 CREATED_BY,
3350 LAST_UPDATE_DATE,
3351 LAST_UPDATED_BY,
3352 LAST_UPDATE_LOGIN,
3353 PROGRAM_ID,
3354 PROGRAM_APPLICATION_ID,
3355 REQUEST_ID,
3356 OBJECT_VERSION_NUMBER,
3357 COUNTRY_CODE)--4742586
3358 VALUES (
3359 PTY.PARTY_TYPE_CODE,
3360 ZX_PARTY_TAX_PROFILE_S.NEXTVAL,
3361 PTY.PARTY_ID,
3362 PTY.PROGRAM_LOGIN_ID,
3363 PTY.TAX_REFERENCE,
3364 PTY.CREATION_DATE,
3365 PTY.CREATED_BY,
3366 PTY.LAST_UPDATE_DATE,
3367 PTY.LAST_UPDATED_BY,
3368 PTY.LAST_UPDATE_LOGIN,
3369 hz_utility_v2pub.program_id,
3370 hz_utility_v2pub.program_application_id,
3371 p_request_id,
3372 1,
3373 PTY.COUNTRY_CODE);--4742586
3374 --COMMIT;
3375
3376 END sync_tax_profile;
3377
3378 PROCEDURE insert_ci_party_usages
3379 (
3380 p_request_id IN NUMBER
3381 )
3382 IS
3383
3384 BEGIN
3385 INSERT INTO hz_party_usg_assignments(
3386 PARTY_USG_ASSIGNMENT_ID
3387 ,PARTY_ID
3388 ,PARTY_USAGE_CODE
3389 ,EFFECTIVE_START_DATE
3390 ,EFFECTIVE_END_DATE
3391 ,STATUS_FLAG
3392 ,COMMENTS
3393 ,OWNER_TABLE_NAME
3394 ,OWNER_TABLE_ID
3395 ,OBJECT_VERSION_NUMBER
3396 ,CREATED_BY_MODULE
3397 ,APPLICATION_ID
3398 ,CREATED_BY
3399 ,CREATION_DATE
3400 ,LAST_UPDATE_LOGIN
3401 ,LAST_UPDATE_DATE
3402 ,LAST_UPDATED_BY
3403 ,REQUEST_ID
3404 ,PROGRAM_APPLICATION_ID
3405 ,PROGRAM_ID )
3406 SELECT hz_party_usg_assignments_s.nextval -- PARTY_USG_ASSIGNMENT_ID
3407 ,hzp.party_id -- PARTY_ID
3408 ,'CUSTOMER' -- PARTY_USAGE_CODE
3409 ,trunc(SYSDATE) -- EFFECTIVE_START_DATE
3410 ,decode((select min(status)
3411 from hz_cust_accounts
3412 where party_id = hzp.party_id),
3413 'A',to_date('31-12-4712','DD-MM-YYYY')
3414 ,trunc(SYSDATE)) -- EFFECTIVE_END_DATE
3415 ,(select min(status)
3416 from hz_cust_accounts
3417 where party_id = hzp.party_id) -- STATUS_FLAG
3418 ,'' -- COMMENTS
3419 ,'' -- OWNER_TABLE_NAME
3420 ,'' -- OWNER_TABLE_ID
3421 ,1 -- OBJECT_VERSION_NUMBER
3422 ,'CUST_INTERFACE' -- CREATED_BY_MODULE
3423 ,'' -- APPLICATION_ID
3424 ,hz_utility_v2pub.created_by -- CREATED_BY
3425 , SYSDATE -- CREATION_DATE
3426 ,hz_utility_v2pub.last_update_login -- LAST_UPDATE_LOGIN
3427 , SYSDATE -- LAST_UPDATE_DATE
3428 ,hz_utility_v2pub.last_updated_by -- LAST_UPDATED_BY
3429 ,p_request_id -- REQUEST_ID
3430 ,hz_utility_v2pub.program_application_id -- PROGRAM_APPLICATION_ID
3431 ,hz_utility_v2pub.program_id -- PROGRAM_ID
3432 from ra_customers_interface rci,
3433 hz_parties hzp
3434 WHERE hzp.orig_system_reference = nvl(rci.orig_system_party_ref, rci.orig_system_customer_ref)
3435 and hzp.request_id = p_request_id
3436 AND rci.interface_status is null
3437 AND rci.insert_update_flag = 'I'
3438 AND ( rci.rowid = (SELECT min(i2.rowid)
3439 FROM ra_customers_interface i2
3440 WHERE i2.orig_system_customer_ref =
3441 rci.orig_system_customer_ref
3442 and rci.orig_system_party_ref is null
3443 AND i2.interface_status is null
3444 AND i2.insert_update_flag = 'I') OR
3445 rci.rowid = (SELECT min(i2.rowid)
3446 FROM ra_customers_interface i2
3447 WHERE i2.orig_system_party_ref = rci.orig_system_party_ref
3448 AND i2.interface_status is null
3449 AND i2.insert_update_flag = 'I')
3450 );
3451
3452 END insert_ci_party_usages;
3453
3454 PROCEDURE insert_nci_party_usages
3455 (
3456 p_request_id IN NUMBER
3457 )
3458 IS
3459
3460 BEGIN
3461 INSERT INTO hz_party_usg_assignments(
3462 PARTY_USG_ASSIGNMENT_ID
3463 ,PARTY_ID
3464 ,PARTY_USAGE_CODE
3465 ,EFFECTIVE_START_DATE
3466 ,EFFECTIVE_END_DATE
3467 ,STATUS_FLAG
3468 ,COMMENTS
3469 ,OWNER_TABLE_NAME
3470 ,OWNER_TABLE_ID
3471 ,OBJECT_VERSION_NUMBER
3472 ,CREATED_BY_MODULE
3473 ,APPLICATION_ID
3474 ,CREATED_BY
3475 ,CREATION_DATE
3476 ,LAST_UPDATE_LOGIN
3477 ,LAST_UPDATE_DATE
3478 ,LAST_UPDATED_BY
3479 ,REQUEST_ID
3480 ,PROGRAM_APPLICATION_ID
3481 ,PROGRAM_ID )
3482 SELECT hz_party_usg_assignments_s.nextval -- PARTY_USG_ASSIGNMENT_ID
3483 ,hzp.party_id -- PARTY_ID
3484 ,'CUSTOMER' -- PARTY_USAGE_CODE
3485 ,trunc(SYSDATE) -- EFFECTIVE_START_DATE
3486 ,decode((select min(status)
3487 from hz_cust_accounts
3488 where party_id = hzp.party_id),
3489 'A',to_date('31-12-4712','DD-MM-YYYY')
3490 ,trunc(SYSDATE)) -- EFFECTIVE_END_DATE
3491 ,(select min(status)
3492 from hz_cust_accounts
3493 where party_id = hzp.party_id)-- STATUS_FLAG
3494 ,'' -- COMMENTS
3495 ,'' -- OWNER_TABLE_NAME
3496 ,'' -- OWNER_TABLE_ID
3497 ,1 -- OBJECT_VERSION_NUMBER
3498 ,'CUST_INTERFACE' -- CREATED_BY_MODULE
3499 ,'' -- APPLICATION_ID
3500 ,hz_utility_v2pub.created_by -- CREATED_BY
3501 , SYSDATE -- CREATION_DATE
3502 ,hz_utility_v2pub.last_update_login -- LAST_UPDATE_LOGIN
3503 , SYSDATE -- LAST_UPDATE_DATE
3504 ,hz_utility_v2pub.last_updated_by -- LAST_UPDATED_BY
3505 ,p_request_id -- REQUEST_ID
3506 ,hz_utility_v2pub.program_application_id-- PROGRAM_APPLICATION_ID
3507 ,hz_utility_v2pub.program_id -- PROGRAM_ID
3508 from ra_customers_interface_all rci, -- Bug 4956131
3509 hz_parties hzp
3510 WHERE hzp.party_id = HZ_CUSTOMER_INT.get_account_party_id(rci.orig_system_party_ref,rci.person_flag,'P')
3511 AND rci.request_id = p_request_id
3512 AND rci.interface_status is null
3513 AND rci.insert_update_flag = 'I'
3514 AND ( rci.rowid = (SELECT min(i2.rowid)
3515 FROM ra_customers_interface_all i2 -- Bug 4956131
3516 WHERE i2.orig_system_customer_ref =
3517 rci.orig_system_customer_ref
3518 and rci.orig_system_party_ref is null
3519 AND i2.interface_status is null
3520 AND i2.request_id = p_request_id
3521 AND i2.insert_update_flag = 'I') OR
3522 rci.rowid = (SELECT min(i2.rowid)
3523 FROM ra_customers_interface_all i2 -- Bug 4956131
3524 WHERE i2.orig_system_party_ref = rci.orig_system_party_ref
3525 AND i2.request_id = p_request_id
3526 AND i2.interface_status is null
3527 AND i2.insert_update_flag = 'I')
3528 )
3529 and not exists( SELECT '1'
3530 FROM hz_parties
3531 WHERE party_id = hzp.party_id
3532 AND request_id = p_request_id )
3533 and not exists(
3534 select '1'
3535 from hz_party_usg_assignments pua
3536 where pua.party_id = hzp.party_id
3537 and party_usage_code = 'CUSTOMER'
3538 and pua.status_flag = ( select min(status)
3539 from hz_cust_accounts
3540 where party_id = hzp.party_id)
3541 and pua.effective_start_date <= decode((select min(status)
3542 from hz_cust_accounts
3543 where party_id = hzp.party_id),
3544 'A',trunc(SYSDATE)
3545 ,pua.effective_start_date)
3546 and nvl(pua.effective_end_date,to_date('31-12-4712','DD-MM-YYYY')) >= decode((select min(status)
3547 from hz_cust_accounts
3548 where party_id = hzp.party_id),
3549 'A',trunc(SYSDATE)
3550 ,nvl(pua.effective_start_date,
3551 to_date('31-12-4712','DD-MM-YYYY')
3552 )));
3553 END insert_nci_party_usages;
3554
3555 /* Bug 11819914 Sync tax_reference in HZ_PARTIES, HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES */
3556
3557 PROCEDURE sync_tax_reference ( p_request_id IN NUMBER
3558 ,x_return_status OUT NOCOPY VARCHAR2
3559 ,x_msg_data OUT NOCOPY VARCHAR2)
3560 IS
3561
3562 CURSOR c_tax_reference IS SELECT party_id, rep_registration_number
3563 FROM zx_party_tax_profile
3564 WHERE request_id = p_request_id
3565 AND party_type_code ='THIRD_PARTY';
3566
3567 TYPE PARTY_ID IS TABLE OF HZ_PARTIES.PARTY_ID%TYPE;
3568 TYPE TAX_REFERENCE IS TABLE OF HZ_PARTIES.TAX_REFERENCE%TYPE;
3569
3570 l_party_id PARTY_ID;
3571 l_tax_reference TAX_REFERENCE;
3572
3573 BEGIN
3574 FND_FILE.put_line(fnd_file.log,'sync_tax_reference + req_id : '||p_request_id);
3575
3576 x_return_status := FND_API.G_RET_STS_SUCCESS;
3577
3578 OPEN c_tax_reference;
3579 FETCH c_tax_reference BULK COLLECT INTO l_party_id, l_tax_reference ;
3580
3581 FORALL i IN 1..l_party_id.count
3582
3583 UPDATE HZ_PARTIES p
3584 SET tax_reference = l_tax_reference(i)
3585 WHERE p.party_id = l_party_id(i);
3586
3587 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No. of records updated in HZ_PARTIES : ' || l_party_id.count);
3588
3589 FORALL i IN 1..l_party_id.count
3590
3591 UPDATE HZ_PERSON_PROFILES per
3592 SET tax_reference = l_tax_reference(i)
3593 WHERE per.party_id = l_party_id(i)
3594 AND actual_content_source = 'SST'
3595 AND effective_end_date IS NULL;
3596
3597 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No. of records updated in HZ_PERSON_PROFILES : ' || l_party_id.count);
3598
3599 FORALL i IN 1..l_party_id.count
3600
3601 UPDATE HZ_ORGANIZATION_PROFILES org
3602 SET tax_reference = l_tax_reference(i)
3603 WHERE party_id = l_party_id(i)
3604 AND actual_content_source = 'SST'
3605 AND effective_end_date IS NULL;
3606
3607 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No. of records updated in HZ_ORGANIZATION_PROFILES : ' || l_party_id.count);
3608
3609 CLOSE c_tax_reference;
3610
3611 FND_FILE.put_line(fnd_file.log,'sync_tax_reference - ');
3612
3613 EXCEPTION
3614 WHEN OTHERS THEN
3615 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3616 x_msg_data := SQLERRM;
3617 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in HZ_CUSTOMER_INT.sync_tax_reference : ' || SQLERRM);
3618
3619
3620 END sync_tax_reference;
3621
3622 PROCEDURE Import_tax_attributes(p_request_id IN NUMBER,
3623 p_MODE IN VARCHAR2,
3624 x_return_status OUT NOCOPY VARCHAR2,
3625 x_msg_data OUT NOCOPY VARCHAR2,
3626 validation_flag IN NUMBER
3627 ) IS
3628
3629 l_sql_select_insert_ptp_intf varchar2(2000);
3630 l_sql_from_insert_ptp_intf varchar2(2000);
3631 l_sql_select_valid_party_id varchar2(2000);
3632 l_sql_from_valid_party_id varchar2(3000);
3633 l_sql_select_insert_ptp_prod varchar2(2000);
3634 l_sql_from_insert_ptp_prod varchar2(3000);
3635
3636 TYPE orig_sys_ref IS TABLE OF VARCHAR2(240);
3637
3638 l_party_orig_sys_ref orig_sys_ref;
3639 l_address_orig_sys_ref orig_sys_ref;
3640
3641
3642
3643
3644 /*Query to identify errored out Customers*/
3645
3646 CURSOR error_parties IS SELECT intf_party_reference
3647 FROM zx_party_tax_profile_int zx_ptp
3648 WHERE request_id = p_request_id
3649 AND zx_ptp.intf_party_site_reference IS NULL
3650 AND NVL(zx_ptp.record_status,3) = 3;
3651
3652 /*Query to identify errored out Addresses*/
3653
3654 CURSOR error_sites IS SELECT intf_party_site_reference
3655 FROM zx_party_tax_profile_int zx_ptp
3656 WHERE request_id = p_request_id
3657 AND zx_ptp.intf_party_site_reference IS NOT NULL
3658 AND NVL(zx_ptp.record_status,3) = 3;
3659 l_request_id NUMBER;
3660 l_message_header_string_1 VARCHAR2(2000);
3661 l_message_header_string_2 VARCHAR2(2000);
3662
3663 l_cust_tax_reference VARCHAR2(50);
3664
3665 BEGIN
3666
3667 x_return_status := FND_API.G_RET_STS_SUCCESS;
3668
3669 --Dbms_Output.put_line('Import_tax_attributes +');
3670
3671 FND_FILE.put_line(fnd_file.log,'Import_tax_attributes +'||p_MODE||' req_id - '||p_request_id);
3672
3673 IF p_mode = 'VALIDATE' THEN
3674
3675 BEGIN
3676 SELECT cust_tax_reference INTO l_cust_tax_reference
3677 FROM ra_customers_interface
3678 WHERE request_id = p_request_id
3679 AND cust_tax_reference IS NOT NULL
3680 AND ROWNUM = 1;
3681
3682 EXCEPTION
3683 WHEN OTHERS THEN
3684 l_cust_tax_reference := NULL;
3685 END;
3686
3687 IF l_cust_tax_reference IS NOT NULL THEN
3688
3689 l_sql_select_insert_ptp_intf := 'SELECT cust_tax_reference,country,''CREATE'''||
3690 ',Nvl(orig_system_party_ref,orig_system_customer_ref) AS intf_party_reference'||
3691 ',NULL AS intf_party_site_reference'||
3692 ',''THIRD_PARTY''';
3693 l_sql_from_insert_ptp_intf := ' FROM ra_customers_interface_all hz_rcia'||
3694 ' WHERE hz_rcia.insert_update_flag = ''I'''||
3695 ' AND hz_rcia.request_id = '||p_request_id||
3696 ' AND hz_rcia.cust_tax_reference IS NOT NULL';
3697
3698 ELSE
3699
3700 l_sql_select_insert_ptp_intf := NULL;
3701 l_sql_from_insert_ptp_intf := NULL;
3702
3703 FND_FILE.put_line(fnd_file.log,'l_sql_select_insert_ptp_intf : '||l_sql_select_insert_ptp_intf);
3704 FND_FILE.put_line(fnd_file.log,'l_sql_from_insert_ptp_intf : '||l_sql_from_insert_ptp_intf);
3705
3706 END IF;
3707
3708 FND_FILE.put_line(fnd_file.log,SYSDATE||' Call ZX_PTP_IMPORT.IMPORT_WRAPPER for VALIDATE with request_id :'||p_request_id);
3709
3710 ZX_PTP_IMPORT.IMPORT_WRAPPER(p_request_id => p_request_id
3711 ,p_interface_table_name => 'RA_CUSTOMERS_INTERFACE_ALL'
3712 ,p_gather_stats => FND_API.G_TRUE
3713 , p_validate_only => FND_API.G_TRUE
3714 , p_sql_select_insert_ptp_intf => l_sql_select_insert_ptp_intf
3715 , p_sql_from_insert_ptp_intf => l_sql_from_insert_ptp_intf
3716 , p_insert_only => FND_API.G_FALSE
3717 , p_sql_select_valid_party_id => NULL
3718 , p_sql_from_valid_party_id => NULL
3719 , p_sql_select_insert_ptp_prod => NULL
3720 , p_sql_from_insert_ptp_prod => NULL
3721 , p_commit => FND_API.G_FALSE
3722 , p_batch_id => null
3723 , x_return_status => x_return_status
3724 , x_msg_data => x_msg_data);
3725
3726
3727
3728 FND_FILE.put_line(fnd_file.log,SYSDATE||' End Call ZX_PTP_IMPORT.IMPORT_WRAPPER for VALIDATE with status :'||x_return_status);
3729
3730 IF x_return_status <>FND_API.G_RET_STS_SUCCESS AND x_return_status <> 'W' THEN
3731 FND_FILE.put_line(fnd_file.log,SYSDATE||' Error in Call ZX_PTP_IMPORT.IMPORT_WRAPPER for VALIDATE :'||x_msg_data);
3732 END IF;
3733
3734
3735 IF x_return_status = 'W' THEN
3736
3737 OPEN error_parties;
3738 FETCH error_parties BULK COLLECT INTO l_party_orig_sys_ref;
3739
3740 /* Update error status for all the errored out customers in ra_customers_interface_all*/
3741
3742 FORALL i IN 1..l_party_orig_sys_ref.Count
3743
3744 UPDATE ra_customers_interface_all rci
3745 SET interface_status = interface_status|| 'z4,'
3746 WHERE request_id = p_request_id
3747 AND Nvl(orig_system_party_ref,orig_system_customer_ref) = l_party_orig_sys_ref(i);
3748
3749 /* Update error status for all the errored out addresses in ra_customers_interface_all*/
3750
3751 CLOSE error_parties;
3752
3753 OPEN error_sites;
3754 FETCH error_sites BULK COLLECT INTO l_address_orig_sys_ref;
3755
3756 FORALL i IN 1..l_address_orig_sys_ref.Count
3757
3758 UPDATE ra_customers_interface_all rci
3759 SET interface_status = interface_status|| 'z5,'
3760 WHERE request_id = p_request_id
3761 AND orig_system_address_ref = l_address_orig_sys_ref(i);
3762 CLOSE error_sites;
3763
3764 END IF;
3765
3766 ELSIF p_mode = 'INSERT' THEN
3767
3768 l_sql_select_valid_party_id := 'SELECT party_id, party_type_code'||
3769 ',intf_party_reference'||
3770 ',intf_party_site_reference';
3771
3772 l_sql_from_valid_party_id := ' FROM ((SELECT hz_insert.party_id, ''THIRD_PARTY'' as party_type_code'||
3773 ',hz_insert.orig_system_reference as intf_party_reference'||
3774 ',NULL as intf_party_site_reference'||
3775 ' FROM HZ_PARTIES hz_insert, ra_customers_interface hz_rcia'||
3776 ' WHERE hz_insert.orig_system_reference = nvl(hz_rcia.orig_system_party_ref, hz_rcia.orig_system_customer_ref)'||
3777 ' AND hz_insert.request_id = hz_rcia.request_id'||
3778 ' AND hz_rcia.interface_status is null'||
3779 ' AND hz_rcia.insert_update_flag = ''I'''||
3780 ' AND (hz_rcia.rowid = ( SELECT min(i2.rowid)'||
3781 ' FROM ra_customers_interface i2'||
3782 ' WHERE i2.orig_system_customer_ref = hz_rcia.orig_system_customer_ref'||
3783 ' AND i2.request_id = hz_rcia.request_id'||
3784 ' AND hz_rcia.orig_system_party_ref is null'||
3785 ' AND i2.interface_status is null'||
3786 ' AND i2.insert_update_flag = ''I'') OR'||
3787 ' hz_rcia.rowid = ( SELECT min(i2.rowid)'||
3788 ' FROM ra_customers_interface_all i2'||
3789 ' WHERE i2.orig_system_party_ref = hz_rcia.orig_system_party_ref'||
3790 ' AND i2.request_id = hz_rcia.request_id'||
3791 ' AND i2.interface_status is null'||
3792 ' AND i2.insert_update_flag = ''I''))'||
3793 ' AND (hz_insert.party_type =''ORGANIZATION'' OR hz_insert.party_type =''PERSON''))'||
3794 ' UNION ALL ' ||
3795 '(SELECT ps.party_site_id as party_id, ''THIRD_PARTY_SITE'' as party_type_code'||
3796 ', NVL(rci.orig_system_party_ref, rci.orig_system_customer_ref) as intf_party_reference'||
3797 ',ps.orig_system_reference as intf_party_site_reference'||
3798 ' FROM HZ_PARTY_SITES ps, ra_customers_interface rci'||
3799 ' WHERE ps.orig_system_reference = rci.orig_system_address_ref'||
3800 ' AND ps.request_id = rci.request_id'||
3801 ' AND rci.interface_status is null' ||
3802 ' AND rci.insert_update_flag = ''I'''||
3803 ' AND (rci.rowid = ( SELECT min(i2.rowid)'||
3804 ' FROM ra_customers_interface i2'||
3805 ' WHERE i2.orig_system_address_ref = rci.orig_system_address_ref'||
3806 ' AND i2.request_id = rci.request_id'||
3807 ' AND i2.interface_status is NULL'||
3808 ' AND i2.insert_update_flag = ''I''))) '||
3809 ')';
3810
3811 l_sql_select_insert_ptp_prod := 'SELECT party_id, party_type_code, country country_code';
3812
3813 l_sql_from_insert_ptp_prod := ' FROM ((SELECT hz_insert.party_id, ''THIRD_PARTY'' as party_type_code, hz_insert.country'||
3814 ' FROM HZ_PARTIES hz_insert, ra_customers_interface hz_rcia'||
3815 ' WHERE hz_insert.orig_system_reference = nvl(hz_rcia.orig_system_party_ref, hz_rcia.orig_system_customer_ref)'||
3816 ' AND hz_insert.request_id = hz_rcia.request_id'||
3817 ' AND hz_rcia.interface_status is null'||
3818 ' AND hz_rcia.insert_update_flag = ''I'''||
3819 ' AND (hz_rcia.rowid = ( SELECT min(i2.rowid)'||
3820 ' FROM ra_customers_interface i2'||
3821 ' WHERE i2.orig_system_customer_ref = hz_rcia.orig_system_customer_ref'||
3822 ' AND i2.request_id = hz_rcia.request_id'||
3823 ' AND hz_rcia.orig_system_party_ref is null'||
3824 ' AND i2.interface_status is null'||
3825 ' AND i2.insert_update_flag = ''I'') OR'||
3826 ' hz_rcia.rowid = ( SELECT min(i2.rowid)'||
3827 ' FROM ra_customers_interface i2'||
3828 ' WHERE i2.orig_system_party_ref = hz_rcia.orig_system_party_ref'||
3829 ' AND i2.request_id = hz_rcia.request_id'||
3830 ' AND i2.interface_status is null'||
3831 ' AND i2.insert_update_flag = ''I''))'||
3832 ' AND (hz_insert.party_type =''ORGANIZATION'' OR hz_insert.party_type =''PERSON''))'||
3833 ' UNION ALL ' ||
3834 '(SELECT ps.party_site_id as party_id, ''THIRD_PARTY_SITE'' as party_type_code, loc.country'||
3835 ' FROM HZ_PARTY_SITES ps, ra_customers_interface rci, HZ_LOCATIONS loc '||
3836 ' WHERE ps.orig_system_reference = rci.orig_system_address_ref'||
3837 ' AND loc.location_id = ps.location_id '||
3838 ' AND ps.request_id = rci.request_id'||
3839 ' AND rci.interface_status is null' ||
3840 ' AND rci.insert_update_flag = ''I'''||
3841 ' AND (rci.rowid = ( SELECT min(i2.rowid)'||
3842 ' FROM ra_customers_interface i2'||
3843 ' WHERE i2.orig_system_address_ref = rci.orig_system_address_ref'||
3844 ' AND i2.interface_status is NULL'||
3845 ' AND i2.request_id = rci.request_id'||
3846 ' AND i2.insert_update_flag = ''I'')))'||
3847 ' UNION ALL ' ||
3848
3849 '(SELECT party.party_id, ''THIRD_PARTY'' party_type_code, party.country' ||
3850 ' FROM HZ_PARTIES party, RA_CONTACT_PHONES_INTERFACE rcpi' ||
3851 ' WHERE party.orig_system_reference = rcpi.orig_system_contact_ref' ||
3852 ' AND party.request_id = rcpi.request_id'||
3853 ' AND rcpi.interface_status is null'||
3854 ' AND rcpi.insert_update_flag = ''I'''||
3855 ' AND rcpi.rowid = ( SELECT min(i2.rowid)'||
3856 ' FROM RA_CONTACT_PHONES_INTERFACE i2 '||
3857 ' WHERE i2.orig_system_contact_ref = rcpi.orig_system_contact_ref'||
3858 ' AND i2.interface_status is null'||
3859 ' AND i2.request_id = rcpi.request_id'||
3860 ' AND i2.insert_update_flag = ''I'')'||
3861 'AND party.party_type =''PERSON'')'||
3862 ') hz_insert WHERE 1 = 1';
3863
3864 IF validation_flag = 0 THEN
3865
3866 l_request_id := p_request_id;
3867
3868 ELSE
3869
3870 BEGIN
3871
3872 SELECT request_id INTO l_request_id
3873 FROM zx_party_tax_profile_int
3874 WHERE intf_party_reference IN (SELECT Nvl(orig_system_party_ref, orig_system_customer_ref)
3875 FROM ra_customers_interface
3876 )
3877 AND ROWNUM = 1 ;
3878
3879 EXCEPTION
3880
3881 WHEN No_Data_Found THEN
3882 l_request_id := p_request_id;
3883 END;
3884 END IF;
3885
3886 FND_FILE.put_line(fnd_file.log,'['||To_Char(SYSDATE,'DD-MON-RRRR HH24:MI:SS')||'] '||' Call ZX_PTP_IMPORT.IMPORT_WRAPPER for INSERT with request_id :'||l_request_id);
3887
3888 ZX_PTP_IMPORT.IMPORT_WRAPPER(p_request_id => l_request_id
3889 ,p_interface_table_name => 'RA_CUSTOMERS_INTERFACE_ALL'
3890 ,p_gather_stats => FND_API.G_FALSE
3891 , p_validate_only => FND_API.G_FALSE
3892 , p_sql_select_insert_ptp_intf => NULL
3893 , p_sql_from_insert_ptp_intf => NULL
3894 , p_insert_only => FND_API.G_TRUE
3895 , p_sql_select_valid_party_id => l_sql_select_valid_party_id
3896 , p_sql_from_valid_party_id => l_sql_from_valid_party_id
3897 , p_sql_select_insert_ptp_prod => l_sql_select_insert_ptp_prod
3898 , p_sql_from_insert_ptp_prod => l_sql_from_insert_ptp_prod
3899 , p_commit => FND_API.G_FALSE
3900 , p_batch_id => NULL
3901 , x_return_status => x_return_status
3902 , x_msg_data => x_msg_data);
3903
3904
3905
3906 FND_FILE.put_line(fnd_file.log,'['||To_Char(SYSDATE,'DD-MON-RRRR HH24:MI:SS')||'] '||' End Call ZX_PTP_IMPORT.IMPORT_WRAPPER for INSERT with status :'||x_return_status);
3907
3908 IF x_return_status <>FND_API.G_RET_STS_SUCCESS THEN
3909 FND_FILE.put_line(fnd_file.log,SYSDATE||' Error in Call ZX_PTP_IMPORT.IMPORT_WRAPPER for INSERT :'||x_msg_data);
3910 END IF;
3911
3912 ELSIF p_mode = 'DISP_STATS' THEN
3913
3914
3915 BEGIN
3916
3917 SELECT request_id INTO l_request_id
3918 FROM zx_party_tax_profile_int
3919 WHERE intf_party_reference IN (SELECT Nvl(orig_system_party_ref, orig_system_customer_ref)
3920 FROM ra_customers_interface
3921 )
3922 AND ROWNUM = 1 ;
3923
3924 EXCEPTION
3925
3926 WHEN No_Data_Found THEN
3927 l_request_id := p_request_id;
3928 END;
3929
3930 FND_FILE.put_line(fnd_file.log,'Call ZX_PTP_IMPORT.MESSAGE_WRAPPER stats');
3931
3932 FND_FILE.put_line(fnd_file.log,'l_request_id -'||l_request_id);
3933 FND_FILE.put_line(fnd_file.output,'');
3934
3935 ZX_PTP_IMPORT.MESSAGE_WRAPPER(p_request_id => l_request_id
3936 ,p_interface_table_name => 'RA_CUSTOMERS_INTERFACE_ALL'
3937 ,p_display_stats => FND_API.G_TRUE
3938 ,p_display_messages => FND_API.G_FALSE
3939 ,p_message_header_string_1 => NULL
3940 ,p_message_header_string_2 => NULL
3941 ,x_return_status => x_return_status
3942 ,x_msg_data => x_msg_data
3943 );
3944 FND_FILE.put_line(fnd_file.log,'end Call ZX_PTP_IMPORT.MESSAGE_WRAPPER stats with status :'||x_return_status);
3945
3946
3947
3948 ELSIF p_mode = 'DISP_ERRORS' THEN
3949
3950
3951 BEGIN
3952
3953 SELECT request_id INTO l_request_id
3954 FROM zx_party_tax_profile_int
3955 WHERE intf_party_reference IN (SELECT Nvl(orig_system_party_ref, orig_system_customer_ref)
3956 FROM ra_customers_interface
3957 )
3958 AND ROWNUM = 1 ;
3959
3960 EXCEPTION
3961
3962 WHEN No_Data_Found THEN
3963 l_request_id := p_request_id;
3964 END;
3965
3966 l_message_header_string_1 := fnd_message.get_string('AR','AR_RACUST_OUT_ZX_HDR1');
3967 l_message_header_string_2 := fnd_message.get_string('AR','AR_RACUST_OUT_ZX_HDR2');
3968
3969 FND_FILE.put_line(fnd_file.log,'Call ZX_PTP_IMPORT.MESSAGE_WRAPPER messages');
3970 FND_FILE.put_line(fnd_file.log,'l_request_id -'||l_request_id);
3971
3972
3973 ZX_PTP_IMPORT.MESSAGE_WRAPPER(p_request_id => l_request_id
3974 ,p_interface_table_name => 'RA_CUSTOMERS_INTERFACE_ALL'
3975 ,p_display_stats => FND_API.G_FALSE
3976 ,p_display_messages => FND_API.G_TRUE
3977 ,p_message_header_string_1 => l_message_header_string_1
3978 ,p_message_header_string_2 => l_message_header_string_2
3979 ,x_return_status => x_return_status
3980 ,x_msg_data => x_msg_data
3981 );
3982
3983 FND_FILE.put_line(fnd_file.log,'End Call ZX_PTP_IMPORT.MESSAGE_WRAPPER messages with status :'||x_return_status);
3984
3985 ELSIF p_mode = 'DELETE_ZX_REC' THEN
3986
3987 IF validation_flag = 0 THEN
3988
3989 l_request_id := p_request_id;
3990
3991 ELSE
3992
3993 BEGIN
3994
3995 SELECT request_id INTO l_request_id
3996 FROM zx_party_tax_profile_int
3997 WHERE intf_party_reference IN (SELECT Nvl(orig_system_party_ref, orig_system_customer_ref)
3998 FROM ra_customers_interface
3999 )
4000 AND ROWNUM = 1 ;
4001
4002 EXCEPTION
4003
4004 WHEN No_Data_Found THEN
4005 l_request_id := p_request_id;
4006 END;
4007 END IF;
4008
4009
4010 FND_FILE.put_line(fnd_file.log,'Call ZX_PTP_IMPORT.delete_success_records');
4011
4012 ZX_PTP_IMPORT.delete_success_records(p_request_id => l_request_id
4013 ,p_commit => FND_API.G_FALSE
4014 ,x_return_status => x_return_status
4015 ,x_msg_data => x_msg_data);
4016
4017 FND_FILE.put_line(fnd_file.log,'End Call ZX_PTP_IMPORT.delete_success_records');
4018
4019
4020 END IF;
4021
4022 FND_FILE.put_line(fnd_file.log,'Import_tax_attributes -');
4023
4024 EXCEPTION
4025
4026 WHEN OTHERS THEN
4027
4028 FND_FILE.put_line(fnd_file.log,'Import_tax_attributes error :'||sqlerrm);
4029 RAISE;
4030
4031 END Import_tax_attributes;
4032
4033 PROCEDURE delete_success_records IS
4034 BEGIN
4035
4036 FND_FILE.put_line(fnd_file.log,'HZ_CUSTOMER_INT.delete_success_records +');
4037
4038 DELETE FROM ra_customers_interface
4039 WHERE interface_status is NULL;
4040
4041 DELETE FROM RA_CONTACT_PHONES_INTERFACE
4042 WHERE interface_status is null;
4043
4044 DELETE FROM ra_cust_pay_method_interface
4045 WHERE interface_status is null;
4046
4047 DELETE FROM ra_customer_banks_interface
4048 WHERE interface_status is null;
4049
4050 DELETE FROM ra_customer_profiles_interface
4051 WHERE interface_status is null;
4052
4053 /* reset request id to null */
4054
4055 UPDATE ra_customers_interface
4056 SET request_id = NULL;
4057
4058 UPDATE ra_customer_profiles_interface
4059 SET request_id = NULL;
4060
4061 UPDATE ra_contact_phones_interface
4062 SET request_id = NULL;
4063
4064 UPDATE ra_cust_pay_method_interface
4065 SET request_id = NULL;
4066
4067 UPDATE ra_customer_banks_interface
4068 SET request_id = NULL;
4069
4070 FND_FILE.put_line(fnd_file.log,'HZ_CUSTOMER_INT.delete_success_records -');
4071
4072 END delete_success_records;
4073
4074 -- enh 14181243
4075
4076 PROCEDURE upd_non_primary_bank_priority(p_request_id IN NUMBER) IS
4077
4078 cursor get_cust_bank_acct_priority is
4079 select distinct ipi.ext_pmt_party_id, max(ipi.order_of_preference), count(ipi.instrument_payment_use_id)
4080 FROM iby_external_payers_all iep,
4081 iby_pmt_instr_uses_all ipi,
4082 iby_ext_bank_accounts eba,
4083 hz_cust_accounts hca,
4084 ra_customer_banks_int_all cbi
4085 WHERE
4086 cbi.request_id = p_request_id
4087 AND eba.currency_code = cbi.bank_account_currency_code
4088 AND cbi.orig_system_customer_ref = hca.orig_system_reference
4089 AND iep.cust_account_id = hca.cust_account_id
4090 AND iep.party_id = hca.party_id
4091 AND decode(cbi.orig_system_address_ref,null,-1,iep.org_id) = decode(cbi.orig_system_address_ref,null,-1,cbi.org_id)
4092 AND decode(cbi.orig_system_address_ref,null,'OPERATING_UNIT',iep.org_type) = 'OPERATING_UNIT'
4093 AND iep.ext_payer_id = ipi.ext_pmt_party_id
4094 AND ipi.payment_function = 'CUSTOMER_PAYMENT'
4095 AND ipi.payment_flow = 'FUNDS_CAPTURE'
4096 AND ipi.instrument_type = 'BANKACCOUNT'
4097 AND ipi.instrument_id = eba.ext_bank_account_id
4098 AND cbi.interface_status is null
4099 group by ipi.ext_pmt_party_id;
4100
4101
4102 cursor get_modifying_priorities(l_ext_pmt_party_id in number) is
4103 select ipi.instrument_payment_use_id
4104 FROM iby_pmt_instr_uses_all ipi
4105 WHERE ipi.ext_pmt_party_id = l_ext_pmt_party_id
4106 AND ipi.order_of_preference = -1
4107 order by instrument_payment_use_id;
4108
4109
4110 TYPE IDlist IS TABLE OF NUMBER(15);
4111 i_max_order_of_preference IDlist;
4112 i_ext_pmt_party_id IDlist;
4113 i_instrument_payment_use_id IDlist;
4114 i_bank_acct_count IDlist;
4115
4116 begin
4117 -- FND_FILE.PUT_LINE( FND_FILE.LOG, 'In upd_non_primary_bank_priority: ');
4118 open get_cust_bank_acct_priority;
4119 fetch get_cust_bank_acct_priority bulk collect into i_ext_pmt_party_id, i_max_order_of_preference, i_bank_acct_count;
4120 close get_cust_bank_acct_priority;
4121
4122 for i in 1..i_ext_pmt_party_id.count loop
4123 -- FND_FILE.PUT_LINE( FND_FILE.LOG,'i_ext_pmt_party_id'||i||':'||i_ext_pmt_party_id(i));
4124 -- FND_FILE.PUT_LINE( FND_FILE.LOG,'i_max_order_of_preference'||i||':'||i_max_order_of_preference(i));
4125 open get_modifying_priorities(i_ext_pmt_party_id(i));
4126 fetch get_modifying_priorities bulk collect into i_instrument_payment_use_id;
4127 close get_modifying_priorities;
4128
4129 for j in 1..i_instrument_payment_use_id.count loop
4130
4131 IF i_max_order_of_preference(i) = -1 THEN
4132 update iby_pmt_instr_uses_all
4133 set order_of_preference = j
4134 where instrument_payment_use_id = i_instrument_payment_use_id(j);
4135
4136 ELSE
4137 update iby_pmt_instr_uses_all
4138 set order_of_preference = i_max_order_of_preference(i) +j
4139 where instrument_payment_use_id = i_instrument_payment_use_id(j);
4140
4141
4142 END IF;
4143 end loop;
4144 end loop;
4145
4146
4147 end upd_non_primary_bank_priority;
4148
4149 PROCEDURE update_bank_priority (p_request_id IN NUMBER) IS
4150
4151 cursor get_cust_bank_acct_priority is
4152 select ipi.instrument_payment_use_id, ipi.order_of_preference
4153 FROM iby_external_payers_all iep,
4154 iby_pmt_instr_uses_all ipi,
4155 iby_ext_bank_accounts eba,
4156 hz_cust_accounts hca,
4157 ra_customer_banks_int_all cbi
4158 WHERE cbi.orig_system_address_ref is null
4159 AND cbi.request_id = p_request_id
4160 AND cbi.primary_flag = 'Y'
4161 AND eba.currency_code = cbi.bank_account_currency_code
4162 AND cbi.orig_system_customer_ref = hca.orig_system_reference
4163 AND iep.cust_account_id = hca.cust_account_id
4164 AND iep.party_id = hca.party_id
4165 AND iep.acct_site_use_id is null
4166 AND iep.ext_payer_id = ipi.ext_pmt_party_id
4167 AND ipi.payment_function = 'CUSTOMER_PAYMENT'
4168 AND ipi.payment_flow = 'FUNDS_CAPTURE'
4169 AND ipi.instrument_type = 'BANKACCOUNT'
4170 AND ipi.instrument_id = eba.ext_bank_account_id
4171 AND cbi.interface_status is null
4172 order by ipi.order_of_preference desc;
4173
4174 cursor get_site_bank_acct_priority is
4175 select ipi.instrument_payment_use_id, ipi.order_of_preference
4176 FROM iby_external_payers_all iep,
4177 iby_pmt_instr_uses_all ipi,
4178 iby_ext_bank_accounts eba,
4179 hz_cust_acct_sites_all hca,
4180 hz_cust_site_uses_all hcs,
4181 hz_cust_accounts hcu,
4182 ra_customer_banks_int_all cbi
4183 WHERE cbi.request_id = p_request_id
4184 AND cbi.primary_flag = 'Y'
4185 AND cbi.orig_system_address_ref= hca.orig_system_reference
4186 AND cbi.org_id = hca.org_id
4187 AND hca.cust_acct_site_id = hcs.cust_acct_site_id
4188 AND eba.currency_code = cbi.bank_account_currency_code
4189 AND cbi.orig_system_customer_ref = hcu.orig_system_reference
4190 AND iep.cust_account_id = hcu.cust_account_id
4191 AND iep.party_id = hcu.party_id
4192 AND iep.org_id = cbi.org_id
4193 AND iep.org_type = 'OPERATING_UNIT'
4194 AND iep.acct_site_use_id = hcs.site_use_id
4195 AND nvl(hca.status,'A') = 'A'
4196 AND nvl(hcs.status,'A') = 'A'
4197 AND iep.ext_payer_id = ipi.ext_pmt_party_id
4198 AND ipi.payment_function = 'CUSTOMER_PAYMENT'
4199 AND ipi.payment_flow = 'FUNDS_CAPTURE'
4200 AND ipi.instrument_type = 'BANKACCOUNT'
4201 AND ipi.instrument_id = eba.ext_bank_account_id
4202 AND cbi.interface_status is null
4203 order by ipi.order_of_preference desc;
4204
4205 TYPE IDlist IS TABLE OF NUMBER(15);
4206 i_instrument_payment_use_id IDlist;
4207 i_order_of_preference IDlist;
4208
4209 s_instrument_payment_use_id IDlist;
4210 s_order_of_preference IDlist;
4211
4212 BEGIN
4213
4214 open get_cust_bank_acct_priority;
4215 fetch get_cust_bank_acct_priority bulk collect into
4216 i_instrument_payment_use_id,i_order_of_preference;
4217 close get_cust_bank_acct_priority;
4218
4219 forall i in 1..i_instrument_payment_use_id.count
4220 update iby_pmt_instr_uses_all
4221 set order_of_preference = i_order_of_preference(i) +1
4222 where instrument_payment_use_id = i_instrument_payment_use_id(i);
4223
4224 open get_site_bank_acct_priority;
4225 fetch get_site_bank_acct_priority bulk collect into
4226 s_instrument_payment_use_id,s_order_of_preference;
4227 close get_site_bank_acct_priority;
4228
4229 forall i in 1..s_instrument_payment_use_id.count
4230 update iby_pmt_instr_uses_all
4231 set order_of_preference = s_order_of_preference(i) +1
4232 where instrument_payment_use_id = s_instrument_payment_use_id(i);
4233
4234 END update_bank_priority;
4235
4236 END hz_customer_int;