[Home] [Help]
PACKAGE BODY: APPS.ARP_CMERGE_ARATC
Source
1 PACKAGE BODY ARP_CMERGE_ARATC as
2 /* $Header: ARPLATCB.pls 120.5 2005/10/30 04:24:15 appldev ship $ */
3
4 /*---------------------------- PRIVATE VARIABLES ----------------------------*/
5 g_count NUMBER := 0;
6
7
8 /*--------------------------- PRIVATE ROUTINES ------------------------------*/
9
10 PROCEDURE ar_icr (
11 req_id NUMBER,
12 set_num NUMBER,
13 process_mode VARCHAR2) IS
14
15 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
16 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
17 INDEX BY BINARY_INTEGER;
18 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
19
20 TYPE cash_receipt_id_LIST_TYPE IS TABLE OF
21 AR_INTERIM_CASH_RECEIPTS.cash_receipt_id%TYPE
22 INDEX BY BINARY_INTEGER;
23 PRIMARY_KEY_ID_LIST cash_receipt_id_LIST_TYPE;
24
25 TYPE pay_from_customer_LIST_TYPE IS TABLE OF
26 AR_INTERIM_CASH_RECEIPTS.pay_from_customer%TYPE
27 INDEX BY BINARY_INTEGER;
28 NUM_COL1_ORIG_LIST pay_from_customer_LIST_TYPE;
29 NUM_COL1_NEW_LIST pay_from_customer_LIST_TYPE;
30
31 TYPE site_use_id_LIST_TYPE IS TABLE OF
32 AR_INTERIM_CASH_RECEIPTS.site_use_id%TYPE
33 INDEX BY BINARY_INTEGER;
34 NUM_COL2_ORIG_LIST site_use_id_LIST_TYPE;
35 NUM_COL2_NEW_LIST site_use_id_LIST_TYPE;
36
37 l_profile_val VARCHAR2(30);
38 CURSOR merged_records IS
39 SELECT distinct CUSTOMER_MERGE_HEADER_ID
40 ,cash_receipt_id
41 ,pay_from_customer
42 ,site_use_id
43 FROM AR_INTERIM_CASH_RECEIPTS yt, ra_customer_merges m
44 WHERE ( yt.pay_from_customer = m.DUPLICATE_ID AND
45 nvl(yt.site_use_id,m.DUPLICATE_SITE_ID) = m.DUPLICATE_SITE_ID)
46 AND m.process_flag = 'N'
47 AND m.request_id = req_id
48 AND m.set_number = set_num;
49 l_last_fetch BOOLEAN := FALSE;
50 l_count NUMBER;
51 BEGIN
52 IF process_mode='LOCK' THEN
53 NULL;
54 ELSE
55 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
56 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','AR_INTERIM_CASH_RECEIPTS',FALSE);
57 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
58 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
59
60 open merged_records;
61 LOOP
62 FETCH merged_records BULK COLLECT INTO
63 MERGE_HEADER_ID_LIST
64 , PRIMARY_KEY_ID_LIST
65 , NUM_COL1_ORIG_LIST
66 , NUM_COL2_ORIG_LIST
67 LIMIT ARP_CMERGE.max_array_size;/*Bug 2447449*/
68 IF merged_records%NOTFOUND THEN
69 l_last_fetch := TRUE;
70 END IF;
71 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
72 exit;
73 END IF;
74 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
75 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
76 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
77 END LOOP;
78 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
79 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
80 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
81 MERGE_LOG_ID,
82 TABLE_NAME,
83 MERGE_HEADER_ID,
84 PRIMARY_KEY_ID,
85 NUM_COL1_ORIG,
86 NUM_COL1_NEW,
87 NUM_COL2_ORIG,
88 NUM_COL2_NEW,
89 ACTION_FLAG,
90 REQUEST_ID,
91 CREATED_BY,
92 CREATION_DATE,
93 LAST_UPDATE_LOGIN,
94 LAST_UPDATE_DATE,
95 LAST_UPDATED_BY
96 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
97 'AR_INTERIM_CASH_RECEIPTS',
98 MERGE_HEADER_ID_LIST(I),
99 PRIMARY_KEY_ID_LIST(I),
100 NUM_COL1_ORIG_LIST(I),
101 NUM_COL1_NEW_LIST(I),
102 NUM_COL2_ORIG_LIST(I),
103 NUM_COL2_NEW_LIST(I),
104 'U',
105 req_id,
106 hz_utility_pub.CREATED_BY,
107 hz_utility_pub.CREATION_DATE,
108 hz_utility_pub.LAST_UPDATE_LOGIN,
109 hz_utility_pub.LAST_UPDATE_DATE,
110 hz_utility_pub.LAST_UPDATED_BY
111 );
112
113 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
114 UPDATE AR_INTERIM_CASH_RECEIPTS yt SET
115 pay_from_customer=NUM_COL1_NEW_LIST(I)
116 ,site_use_id=NUM_COL2_NEW_LIST(I)
117 , LAST_UPDATE_DATE=SYSDATE
118 , last_updated_by=arp_standard.profile.user_id
119 , last_update_login=arp_standard.profile.last_update_login
120 , REQUEST_ID=req_id
121 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
122 , PROGRAM_ID=arp_standard.profile.program_id
123 , PROGRAM_UPDATE_DATE=SYSDATE
124 WHERE cash_receipt_id=PRIMARY_KEY_ID_LIST(I)
125 ;
126 l_count := l_count + SQL%ROWCOUNT;
127 IF l_last_fetch THEN
128 EXIT;
129 END IF;
130 END LOOP;
131
132 arp_message.set_name('AR','AR_ROWS_UPDATED');
133 arp_message.set_token('NUM_ROWS',to_char(l_count));
134 END IF;
135 EXCEPTION
136 WHEN OTHERS THEN
137 arp_message.set_line( 'ar_icr');
138 RAISE;
139 END ar_icr;
140
141 PROCEDURE ar_icrl (
142 req_id NUMBER,
143 set_num NUMBER,
144 process_mode VARCHAR2) IS
145
146 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
147 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
148 INDEX BY BINARY_INTEGER;
149 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
150
151 TYPE cash_receipt_id_LIST_TYPE IS TABLE OF
152 AR_INTERIM_CASH_RECEIPT_LINES.cash_receipt_id%TYPE
153 INDEX BY BINARY_INTEGER;
154 PRIMARY_KEY1_LIST cash_receipt_id_LIST_TYPE;
155
156 TYPE cash_receipt_line_id_LIST_TYPE IS TABLE OF
157 AR_INTERIM_CASH_RECEIPT_LINES.cash_receipt_line_id%TYPE
158 INDEX BY BINARY_INTEGER;
159 PRIMARY_KEY2_LIST cash_receipt_line_id_LIST_TYPE;
160
161 TYPE sold_to_customer_LIST_TYPE IS TABLE OF
162 AR_INTERIM_CASH_RECEIPT_LINES.sold_to_customer%TYPE
163 INDEX BY BINARY_INTEGER;
164 NUM_COL1_ORIG_LIST sold_to_customer_LIST_TYPE;
165 NUM_COL1_NEW_LIST sold_to_customer_LIST_TYPE;
166
167 l_profile_val VARCHAR2(30);
168 CURSOR merged_records IS
169 SELECT distinct CUSTOMER_MERGE_HEADER_ID
170 ,cash_receipt_id
171 ,cash_receipt_line_id
172 ,sold_to_customer
173 FROM AR_INTERIM_CASH_RECEIPT_LINES yt, ra_customer_merges m
174 WHERE (
175 yt.sold_to_customer = m.DUPLICATE_ID
176 ) AND m.process_flag = 'N'
177 AND m.request_id = req_id
178 AND m.set_number = set_num;
179 l_last_fetch BOOLEAN := FALSE;
180 l_count NUMBER;
181 BEGIN
182 IF process_mode='LOCK' THEN
183 NULL;
184 ELSE
185 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
186 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','AR_INTERIM_CASH_RECEIPT_LINES',FALSE);
187 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
188 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
189
190 open merged_records;
191 LOOP
192 FETCH merged_records BULK COLLECT INTO
193 MERGE_HEADER_ID_LIST
194 , PRIMARY_KEY1_LIST
195 , PRIMARY_KEY2_LIST
196 , NUM_COL1_ORIG_LIST
197 LIMIT ARP_CMERGE.max_array_size;/*Additional changes for 2447449*/
198 IF merged_records%NOTFOUND THEN
199 l_last_fetch := TRUE;
200 END IF;
201 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
202 exit;
203 END IF;
204 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
205 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
206 END LOOP;
207 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
208 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
209 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
210 MERGE_LOG_ID,
211 TABLE_NAME,
212 MERGE_HEADER_ID,
213 PRIMARY_KEY1,
214 PRIMARY_KEY2,
215 NUM_COL1_ORIG,
216 NUM_COL1_NEW,
217 ACTION_FLAG,
218 REQUEST_ID,
219 CREATED_BY,
220 CREATION_DATE,
221 LAST_UPDATE_LOGIN,
222 LAST_UPDATE_DATE,
223 LAST_UPDATED_BY
224 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
225 'AR_INTERIM_CASH_RECEIPT_LINES',
226 MERGE_HEADER_ID_LIST(I),
227 PRIMARY_KEY1_LIST(I),
228 PRIMARY_KEY2_LIST(I),
229 NUM_COL1_ORIG_LIST(I),
230 NUM_COL1_NEW_LIST(I),
231 'U',
232 req_id,
233 hz_utility_pub.CREATED_BY,
234 hz_utility_pub.CREATION_DATE,
235 hz_utility_pub.LAST_UPDATE_LOGIN,
236 hz_utility_pub.LAST_UPDATE_DATE,
237 hz_utility_pub.LAST_UPDATED_BY
238 );
239
240 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
241 UPDATE AR_INTERIM_CASH_RECEIPT_LINES yt SET
242 sold_to_customer=NUM_COL1_NEW_LIST(I)
243 , LAST_UPDATE_DATE=SYSDATE
244 , last_updated_by=arp_standard.profile.user_id
245 , last_update_login=arp_standard.profile.last_update_login
246 , REQUEST_ID=req_id
247 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
248 , PROGRAM_ID=arp_standard.profile.program_id
249 , PROGRAM_UPDATE_DATE=SYSDATE
250 WHERE cash_receipt_id=PRIMARY_KEY1_LIST(I)
251 AND cash_receipt_line_id=PRIMARY_KEY2_LIST(I)
252 ;
253 l_count := l_count + SQL%ROWCOUNT;
254 IF l_last_fetch THEN
255 EXIT;
256 END IF;
257 END LOOP;
258
259 arp_message.set_name('AR','AR_ROWS_UPDATED');
260 arp_message.set_token('NUM_ROWS',to_char(l_count));
261 END IF;
262 EXCEPTION
263 WHEN OTHERS THEN
264 arp_message.set_line( 'ar_icrl');
265 RAISE;
266 END ar_icrl;
267
268 /*---------------------------- PUBLIC ROUTINES ------------------------------*/
269 PROCEDURE merge (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
270 BEGIN
271
272 arp_message.set_line( 'ARP_CMERGE_ARATC.MERGE()+' );
273
274 AR_ICR( req_id, set_num, process_mode );
275 AR_ICRL( req_id, set_num, process_mode );
276
277 arp_message.set_line( 'ARP_CMERGE_ARATC.MERGE()-' );
278
279 END merge;
280
281 end ARP_CMERGE_ARATC;