[Home] [Help]
PACKAGE BODY: APPS.ARP_CMERGE_ARCON
Source
1 PACKAGE BODY ARP_CMERGE_ARCON as
2 /* $Header: ARCMCONB.pls 120.11 2005/10/30 04:14:26 appldev ship $ */
3
4 /*---------------------------- PRIVATE VARIABLES ----------------------------*/
5 g_count NUMBER := 0;
6
7
8 /*--------------------------- PRIVATE ROUTINES ------------------------------*/
9
10 PROCEDURE ar_cin (
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 cons_inv_id_LIST_TYPE IS TABLE OF
21 AR_CONS_INV.cons_inv_id%TYPE
22 INDEX BY BINARY_INTEGER;
23 PRIMARY_KEY_ID_LIST cons_inv_id_LIST_TYPE;
24
25 TYPE customer_id_LIST_TYPE IS TABLE OF
26 AR_CONS_INV.customer_id%TYPE
27 INDEX BY BINARY_INTEGER;
28 NUM_COL1_ORIG_LIST customer_id_LIST_TYPE;
29 NUM_COL1_NEW_LIST customer_id_LIST_TYPE;
30
31 TYPE site_use_id_LIST_TYPE IS TABLE OF
32 AR_CONS_INV.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 ,cons_inv_id
41 ,yt.customer_id
42 ,site_use_id
43 FROM AR_CONS_INV yt, ra_customer_merges m
44 WHERE ( yt.customer_id = m.DUPLICATE_ID AND
45 yt.site_use_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_CONS_INV',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_CONS_INV',
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;
114
115 l_count := l_count + SQL%ROWCOUNT;
116 IF l_last_fetch THEN
117 EXIT;
118 END IF;
119 END LOOP;
120
121 -- bug2778646 added the update stmt which set 'MERGE_PENDING' status to
122 -- merged and latest ar_cons_inv record.
123 -- bug3123397 added 'group by' to sub-query to update status of all site's
124 -- latest CBI.
125 UPDATE ar_cons_inv ci
126 SET status = 'MERGE_PENDING'
127 WHERE (cons_inv_id) in
128 (SELECT max(ci2.cons_inv_id)
129 FROM ar_cons_inv ci2,
130 ra_customer_merges m
131 WHERE ci2.site_use_id = m.duplicate_site_id
132 AND m.process_flag = 'N'
133 AND m.request_id = req_id
134 AND m.set_number = set_num
135 AND ci2.status = 'ACCEPTED'
136 GROUP BY duplicate_site_id ) ;
137
138 -- bug2778646 added "status" column and move outside the FOR loop.
139 -- added where clause to prevent from updating unrelated record.
140 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
141 UPDATE AR_CONS_INV yt SET
142 customer_id=NUM_COL1_NEW_LIST(I)
143 ,site_use_id=NUM_COL2_NEW_LIST(I)
144 , LAST_UPDATE_DATE=SYSDATE
145 , last_updated_by=arp_standard.profile.user_id
146 , last_update_login=arp_standard.profile.last_update_login
147 , status = decode(status , 'MERGE_PENDING','MERGE_PENDING','MERGED')
148 WHERE cons_inv_id=PRIMARY_KEY_ID_LIST(I)
149 AND (customer_id <> NUM_COL1_NEW_LIST(I)
150 OR site_use_id <> NUM_COL2_NEW_LIST(I) )
151 ;
152
153 arp_message.set_name('AR','AR_ROWS_UPDATED');
154 arp_message.set_token('NUM_ROWS',to_char(l_count));
155 END IF;
156 EXCEPTION
157 WHEN OTHERS THEN
158 arp_message.set_line( 'ar_cin');
159 RAISE;
160 END ar_cin;
161
162
163
164 /*---------------------------- PUBLIC ROUTINES ------------------------------*/
165
166 PROCEDURE merge (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
167 BEGIN
168
169 arp_message.set_line( 'ARP_CMERGE_ARCIN.MERGE()+' );
170
171 ar_cin( req_id, set_num, process_mode );
172
173 arp_message.set_line( 'ARP_CMERGE_ARCIN.MERGE()-' );
174
175 END merge;
176
177 end ARP_CMERGE_ARCON;