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