[Home] [Help]
PACKAGE BODY: APPS.HZ_CUSTOMER_INT
Source
1 PACKAGE BODY hz_customer_int AS
2 /*$Header: ARHCUSIB.pls 120.21.12010000.2 2008/10/17 13:02:45 idali 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 ) 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 l_request_data := TO_CHAR( l_times ) || ' ' ||
1186 TO_CHAR( m_request_id ) || ' ' ||
1187 TO_CHAR( l_sub_request_id ) || ' ';
1188
1189 -- if this is third run, do parallel validation
1190 -- if this is fouth run, do parallel insert/update
1191 ELSIF l_times IN ( 2, 4 ) THEN
1192 -- read profile option.
1193 l_profile := 'HZ_CINTERFACE_NUM_OF_WORKERS';
1194
1195 l_num_of_workers := NVL(FND_PROFILE.VALUE(l_profile), 1);
1196
1197 FND_FILE.PUT_LINE(FND_FILE.LOG,
1198 l_profile || ' = ' || TO_CHAR(l_num_of_workers));
1199
1200 l_request_data := '';
1201
1202 -- submit sub-requests to insert/update customers.
1203 -- do NOT do validation.
1204 FOR i IN 1..l_num_of_workers LOOP
1205 IF p_org_id <> 0 THEN
1206 FND_REQUEST.SET_ORG_ID(p_org_id);
1207 END IF;
1208 -- Bug 2092530: added create_reciprocal_flag arg
1209 l_sub_request_id := FND_REQUEST.SUBMIT_REQUEST(
1210 'AR', 'RACUSTSB', '',
1211 TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS'), --Bug 3175928
1212 TRUE,
1213 TO_CHAR(l_num_of_workers),
1214 TO_CHAR(i), TO_CHAR(l_times),
1215 p_create_reciprocal_flag, p_org_id);
1216
1217 IF l_sub_request_id = 0 THEN
1218
1219 FND_MESSAGE.SET_NAME('AR', 'AR_CUST_CONC_ERROR');
1220 FND_MESSAGE.RETRIEVE(l_message );
1221
1222 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(i) || ' : ' || l_message);
1223 retcode := 2;
1224
1225 ELSE
1226
1227 FND_MESSAGE.SET_NAME('AR', 'AR_CUST_SHOW_UPD_REQID');
1228 FND_MESSAGE.RETRIEVE(l_message);
1229
1230 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(i) || ' : ' || l_message);
1231 retcode := 0;
1232
1233 l_request_data := l_request_data || TO_CHAR(l_sub_request_id) || ' ';
1234 END IF;
1235
1236 END LOOP;
1237
1238 IF l_times = 4 THEN
1239 retcode := 0;
1240 RETURN;
1241 END IF;
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 END hz_customer_int;