[Home] [Help]
PACKAGE BODY: APPS.INVP_CMERGE_TXHI
Source
1 PACKAGE BODY INVP_CMERGE_TXHI as
2 /* $Header: invcmtb.pls 120.1.12020000.2 2012/07/09 08:23:12 asugandh ship $ */
3
4 /*---------------------------- PRIVATE VARIABLES ----------------------------*/
5 g_count NUMBER := 0;
6
7 /*--------------------------- PRIVATE ROUTINES ------------------------------*/
8
9 /*--------------------------- MTL_DEMAND ------------------------------------*/
10
11 procedure INV_MUT (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
12
13 CURSOR C1 IS
14 SELECT NULL
15 FROM MTL_UNIT_TRANSACTIONS
16 WHERE customer_id in (select racm.duplicate_id
17 from ra_customer_merges racm
18 where racm.process_flag = 'N'
19 and racm.request_id = req_id
20 and racm.set_number = set_num)
21 FOR UPDATE NOWAIT;
22 mut_cust_flag NUMBER; /*Bug#5574255.*/
23
24 BEGIN
25 arp_message.set_line( 'INVP_CMERGE_TXHI.INV_MUT()+' );
26 /*Bug#5574255. Modified code in such a way that LOCKING of rows of
27 MTL_UNIT_TRANSACTIONS or updation of customer_id in MTL_UNIT_TRANSACTIONS
28 happens only if there is at least one row in MTL_UNIT_TRANSACTIONS with
29 non-zero and non-null customer_id.*/
30
31 /*Bug 13795366 Added the index_ffs after SELECT to increase the performace significantly for
32 customer merge*/
33 BEGIN
34 SELECT 1
35 INTO mut_cust_flag
36 FROM dual
37 WHERE EXISTS ( SELECT /*+ index_ffs(MTL_UNIT_TRANSACTIONS MTL_UNIT_TRANSACTIONS_N3)*/ 1
38 FROM mtl_unit_transactions
39 WHERE customer_id <> 0);
40 EXCEPTION WHEN OTHERS THEN
41 mut_cust_flag := 0;
42 END;
43
44 /*-----------------------+
45 | MTL_UNIT_TRANSACTIONS |
46 +-----------------------*/
47 /* try to lock the table first */
48 IF ( mut_cust_flag <> 0) THEN
49 IF (process_mode = 'LOCK') then
50 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
51 arp_message.set_token( 'TABLE_NAME', 'MTL_UNIT_TRANSACTIONS', FALSE );
52
53 OPEN C1;
54 CLOSE C1;
55
56 ELSE
57
58 /* customer level update */
59
60 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
61 arp_message.set_token('TABLE_NAME', 'MTL_UNIT_TRANSACTIONS', FALSE);
62 arp_message.set_line('customer level : customer_id');
63
64 UPDATE MTL_UNIT_TRANSACTIONS yt
65 set customer_id = (select distinct racm.customer_id
66 from ra_customer_merges racm
67 where yt.customer_id =
68 racm.duplicate_id
69 and racm.process_flag = 'N'
70 and racm.request_id = req_id
71 and racm.set_number = set_num),
72 last_update_date = sysdate,
73 last_updated_by = arp_standard.profile.user_id,
74 last_update_login = arp_standard.profile.last_update_login
75 where customer_id in (select racm.duplicate_id
76 from ra_customer_merges racm
77 where racm.process_flag = 'N'
78 and racm.request_id = req_id
79 and racm.set_number = set_num);
80
81 g_count := sql%rowcount;
82
83 /* Number of rows updates */
84 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
85 arp_message.set_token('NUM_ROWS', to_char(g_count));
86
87 END IF;
88 END IF;
89
90 arp_message.set_line( 'INVP_CMERGE_TXHI.INV_MUT()-' );
91
92
93 EXCEPTION
94 when others then
95 arp_message.set_error( 'INVP_CMERGE_TXHI.INV_MUT');
96 raise;
97
98 END;
99 /*------------------------MTL_MOVEMENT_STATISTICS---------------------------*/
100
101 /*
102 PROCEDURE INV_MMS (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) IS
103
104 CURSOR c1 IS
105 SELECT movement_id
106 FROM mtl_movement_statistics
107 WHERE ship_to_customer_id IN (
108 SELECT rcm.duplicate_id
109 FROM ra_customer_merges rcm
110 WHERE rcm.process_flag = 'N'
111 AND rcm.request_id = req_id
112 AND rcm.set_number = set_num)
113 OR bill_to_customer_id IN (
114 SELECT rcm.duplicate_id
115 FROM ra_customer_merges rcm
116 WHERE rcm.process_flag = 'N'
117 AND rcm.request_id = req_id
118 AND rcm.set_number = set_num)
119 FOR UPDATE NOWAIT;
120
121 CURSOR c2 IS
122 SELECT movement_id
123 FROM mtl_movement_statistics
124 WHERE ship_to_site_use_id IN (
125 SELECT rcm.duplicate_site_id
126 FROM ra_customer_merges rcm
127 WHERE rcm.process_flag = 'N'
128 AND rcm.request_id = req_id
129 AND rcm.set_number = set_num)
130 OR bill_to_site_use_id IN (
131 SELECT rcm.duplicate_site_id
132 FROM ra_customer_merges rcm
133 WHERE rcm.process_flag = 'N'
134 AND rcm.request_id = req_id
135 AND rcm.set_number = set_num)
136 FOR UPDATE NOWAIT;
137
138 BEGIN
139
140 arp_message.set_line( 'INV_CMERGE_TXHI.INV_MMS()+' );
141
142
143 +--------------------------------------------------+
144 | MTL_MOVEMENT_STATISTICS |
145 +--------------------------------------------------
146
147
148 IF (process_mode = 'LOCK' ) THEN
149
150 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE' );
151 arp_message.set_token( 'TABLE_NAME', 'MTL_MOVEMENT_STATISTICS',FALSE );
152
153
154 -- ** Lock For Customer Level Update **
155 open c1;
156 close c1;
157
158 -- ** Lock For Site Level Update **
159 open c2;
160 close c2;
161
162 ELSE
163
164 -- ** CUSTOMER LEVEL UPDATE **
165
166 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
167 arp_message.set_token('TABLE_NAME', 'MTL_MOVEMENT_STATISTICS', FALSE);
168 arp_message.set_line('customer level : customer_id');
169
170 UPDATE mtl_movement_statistics mtl
171 SET (ship_to_customer_id,
172 bill_to_customer_id) = (
173 SELECT distinct
174 decode(mtl.ship_to_customer_id, rcm.duplicate_id,
175 rcm.customer_id, mtl.ship_to_customer_id),
176 decode(mtl.bill_to_customer_id, rcm.duplicate_id,
177 rcm.customer_id, mtl.bill_to_customer_id)
178 FROM ra_customer_merges rcm
179 WHERE mtl.ship_to_customer_id = rcm.duplicate_id
180 OR mtl.bill_to_customer_id = rcm.duplicate_id),
181 last_update_date = sysdate,
182 last_updated_by = arp_standard.profile.user_id,
183 last_update_login = arp_standard.profile.last_update_login
184 WHERE ship_to_customer_id IN (
185 SELECT rcm.duplicate_id
186 FROM ra_customer_merges rcm
187 WHERE rcm.process_flag = 'N'
188 AND rcm.request_id = req_id
189 AND rcm.set_number = set_num)
190 OR bill_to_customer_id IN (
191 SELECT rcm.duplicate_id
192 FROM ra_customer_merges rcm
193 WHERE rcm.process_flag = 'N'
194 AND rcm.request_id = req_id
195 AND rcm.set_number = set_num);
196
197 g_count := sql%rowcount;
198 -- Number of rows updates
199 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
200 arp_message.set_token('NUM_ROWS', to_char(g_count));
201
202
203 -- ** SITE LEVEL UPDATE **
204 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
205 arp_message.set_token('TABLE_NAME', 'MTL_MOVEMENT_STATISTICS', FALSE);
206 arp_message.set_line('customer level : customer_site_id');
207
208 UPDATE mtl_movement_statistics mtl
209 SET (ship_to_site_use_id,
210 bill_to_site_use_id) = (
211 SELECT distinct
212 decode(mtl.ship_to_site_use_id, rcm.duplicate_site_id,
213 rcm.customer_site_id, mtl.ship_to_site_use_id),
214 decode(mtl.bill_to_site_use_id, rcm.duplicate_site_id,
215 rcm.customer_site_id, mtl.bill_to_site_use_id)
216 FROM ra_customer_merges rcm
217 WHERE mtl.ship_to_site_use_id = rcm.duplicate_site_id
218 OR mtl.bill_to_site_use_id = rcm.duplicate_site_id),
219 last_update_date = sysdate,
220 last_updated_by = arp_standard.profile.user_id,
221 last_update_login = arp_standard.profile.last_update_login
222 WHERE ship_to_site_use_id IN (
223 SELECT rcm.duplicate_site_id
224 FROM ra_customer_merges rcm
225 WHERE rcm.process_flag = 'N'
226 AND rcm.request_id = req_id
227 AND rcm.set_number = set_num)
228 OR bill_to_site_use_id IN (
229 SELECT rcm.duplicate_site_id
230 FROM ra_customer_merges rcm
231 WHERE rcm.process_flag = 'N'
232 AND rcm.request_id = req_id
233 AND rcm.set_number = set_num);
234
235
236 g_count := sql%rowcount;
237
238 -- Number of rows updates
239 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
240 arp_message.set_token('NUM_ROWS', to_char(g_count));
241
242 END IF;
243
244 arp_message.set_line( 'INVP_CMERGE_TXHI.INV_MMS()-' );
245
246 EXCEPTION
247 when others then
248 arp_message.set_error( 'INVP_CMERGE_TXHI.INV_MMS');
249 raise;
250
251 END;
252 */
253
254 /*---------------------------- PUBLIC ROUTINES ------------------------------*/
255
256 PROCEDURE MERGE (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
257 BEGIN
258
259 arp_message.set_line( 'INVP_CMERGE_TXHI.MERGE()+' );
260
261 INV_MUT( req_id, set_num, process_mode );
262
263 -- call INV_MMS to do customer merge on mtl_movement_statistics table
264 -- Bug 2423619
265 -- No Movement stat conversion required
266 -- removing the call
267 --- INV_MMS ( req_id, set_num, process_mode );
268 --
269
270 arp_message.set_line ( 'INVP_CMERGE_TXHI.MERGE()-' );
271
272 END MERGE;
273 end INVP_CMERGE_TXHI;