[Home] [Help]
PACKAGE BODY: APPS.OEP_CMERGE_OEPIC
Source
1 PACKAGE BODY OEP_CMERGE_OEPIC AS
2 /* $Header: oepicpb.pls 115.1 99/07/16 08:27:54 porting shi $ */
3
4
5 /*---------------------------- PRIVATE VARIABLES ----------------------------*/
6 g_count NUMBER := 0;
7
8
9 /*--------------------------- PRIVATE ROUTINES ------------------------------*/
10
11 PROCEDURE oe_pr (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
12
13 CURSOR c1 is
14 select picking_rule
15 from so_picking_rules
16 where site_use_id in (select m.duplicate_site_id
17 from ra_customer_merges m
21 for update nowait;
18 where m.process_flag = 'N'
19 and m.request_id = req_id
20 and m.set_number = set_num)
22
23 CURSOR c2 is
24 select picking_rule
25 from so_picking_rules
26 where customer_id in (select m.duplicate_id
27 from ra_customer_merges m
28 where m.process_flag = 'N'
29 and m.request_id = req_id
30 and m.set_number = set_num)
31 for update nowait;
32
33 BEGIN
34
35 arp_message.set_line( 'OEP_CMERGE_OEPIC.OE_PR()+' );
36
37 /*-----------------------------+
38 | SO_PICKING_RULES |
39 +-----------------------------*/
40 /* both customer and site level */
41
42 IF( process_mode = 'LOCK' ) THEN
43
44 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
45 arp_message.set_token( 'TABLE_NAME', 'SO_PICKING_RULES', FALSE );
46
47 open c1;
48 close c1;
49
50 open c2;
51 close c2;
52
53 ELSE
54
55
56 /* site level update */
57 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
58 arp_message.set_token( 'TABLE_NAME', 'SO_PICKING_RULES', FALSE );
59
60 UPDATE SO_PICKING_RULES a
61 set (site_use_id) = (select distinct m.customer_site_id
62 from ra_customer_merges m
63 where a.site_use_id =
64 m.duplicate_site_id
65 and m.request_id = req_id
66 and m.process_flag = 'N'
67 and m.set_number = set_num),
68 last_update_date = sysdate,
69 last_updated_by = arp_standard.profile.user_id,
70 last_update_login = arp_standard.profile.last_update_login
71 where site_use_id in (select m.duplicate_site_id
72 from ra_customer_merges m
73 where m.process_flag = 'N'
74 and m.request_id = req_id
75 and m.set_number = set_num);
76
77 g_count := sql%rowcount;
78
79 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
80 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
81
82 /* customer level update */
83 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
84 arp_message.set_token( 'TABLE_NAME', 'SO_PICKING_RULES', FALSE );
85
86 UPDATE SO_PICKING_RULES a
87 set customer_id = (select distinct m.customer_id
88 from ra_customer_merges m
89 where a.customer_id =
90 m.duplicate_id
91 and m.process_flag = 'N'
92 and m.request_id = req_id
93 and m.set_number = set_num),
94 last_update_date = sysdate,
95 last_updated_by = arp_standard.profile.user_id,
96 last_update_login = arp_standard.profile.last_update_login
97 where customer_id in (select m.duplicate_id
98 from ra_customer_merges m
99 where m.process_flag = 'N'
100 and m.request_id = req_id
101 and m.set_number = set_num);
102
103 g_count := sql%rowcount;
104
105 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
106 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
107
108 END IF;
109
110 arp_message.set_line( 'OEP_CMERGE_OEPIC.OE_PR()-' );
111
112
113 EXCEPTION
114 when others then
115 arp_message.set_error( 'OEP_CMERGE_OEPIC.OE_PR');
116 raise;
117
118 END;
119
120
121 PROCEDURE oe_pb (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
122
123 CURSOR c1 is
124 select batch_id
125 from so_picking_batches
126 where site_use_id in (select m.duplicate_site_id
127 from ra_customer_merges m
128 where m.process_flag = 'N'
129 and m.request_id = req_id
130 and m.set_number = set_num)
131 for update nowait;
132
133 CURSOR c2 is
134 select batch_id
135 from so_picking_batches
136 where customer_id in (select m.duplicate_id
137 from ra_customer_merges m
138 where m.process_flag = 'N'
139 and m.request_id = req_id
140 and m.set_number = set_num)
141 for update nowait;
142
143 BEGIN
144
145 arp_message.set_line( 'OEP_CMERGE_OEPIC.OE_PB()+' );
146
147 /*-----------------------------+
148 | SO_PICKING_BATCHES |
149 +-----------------------------*/
150 /* both customer and site level */
151
152 IF( process_mode = 'LOCK' ) THEN
153
154 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
155 arp_message.set_token( 'TABLE_NAME', 'SO_PICKING_BATCHES', FALSE );
156
157 open c1;
158 close c1;
159
160 open c2;
161 close c2;
162
163 ELSE
164
165
166 /* site level update */
167 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
168 arp_message.set_token( 'TABLE_NAME', 'SO_PICKING_BATCHES', FALSE );
169
170 UPDATE SO_PICKING_BATCHES a
171 set (site_use_id) = (select distinct m.customer_site_id
172 from ra_customer_merges m
176 and m.process_flag = 'N'
173 where a.site_use_id =
174 m.duplicate_site_id
175 and m.request_id = req_id
177 and m.set_number = set_num),
178 last_update_date = sysdate,
179 last_updated_by = arp_standard.profile.user_id,
180 last_update_login = arp_standard.profile.last_update_login
181 where site_use_id in (select m.duplicate_site_id
182 from ra_customer_merges m
183 where m.process_flag = 'N'
184 and m.request_id = req_id
185 and m.set_number = set_num);
186
187 g_count := sql%rowcount;
188
189 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
190 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
191
192 /* customer level update */
193 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
194 arp_message.set_token( 'TABLE_NAME', 'SO_PICKING_BATCHES', FALSE );
195
196 UPDATE SO_PICKING_BATCHES a
197 set customer_id = (select distinct m.customer_id
198 from ra_customer_merges m
199 where a.customer_id =
200 m.duplicate_id
201 and m.process_flag = 'N'
202 and m.request_id = req_id
203 and m.set_number = set_num),
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 where customer_id in (select m.duplicate_id
208 from ra_customer_merges m
209 where m.process_flag = 'N'
210 and m.request_id = req_id
211 and m.set_number = set_num);
212
213 g_count := sql%rowcount;
214
215 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
216 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
217
218 END IF;
219
220 arp_message.set_line( 'OEP_CMERGE_OEPIC.OE_PB()-' );
221
222
223 EXCEPTION
224 when others then
225 arp_message.set_error( 'OEP_CMERGE_OEPIC.OE_PB');
226 raise;
227
228 END;
229
230
231
232 PROCEDURE oe_ph (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
233
234 CURSOR c1 is
235 select picking_header_id
236 from so_picking_headers
237 where ship_to_site_use_id in (select m.duplicate_site_id
238 from ra_customer_merges m
239 where m.process_flag = 'N'
240 and m.request_id = req_id
241 and m.set_number = set_num)
242 for update nowait;
243
244 BEGIN
245
246 arp_message.set_line( 'OEP_CMERGE_OEPIC.OE_PH()+' );
247
248 /*-----------------------------+
249 | SO_PICKING_HEADERS |
250 +-----------------------------*/
251 /* both customer and site level */
252
253 IF( process_mode = 'LOCK' ) THEN
254
255 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
256 arp_message.set_token( 'TABLE_NAME', 'SO_PICKING_HEADERS', FALSE );
257
258 open c1;
259 close c1;
260
261 ELSE
262
263
264 /* site level update */
265 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
266 arp_message.set_token( 'TABLE_NAME', 'SO_PICKING_HEADERS', FALSE );
267
268 UPDATE SO_PICKING_HEADERS a
269 set (ship_to_site_use_id) = (select distinct m.customer_site_id
270 from ra_customer_merges m
271 where a.ship_to_site_use_id =
272 m.duplicate_site_id
273 and m.request_id = req_id
274 and m.process_flag = 'N'
275 and m.set_number = set_num),
276 last_update_date = sysdate,
277 last_updated_by = arp_standard.profile.user_id,
278 last_update_login = arp_standard.profile.last_update_login
279 where ship_to_site_use_id in (select m.duplicate_site_id
280 from ra_customer_merges m
281 where m.process_flag = 'N'
282 and m.request_id = req_id
283 and m.set_number = set_num);
284
285 g_count := sql%rowcount;
286
287 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
288 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
289
290 END IF;
291
292 arp_message.set_line( 'OEP_CMERGE_OEPIC.OE_PH()-' );
293
294
295 EXCEPTION
296 when others then
297 arp_message.set_error( 'OEP_CMERGE_OEPIC.OE_PH');
298 raise;
299
300 END;
301
302
303 PROCEDURE oe_pl (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
304
305 CURSOR c1 is
306 select picking_line_id
307 from so_picking_lines
308 where ship_to_site_use_id in (select m.duplicate_site_id
309 from ra_customer_merges m
310 where m.process_flag = 'N'
311 and m.request_id = req_id
312 and m.set_number = set_num)
313 for update nowait;
314
315 BEGIN
316
317 arp_message.set_line( 'OEP_CMERGE_OEPIC.OE_PL()+' );
318
319 /*-----------------------------+
320 | SO_PICKING_LINES |
321 +-----------------------------*/
322 /* both customer and site level */
323
324 IF( process_mode = 'LOCK' ) THEN
325
326 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
327 arp_message.set_token( 'TABLE_NAME', 'SO_PICKING_LINES', FALSE );
328
329 open c1;
330 close c1;
331
332 ELSE
333
334
335 /* site level update */
336 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
337 arp_message.set_token( 'TABLE_NAME', 'SO_PICKING_LINES', FALSE );
338
339 UPDATE SO_PICKING_LINES a
340 set (ship_to_site_use_id) = (select distinct m.customer_site_id
341 from ra_customer_merges m
342 where a.ship_to_site_use_id =
343 m.duplicate_site_id
344 and m.request_id = req_id
345 and m.process_flag = 'N'
346 and m.set_number = set_num),
347 last_update_date = sysdate,
348 last_updated_by = arp_standard.profile.user_id,
349 last_update_login = arp_standard.profile.last_update_login
350 where ship_to_site_use_id in (select m.duplicate_site_id
351 from ra_customer_merges m
352 where m.process_flag = 'N'
353 and m.request_id = req_id
354 and m.set_number = set_num);
355
356 g_count := sql%rowcount;
357
358 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
359 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
360
361 END IF;
362
363 arp_message.set_line( 'OEP_CMERGE_OEPIC.OE_PL()-' );
364
365
366 EXCEPTION
367 when others then
368 arp_message.set_error( 'OEP_CMERGE_OEPIC.OE_PL');
369 raise;
370
371 END;
372
373
374 /*---------------------------- PUBLIC ROUTINES ------------------------------*/
375
376
377 PROCEDURE MERGE (REQ_ID NUMBER, SET_NUM NUMBER, PROCESS_MODE VARCHAR2) IS
378 BEGIN
379
380 arp_message.set_line( 'OEP_CMERGE_OEPIC.MERGE()+' );
381
382 oe_pr( req_id, set_num, process_mode );
383 oe_pb( req_id, set_num, process_mode );
384 oe_ph( req_id, set_num, process_mode );
385 oe_pl( req_id, set_num, process_mode );
386
387 arp_message.set_line( 'OEP_CMERGE_OEPIC.MERGE()-' );
388
389 EXCEPTION
390 when others then
391 raise;
392
393 END MERGE;
394 END OEP_CMERGE_OEPIC;