1 PACKAGE BODY ARP_EXCHANGE_MERGE as
2 /* $Header: AREXCHMB.pls 120.1 2005/06/16 21:06:48 jhuang ship $ */
3
4 /*---------------------------- PRIVATE VARIABLES ----------------------------*/
5 g_count NUMBER := 0;
6
7 /*--------------------------- PRIVATE ROUTINES ------------------------------*/
8
9 procedure cust_merge (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
10
11
12 -- Get orig_system_reference for the two merging customers.
13 CURSOR c1 IS
14 SELECT
15 ca2.orig_system_reference dup_osr
16 FROM
17 RA_CUSTOMER_MERGES racm,
18 HZ_CUST_ACCOUNTS ca2
19 WHERE
20 racm.request_id = req_id
21 and racm.set_number = set_num
22 and racm.process_flag = 'N'
23 and racm.duplicate_id = ca2.cust_account_id
24 and ca2.orig_system_reference like 'EXCHANGE_CUST%';
25
26 l_merge_not_allowed BOOLEAN := FALSE;
27 l_cust_osr varchar2(240);
28 l_dup_osr varchar2(240);
29 l_error varchar2(2000);
30 MERGE_NOT_ALLOWED EXCEPTION;
31
32 BEGIN
33 arp_message.set_line( 'ARP_EXCHANGE_MERGE.cust_merge()+' );
34
35 IF (process_mode = 'LOCK') then
36
37 /*
38 * No locking is necessary, as no updates will be done locally.
39 * arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
40 * arp_message.set_token( 'TABLE_NAME', 'tablename', FALSE );
41 * OPEN C1;
42 * CLOSE C1;
43 *
44 */
45
46 null;
47
48 ELSE
49
50 /* Logic:
51 1. Get info from ra_customer_merges.
52 2. Get orig_system_reference from hz_cust_accounts
53 3. If OSR is like 'EXCHANGE_CUST', veto. This kind of use of OSR
54 by oex billing will soon change when the oex billing
55 design changes.
56 */
57 OPEN c1;
58
59 FETCH c1 into l_dup_osr;
60
61 IF (c1%FOUND) THEN
62 -- if we are here, that means atleast one merge pair contained an exchange customer.
63 -- Fail the whole set
64 l_merge_not_allowed := TRUE;
65 END IF;
66
67 IF (c1%ISOPEN) THEN
68 CLOSE c1;
69 END IF;
70
71 IF l_merge_not_allowed THEN
72 raise MERGE_NOT_ALLOWED;
73 END IF;
74 END IF;
75
76 arp_message.set_line( 'ARP_EXCHANGE_MERGE.CUST_MERGE()-' );
77
78
79 EXCEPTION
80 WHEN MERGE_NOT_ALLOWED THEN
81 arp_message.set_name('AR','HZ_EXCHANGE_MERGE_DISALLOWED');
82 arp_message.set_error( 'ARP_EXCHANGE_MERGE.CUST_MERGE');
83 raise;
84 when others then
85 raise;
86
87 END;
88
89
90 /*---------------------------- PUBLIC ROUTINES ------------------------------*/
91
92 PROCEDURE CMERGE (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
93 BEGIN
94
95 arp_message.set_line( 'ARP_EXCHANGE_MERGE.CMERGE()+' );
96
97 cust_merge( req_id, set_num, process_mode );
98
99 arp_message.set_line( 'ARP_EXCHANGE_MERGE.CMERGE()-' );
100
101 EXCEPTION
102 when others then
103 raise;
104
105 END CMERGE;
106
107 end ARP_EXCHANGE_MERGE;
[Home] [Help]