[Home] [Help]
PACKAGE BODY: APPS.PV_ACCOUNT_MERGE_PKG
Source
1 PACKAGE BODY PV_ACCOUNT_MERGE_PKG AS
2 /* $Header: pvxvmrab.pls 115.0 2004/03/18 21:41:44 pklin ship $ */
3
4 -- Start of Comments
5 -- Package name : PVX_ACCOUNT_MERGE_PKG
6 -- Purpose : Merges duplicate accounts in PV tables. The
7
8 --
9 -- History
10 -- MM-DD-YYYY NAME MODIFICATIONS
11 -- 03-16-2004 pklin Created
12 --
13 -- End of Comments
14
15
16 /*-------------------------------------------------------------
17 |
18 | PROCEDURE
19 | MERGE_REFERRAL_ACCOUNT
20 | DESCRIPTION :
21 | Account merge procedure for the table, PV_REFERRALS_B
22 |
23 |--------------------------------------------------------------*/
24
25 PROCEDURE MERGE_REFERRAL_ACCOUNT (
26 req_id NUMBER,
27 set_num NUMBER,
28 process_mode VARCHAR2) IS
29
30 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
31 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
32 INDEX BY BINARY_INTEGER;
33 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
34
35 TYPE REFERRAL_ID_LIST_TYPE IS TABLE OF
36 PV_REFERRALS_B.REFERRAL_ID%TYPE
37 INDEX BY BINARY_INTEGER;
38 PRIMARY_KEY_ID_LIST REFERRAL_ID_LIST_TYPE;
39
40 TYPE PARTNER_CUST_ACCOUNT_ID_TYPE IS TABLE OF
41 PV_REFERRALS_B.PARTNER_CUST_ACCOUNT_ID%TYPE
42 INDEX BY BINARY_INTEGER;
43
44 NUM_COL1_ORIG_LIST PARTNER_CUST_ACCOUNT_ID_TYPE;
45 NUM_COL1_NEW_LIST PARTNER_CUST_ACCOUNT_ID_TYPE;
46
47 l_profile_val VARCHAR2(30);
48
49 CURSOR merged_records IS
50 SELECT distinct CUSTOMER_MERGE_HEADER_ID
51 ,REFERRAL_ID
52 ,PARTNER_CUST_ACCOUNT_ID
53 FROM PV_REFERRALS_B yt, ra_customer_merges m
54 WHERE (
55 yt.PARTNER_CUST_ACCOUNT_ID = m.DUPLICATE_ID
56 ) AND m.process_flag = 'N'
57 AND m.request_id = req_id
58 AND m.set_number = set_num;
59
60 l_last_fetch BOOLEAN := FALSE;
61 l_count NUMBER;
62
63 BEGIN
64 IF process_mode='LOCK' THEN
65 NULL;
66
67 ELSE
68 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
69 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','PV_REFERRALS_B',FALSE);
70 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
71 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
72
73 OPEN merged_records;
74
75 LOOP
76 FETCH merged_records BULK COLLECT INTO
77 MERGE_HEADER_ID_LIST
78 , PRIMARY_KEY_ID_LIST
79 , NUM_COL1_ORIG_LIST
80 ;
81
82 IF merged_records%NOTFOUND THEN
83 l_last_fetch := TRUE;
84 END IF;
85
86 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
87 exit;
88 END IF;
89
90 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
91 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
92 END LOOP;
93
94 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
95 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
96 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
97 MERGE_LOG_ID,
98 TABLE_NAME,
99 MERGE_HEADER_ID,
100 PRIMARY_KEY_ID,
101 NUM_COL1_ORIG,
102 NUM_COL1_NEW,
103 ACTION_FLAG,
104 REQUEST_ID,
105 CREATED_BY,
106 CREATION_DATE,
107 LAST_UPDATE_LOGIN,
108 LAST_UPDATE_DATE,
109 LAST_UPDATED_BY
110 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
111 'PV_REFERRALS_B',
112 MERGE_HEADER_ID_LIST(I),
113 PRIMARY_KEY_ID_LIST(I),
114 NUM_COL1_ORIG_LIST(I),
115 NUM_COL1_NEW_LIST(I),
116 'U',
117 req_id,
118 hz_utility_pub.CREATED_BY,
119 hz_utility_pub.CREATION_DATE,
120 hz_utility_pub.LAST_UPDATE_LOGIN,
121 hz_utility_pub.LAST_UPDATE_DATE,
122 hz_utility_pub.LAST_UPDATED_BY
123 );
124
125 END IF;
126
127 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
128 UPDATE PV_REFERRALS_B yt SET
129 PARTNER_CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
130 , LAST_UPDATE_DATE=SYSDATE
131 , last_updated_by=arp_standard.profile.user_id
132 , last_update_login=arp_standard.profile.last_update_login
133 WHERE REFERRAL_ID=PRIMARY_KEY_ID_LIST(I);
134
135 l_count := l_count + SQL%ROWCOUNT;
136
137 IF l_last_fetch THEN
138 EXIT;
139 END IF;
140 END LOOP;
141
142 arp_message.set_name('AR','AR_ROWS_UPDATED');
143 arp_message.set_token('NUM_ROWS',to_char(l_count));
144 END IF;
145
146 -- -----------------------Exception----------------------------------
147 EXCEPTION
148 WHEN OTHERS THEN
149 arp_message.set_line( 'MERGE_REFERRAL_ACCOUNT');
150 RAISE;
151
152 END MERGE_REFERRAL_ACCOUNT;
153
154 END PV_ACCOUNT_MERGE_PKG ;