[Home] [Help]
PACKAGE BODY: APPS.CS_CH_ACCOUNT_MERGE_PKG
Source
1 PACKAGE BODY CS_CH_ACCOUNT_MERGE_PKG AS
2 /* $Header: cschmagb.pls 115.0 2003/05/08 21:21:22 mviswana noship $ */
3
4
5 PROCEDURE MERGE_CUST_ACCOUNTS (req_id IN NUMBER,
6 set_number IN NUMBER,
7 process_mode IN VARCHAR2 ) IS
8
9 message_text varchar2(255);
10 number_of_rows NUMBER;
11
12 BEGIN
13
14 ---Put the header in the report to identify the block to be run
15
16 arp_message.set_line('CRM_MERGE.SR_MERGE()+');
17
18 IF ( process_mode = 'LOCK' ) Then
19 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
20 arp_message.set_token('TABLE_NAME', 'CS_ESTIMATE_DETAILS',FALSE );
21 ELSE
22 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
23 arp_message.set_token('TABLE_NAME', 'CS_ESTIMATE_DETAILS',FALSE );
24
25 END IF;
26
27 ----Merge the CS_ESTIMATE_DETAILS table update the account_id
28
29 message_text := '***-- Procedure CS_CH_MERGE_CUST_ACCOUNT_ID --**';
30 arp_message.set_line(message_text);
31
32 ---dbms_output.put_line('am going to call small proc');
33
34 CS_CH_MERGE_CUST_ACCOUNT_ID( req_id, set_number, process_mode );
35
36 message_text := '***-- End CS_CH_MERGE_CUST_ACCOUNT_ID --**';
37 arp_message.set_line(message_text);
38
39
40 ---Report that the process for CS_ESTIMATE_DETAILS is complete
41
42 IF ( process_mode = 'LOCK' ) Then
43 message_text := '** LOCKING completed for table CS_ESTIMATE_DETAILS **';
44 arp_message.set_line(message_text);
45 ELSE
46 message_text := '** MERGE completed for table CS_ESTIMATE_DETAILS **';
47 arp_message.set_line(message_text);
48 END IF;
49
50 arp_message.set_line('CRM_MERGE.SR_MERGE()-');
51
52 END MERGE_CUST_ACCOUNTS;
53
54 -- The following procedure merges the following columns from CS_ESTIMATE_DETAILS
55 -- account_id
56 -- invoice_to_account_id - added for 11.5.9
57 -- ship_to_account_id - added for 11.5.9
58
59 PROCEDURE CS_CH_MERGE_CUST_ACCOUNT_ID (
60 req_id NUMBER,
61 set_number NUMBER,
62 process_mode VARCHAR2) IS
63
64 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
65 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
66 INDEX BY BINARY_INTEGER;
67 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
68
69 TYPE ESTIMATE_DETAIL_ID_LIST_TYPE IS TABLE OF
70 CS_ESTIMATE_DETAILS.ESTIMATE_DETAIL_ID%TYPE
71 INDEX BY BINARY_INTEGER;
72 PRIMARY_KEY_ID_LIST ESTIMATE_DETAIL_ID_LIST_TYPE;
73
74 TYPE INVOICE_TO_ACCT_ID_LIST_TYPE IS TABLE OF
75 CS_ESTIMATE_DETAILS.INVOICE_TO_ACCOUNT_ID%TYPE
76 INDEX BY BINARY_INTEGER;
77 NUM_COL1_ORIG_LIST INVOICE_TO_ACCT_ID_LIST_TYPE;
78 NUM_COL1_NEW_LIST INVOICE_TO_ACCT_ID_LIST_TYPE;
79
80 TYPE SHIP_TO_ACCT_ID_LIST_TYPE IS TABLE OF
81 CS_ESTIMATE_DETAILS.SHIP_TO_ACCOUNT_ID%TYPE
82 INDEX BY BINARY_INTEGER;
83 NUM_COL2_ORIG_LIST SHIP_TO_ACCT_ID_LIST_TYPE;
84 NUM_COL2_NEW_LIST SHIP_TO_ACCT_ID_LIST_TYPE;
85
86 CURSOR merged_records IS
87 SELECT distinct CUSTOMER_MERGE_HEADER_ID
88 ,ESTIMATE_DETAIL_ID
89 ,INVOICE_TO_ACCOUNT_ID
90 ,SHIP_TO_ACCOUNT_ID
91 FROM CS_ESTIMATE_DETAILS yt,
92 ra_customer_merges m
93 WHERE ( yt.INVOICE_TO_ACCOUNT_ID = m.DUPLICATE_ID
94 OR yt.SHIP_TO_ACCOUNT_ID = m.DUPLICATE_ID)
95 AND m.process_flag = 'N'
96 AND m.request_id = req_id
97 AND m.set_number = set_number;
98
99 CURSOR PARTY_CUR(p_cust_account_id NUMBER)is
100 SELECT PARTY_ID
101 FROM HZ_CUST_ACCOUNTS HCA
102 WHERE cust_account_id = p_cust_account_id;
103
104 CURSOR CUST_MERGE_CUR(req_id NUMBER, set_num NUMBER) IS
105 select CUSTOMER_ID, DUPLICATE_ID
106 from RA_CUSTOMER_MERGES RCM
107 Where rcm.request_id = req_id
108 And rcm.set_number = set_number
109 And rcm.process_flag = 'N';
110
111 l_profile_val VARCHAR2(30);
112 g_customer_id RA_CUSTOMER_MERGES.CUSTOMER_ID%TYPE;
113 g_duplicate_id RA_CUSTOMER_MERGES.DUPLICATE_ID%TYPE;
114
115 g_cust_party_id HZ_PARTIES.PARTY_ID%TYPE;
116 g_dup_party_id HZ_PARTIES.PARTY_ID%TYPE;
117
118 g_different_parties VARCHAR2(1) := 'N';
119 DIFFERENT_PARTIES EXCEPTION;
120
121 l_last_fetch BOOLEAN := FALSE;
122 l_count NUMBER;
123
124
125 BEGIN
126 IF process_mode='LOCK' THEN
127 NULL;
128 ELSE
129 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
130 ARP_MESSAGE.SET_TOKEN('TABLE_NAME',' CS_ESTIMATE_DETAILS',FALSE);
131
132 HZ_ACCT_MERGE_UTIL.load_set(set_number, req_id);
133 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
134
135
136 Open CUST_MERGE_CUR(Req_Id, Set_Number);
137 Loop
138 Fetch CUST_MERGE_CUR
139 into g_customer_id,g_duplicate_id;
140
141 EXIT WHEN CUST_MERGE_CUR%NOTFOUND;
142
143 Open PARTY_CUR(g_customer_id);
144 Fetch PARTY_CUR into g_cust_party_id;
145 Close PARTY_CUR;
146
147 Open PARTY_CUR(g_duplicate_id);
148 Fetch PARTY_CUR into g_dup_party_id;
149 Close PARTY_CUR;
150
151 If g_cust_party_id <> g_dup_party_id Then
152 g_different_parties := 'Y';
153 End If;
154 End Loop;
155
156 Close CUST_MERGE_CUR;
157
158 open merged_records;
159
160 LOOP
161 FETCH merged_records BULK COLLECT INTO
162 MERGE_HEADER_ID_LIST
163 , PRIMARY_KEY_ID_LIST
164 , NUM_COL1_ORIG_LIST
165 , NUM_COL2_ORIG_LIST
166 limit 1000;
167
168 IF merged_records%NOTFOUND THEN
169 l_last_fetch := TRUE;
170 END IF;
171
172 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
173 exit;
174 END IF;
175
176 IF (MERGE_HEADER_ID_LIST.COUNT > 0 AND g_different_parties = 'Y' ) THEN
177 Close merged_records;
178 Raise DIFFERENT_PARTIES;
179 END IF;
180
181 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
182 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
183 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL2_ORIG_LIST(I));
184 END LOOP;
185
186 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
187
188 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
189 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
190 MERGE_LOG_ID, TABLE_NAME,
191 MERGE_HEADER_ID, PRIMARY_KEY_ID,
192 NUM_COL1_ORIG, NUM_COL1_NEW,
193 NUM_COL2_ORIG, NUM_COL2_NEW,
194 ACTION_FLAG, REQUEST_ID,
195 CREATED_BY, CREATION_DATE,
196 LAST_UPDATE_LOGIN, LAST_UPDATE_DATE,
197 LAST_UPDATED_BY )
198 VALUES (
199 HZ_CUSTOMER_MERGE_LOG_s.nextval, 'CS_ESTIMATE_DETAILS',
200 MERGE_HEADER_ID_LIST(I), PRIMARY_KEY_ID_LIST(I),
201 NUM_COL1_ORIG_LIST(I), NUM_COL1_NEW_LIST(I),
202 NUM_COL2_ORIG_LIST(I), NUM_COL2_NEW_LIST(I),
203 'U', req_id,
204 hz_utility_pub.CREATED_BY, hz_utility_pub.CREATION_DATE,
205 hz_utility_pub.LAST_UPDATE_LOGIN, hz_utility_pub.LAST_UPDATE_DATE,
206 hz_utility_pub.LAST_UPDATED_BY );
207
208 END IF;
209
210 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
211 UPDATE CS_ESTIMATE_DETAILS yt SET
212 INVOICE_TO_ACCOUNT_ID = NUM_COL1_NEW_LIST(I)
213 , SHIP_TO_ACCOUNT_ID = NUM_COL2_NEW_LIST(I)
214 , LAST_UPDATE_DATE = SYSDATE
215 , last_updated_by = arp_standard.profile.user_id
216 , last_update_login = arp_standard.profile.last_update_login
217 WHERE ESTIMATE_DETAIL_ID=PRIMARY_KEY_ID_LIST(I) ;
218
219 l_count := l_count + SQL%ROWCOUNT;
220
221 IF l_last_fetch THEN
222 EXIT;
223 END IF;
224
225 END LOOP;
226
227 arp_message.set_name('AR','AR_ROWS_UPDATED');
228 arp_message.set_token('NUM_ROWS',to_char(l_count));
229 END IF;
230
231 EXCEPTION
232 WHEN DIFFERENT_PARTIES THEN
233 arp_message.set_name('CS','CS_ACCT_MERGE_NOT_ALLOWED');
234 RAISE;
235
236 WHEN OTHERS THEN
237 arp_message.set_line( 'CS_CH_MERGE_CUST_ACCOUNT_ID');
238 RAISE;
239
240 END CS_CH_MERGE_CUST_ACCOUNT_ID;
241
242 END CS_CH_ACCOUNT_MERGE_PKG ;
243