[Home] [Help]
PACKAGE BODY: APPS.XDP_PARTY_MERGE
Source
1 PACKAGE BODY XDP_PARTY_MERGE AS
2 /* $Header: XDPMERGB.pls 120.3 2006/04/10 23:21:02 dputhiye noship $ */
3
4 -- PL/SQL Specification
5
6 PROCEDURE account_merge( request_id NUMBER,
7 set_number NUMBER,
8 process_mode VARCHAR2 )
9 IS
10 l_request_id NUMBER:= request_id;
11
12 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
13 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
14 INDEX BY BINARY_INTEGER;
15 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
16
17 TYPE ORDER_ID_LIST_TYPE IS TABLE OF
18 XDP_ORDER_HEADERS.ORDER_ID%TYPE
19 INDEX BY BINARY_INTEGER;
20 PRIMARY_KEY_ID_LIST ORDER_ID_LIST_TYPE;
21
22 TYPE CUST_ACCOUNT_ID_LIST_TYPE IS TABLE OF
23 XDP_ORDER_HEADERS.CUST_ACCOUNT_ID%TYPE
24 INDEX BY BINARY_INTEGER;
25 NUM_COL1_ORIG_LIST CUST_ACCOUNT_ID_LIST_TYPE;
26 NUM_COL1_NEW_LIST CUST_ACCOUNT_ID_LIST_TYPE;
27
28 l_profile_val VARCHAR2(30);
29 CURSOR merged_records IS
30 SELECT distinct CUSTOMER_MERGE_HEADER_ID
31 ,ORDER_ID
32 ,CUST_ACCOUNT_ID
33 FROM XDP_ORDER_HEADERS yt, ra_customer_merges m
34 WHERE (
35 yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID
36 ) AND m.process_flag = 'N'
37 AND m.request_id = request_id
38 AND m.set_number = set_number;
39 l_last_fetch BOOLEAN := FALSE;
40 l_count NUMBER;
41
42 TYPE LINE_ITEM_ID_LIST_TYPE IS TABLE OF
43 XDP_ORDER_LINE_ITEMS.LINE_ITEM_ID%TYPE
44 INDEX BY BINARY_INTEGER;
45 PRIMARY_KEY_ID_LIST1 LINE_ITEM_ID_LIST_TYPE;
46
47 TYPE SITE_USE_ID_LIST_TYPE IS TABLE OF
48 XDP_ORDER_LINE_ITEMS.SITE_USE_ID%TYPE
49 INDEX BY BINARY_INTEGER;
50 NUM_COL1_ORIG_LIST1 SITE_USE_ID_LIST_TYPE;
51 NUM_COL1_NEW_LIST1 SITE_USE_ID_LIST_TYPE;
52
53 CURSOR merged_records1 IS
54 SELECT distinct CUSTOMER_MERGE_HEADER_ID
55 ,LINE_ITEM_ID
56 ,SITE_USE_ID
57 FROM XDP_ORDER_LINE_ITEMS yt, ra_customer_merges m
58 WHERE (
59 yt.SITE_USE_ID = m.DUPLICATE_SITE_ID
60 ) AND m.process_flag = 'N'
61 AND m.request_id = request_id
62 AND m.set_number = set_number;
63
64
65 BEGIN
66
67 IF process_mode='LOCK' THEN
68 NULL;
69 ELSE
70 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
71 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','XDP_ORDER_HEADERS',FALSE);
72 HZ_ACCT_MERGE_UTIL.load_set(set_number, request_id);
73 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
74
75 open merged_records;
76 LOOP
77 FETCH merged_records BULK COLLECT INTO
78 MERGE_HEADER_ID_LIST
79 , PRIMARY_KEY_ID_LIST
80 , NUM_COL1_ORIG_LIST
81 limit 1000;
82 IF merged_records%NOTFOUND THEN
83 l_last_fetch := TRUE;
84 END IF;
85 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
86 exit;
87 END IF;
88 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
89 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
90 END LOOP;
91 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
92 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
93 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
94 MERGE_LOG_ID,
95 TABLE_NAME,
96 MERGE_HEADER_ID,
97 PRIMARY_KEY_ID,
98 NUM_COL1_ORIG,
99 NUM_COL1_NEW,
100 ACTION_FLAG,
101 REQUEST_ID,
102 CREATED_BY,
103 CREATION_DATE,
104 LAST_UPDATE_LOGIN,
105 LAST_UPDATE_DATE,
106 LAST_UPDATED_BY
107 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
108 'XDP_ORDER_HEADERS',
109 MERGE_HEADER_ID_LIST(I),
110 PRIMARY_KEY_ID_LIST(I),
111 NUM_COL1_ORIG_LIST(I),
112 NUM_COL1_NEW_LIST(I),
113 'U',
114 request_id,
115 hz_utility_pub.CREATED_BY,
116 hz_utility_pub.CREATION_DATE,
117 hz_utility_pub.LAST_UPDATE_LOGIN,
118 hz_utility_pub.LAST_UPDATE_DATE,
119 hz_utility_pub.LAST_UPDATED_BY
120 );
121
122 END IF;
123 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
124 UPDATE XDP_ORDER_HEADERS yt SET
125 CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
126 , LAST_UPDATE_DATE=SYSDATE
127 , last_updated_by=arp_standard.profile.user_id
128 , last_update_login=arp_standard.profile.last_update_login
129 -- , REQUEST_ID=request_id
130 -- , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
131 -- , PROGRAM_ID=arp_standard.profile.program_id
132 -- , PROGRAM_UPDATE_DATE=SYSDATE
133 WHERE ORDER_ID=PRIMARY_KEY_ID_LIST(I)
134 ;
135 l_count := l_count + SQL%ROWCOUNT;
136 IF l_last_fetch THEN
137 EXIT;
138 END IF;
139 END LOOP;
140
141 arp_message.set_name('AR','AR_ROWS_UPDATED');
142 arp_message.set_token('NUM_ROWS',to_char(l_count));
143 END IF;
144
145 IF process_mode='LOCK' THEN
146 NULL;
147 ELSE
148 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
149 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','XDP_ORDER_LINE_ITEMS',FALSE);
150 HZ_ACCT_MERGE_UTIL.load_set(set_number, request_id);
151 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
152
153 open merged_records1;
154 LOOP
155 FETCH merged_records1 BULK COLLECT INTO
156 MERGE_HEADER_ID_LIST
157 , PRIMARY_KEY_ID_LIST1
158 , NUM_COL1_ORIG_LIST1
159 limit 1000;
160 IF merged_records1%NOTFOUND THEN
161 l_last_fetch := TRUE;
162 END IF;
163 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
164 exit;
165 END IF;
166 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
167 NUM_COL1_NEW_LIST1(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL1_ORIG_LIST1(I));
168 END LOOP;
169 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
170 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
171 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
172 MERGE_LOG_ID,
173 TABLE_NAME,
174 MERGE_HEADER_ID,
175 PRIMARY_KEY_ID,
176 NUM_COL1_ORIG,
177 NUM_COL1_NEW,
178 ACTION_FLAG,
179 REQUEST_ID,
180 CREATED_BY,
181 CREATION_DATE,
182 LAST_UPDATE_LOGIN,
183 LAST_UPDATE_DATE,
184 LAST_UPDATED_BY
185 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
186 'XDP_ORDER_LINE_ITEMS',
187 MERGE_HEADER_ID_LIST(I),
188 PRIMARY_KEY_ID_LIST1(I),
189 NUM_COL1_ORIG_LIST1(I),
190 NUM_COL1_NEW_LIST1(I),
191 'U',
192 request_id,
193 hz_utility_pub.CREATED_BY,
194 hz_utility_pub.CREATION_DATE,
195 hz_utility_pub.LAST_UPDATE_LOGIN,
196 hz_utility_pub.LAST_UPDATE_DATE,
197 hz_utility_pub.LAST_UPDATED_BY
198 );
199
200 END IF;
201 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
202 UPDATE XDP_ORDER_LINE_ITEMS yt SET
203 SITE_USE_ID=NUM_COL1_NEW_LIST1(I)
204 , LAST_UPDATE_DATE=SYSDATE
205 , last_updated_by=arp_standard.profile.user_id
206 , last_update_login=arp_standard.profile.last_update_login
207 -- , REQUEST_ID=request_id
208 -- , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
209 -- , PROGRAM_ID=arp_standard.profile.program_id
210 -- , PROGRAM_UPDATE_DATE=SYSDATE
211 WHERE LINE_ITEM_ID=PRIMARY_KEY_ID_LIST1(I)
212 ;
213 l_count := l_count + SQL%ROWCOUNT;
214 IF l_last_fetch THEN
215 EXIT;
216 END IF;
217 END LOOP;
218
219 arp_message.set_name('AR','AR_ROWS_UPDATED');
220 arp_message.set_token('NUM_ROWS',to_char(l_count));
221 END IF;
222 EXCEPTION
223 WHEN OTHERS THEN
224 /* Report the error in the log table and reraise the exception */
225 /* The exception MUST be reraised */
226 arp_message.set_error('CRM_MERGE.XDP_PARTY_MERGE');
227 raise;
228
229 END account_merge;
230
231 END XDP_PARTY_MERGE;