[Home] [Help]
PACKAGE BODY: APPS.OEP_CMERGE_OEORD
Source
1 PACKAGE BODY OEP_CMERGE_OEORD AS
2 /* $Header: oeordpb.pls 115.1 99/07/26 11:08:35 porting shi $ */
3
4
5 /*---------------------------- PRIVATE VARIABLES ----------------------------*/
6 g_count NUMBER := 0;
7
8
9 /*--------------------------- PRIVATE ROUTINES ------------------------------*/
10
11
12 PROCEDURE oe_sh (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
13
14 CURSOR c1 is
15 select header_id
16 from so_headers
17 where ship_to_site_use_id in (select m.duplicate_site_id
18 from ra_customer_merges m
19 where m.process_flag = 'N'
20 and m.request_id = req_id
21 and m.set_number = set_num)
22 for update nowait;
23
24 CURSOR c2 is
25 select header_id
26 from so_headers
27 where invoice_to_site_use_id in (select m.duplicate_site_id
28 from ra_customer_merges m
29 where m.process_flag = 'N'
30 and m.request_id = req_id
31 and m.set_number = set_num)
32 for update nowait;
33
34 CURSOR c3 is
35 select header_id
36 from so_headers
37 where customer_id in (select m.duplicate_id
38 from ra_customer_merges m
39 where m.process_flag = 'N'
40 and m.request_id = req_id
41 and m.set_number = set_num)
42 for update nowait;
43
44 BEGIN
45
46 arp_message.set_line( 'OEP_CMERGE_OEORD.OE_SH()+' );
47
48 /*-----------------------------+
49 | SO_HEADERS |
50 +-----------------------------*/
51 /* both customer and site level */
52
53 IF( process_mode = 'LOCK' ) THEN
54
55 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
56 arp_message.set_token( 'TABLE_NAME', 'SO_HEADERS', FALSE );
57
58 open c1;
59 close c1;
60
61 open c2;
62 close c2;
63
64 open c3;
65 close c3;
66
67 ELSE
68
69
70 /* site level update */
71 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
72 arp_message.set_token( 'TABLE_NAME', 'SO_HEADERS', FALSE );
73
74 UPDATE SO_HEADERS a
75 set (ship_to_site_use_id) = (select distinct m.customer_site_id
76 from ra_customer_merges m
77 where a.ship_to_site_use_id =
78 m.duplicate_site_id
79 and m.request_id = req_id
80 and m.process_flag = 'N'
81 and m.set_number = set_num),
82 last_update_date = sysdate,
83 last_updated_by = arp_standard.profile.user_id,
84 last_update_login = arp_standard.profile.last_update_login,
85 request_id = req_id,
86 program_application_id =arp_standard.profile.program_application_id,
87 program_id = arp_standard.profile.program_id,
88 program_update_date = sysdate
89 where ship_to_site_use_id in (select m.duplicate_site_id
90 from ra_customer_merges m
91 where m.process_flag = 'N'
92 and m.request_id = req_id
93 and m.set_number = set_num);
94
95 g_count := sql%rowcount;
96
97 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
98 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
99
100 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
101 arp_message.set_token( 'TABLE_NAME', 'SO_HEADERS', FALSE );
102
103 UPDATE SO_HEADERS a
104 set (invoice_to_site_use_id) = (select distinct m.customer_site_id
105 from ra_customer_merges m
106 where a.invoice_to_site_use_id =
107 m.duplicate_site_id
108 and m.request_id = req_id
109 and m.process_flag = 'N'
110 and m.set_number = set_num),
111 last_update_date = sysdate,
112 last_updated_by = arp_standard.profile.user_id,
113 last_update_login = arp_standard.profile.last_update_login,
114 request_id = req_id,
115 program_application_id =arp_standard.profile.program_application_id,
116 program_id = arp_standard.profile.program_id,
117 program_update_date = sysdate
118 where invoice_to_site_use_id in (select m.duplicate_site_id
119 from ra_customer_merges m
120 where m.process_flag = 'N'
121 and m.request_id = req_id
122 and m.set_number = set_num);
123
124 g_count := sql%rowcount;
125
126 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
127 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
128
129 /* customer level update */
130 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
131 arp_message.set_token( 'TABLE_NAME', 'SO_HEADERS', FALSE );
132
133 UPDATE SO_HEADERS a
134 set customer_id = (select distinct m.customer_id
135 from ra_customer_merges m
136 where a.customer_id =
137 m.duplicate_id
138 and m.process_flag = 'N'
139 and m.request_id = req_id
140 and m.set_number = set_num),
141 last_update_date = sysdate,
142 last_updated_by = arp_standard.profile.user_id,
143 last_update_login = arp_standard.profile.last_update_login,
144 request_id = req_id,
145 program_application_id =arp_standard.profile.program_application_id,
146 program_id = arp_standard.profile.program_id,
147 program_update_date = sysdate
148 where customer_id in (select m.duplicate_id
149 from ra_customer_merges m
150 where m.process_flag = 'N'
151 and m.request_id = req_id
152 and m.set_number = set_num);
153
154 g_count := sql%rowcount;
155
156 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
157 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
158
159 END IF;
160
161 arp_message.set_line( 'OEP_CMERGE_OEORD.OE_SH()-' );
162
163
164 EXCEPTION
165 when others then
166 arp_message.set_error( 'OEP_CMERGE_OEORD.OE_SH');
167 raise;
168
169 END;
170
171
172
173 PROCEDURE oe_sl (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
174
175 CURSOR c1 is
176 select line_id
177 from so_lines
178 where ship_to_site_use_id in (select m.duplicate_site_id
179 from ra_customer_merges m
180 where m.process_flag = 'N'
181 and m.request_id = req_id
182 and m.set_number = set_num)
183 for update nowait;
184
185 BEGIN
186
187 arp_message.set_line( 'OEP_CMERGE_OEORD.OE_SL()+' );
188
189 /*-----------------------------+
190 | SO_LINES |
191 +-----------------------------*/
192 /* both customer and site level */
193
194 IF( process_mode = 'LOCK' ) THEN
195
196 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
197 arp_message.set_token( 'TABLE_NAME', 'SO_LINES', FALSE );
198
199 open c1;
200 close c1;
201
202 ELSE
203
204
205 /* site level update */
206 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
207 arp_message.set_token( 'TABLE_NAME', 'SO_LINES', FALSE );
208
209 UPDATE SO_LINES a
210 set (ship_to_site_use_id) = (select distinct m.customer_site_id
211 from ra_customer_merges m
212 where a.ship_to_site_use_id =
213 m.duplicate_site_id
214 and m.request_id = req_id
215 and m.process_flag = 'N'
216 and m.set_number = set_num),
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 request_id = req_id,
221 program_application_id =arp_standard.profile.program_application_id,
222 program_id = arp_standard.profile.program_id,
223 program_update_date = sysdate
224 where ship_to_site_use_id in (select m.duplicate_site_id
225 from ra_customer_merges m
226 where m.process_flag = 'N'
227 and m.request_id = req_id
228 and m.set_number = set_num);
229
230 g_count := sql%rowcount;
231
232 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
233 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
234
235 END IF;
236
237 arp_message.set_line( 'OEP_CMERGE_OEORD.OE_SL()-' );
238
239
240 EXCEPTION
241 when others then
242 arp_message.set_error( 'OEP_CMERGE_OEORD.OE_SL');
243 raise;
244
245 END;
246
247 PROCEDURE oe_sd (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
248
249 CURSOR c1 is
250 select line_service_detail_id
251 from so_line_service_details
252 where installation_site_use_id in (select m.duplicate_site_id
253 from ra_customer_merges m
254 where m.process_flag = 'N'
255 and m.request_id = req_id
256 and m.set_number = set_num)
257 for update nowait;
258
259 BEGIN
260
261 arp_message.set_line( 'OEP_CMERGE_OEORD.OE_SD()+' );
262
263 /*-----------------------------+
264 | SO_LINE_SERVICE_DETAILS |
265 +-----------------------------*/
266 /* both customer and site level */
267
268 IF( process_mode = 'LOCK' ) THEN
269
270 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
271 arp_message.set_token( 'TABLE_NAME', 'SO_LINE_SERVICE_DETAILS', FALSE );
272
273 open c1;
274 close c1;
275
276 ELSE
277
278
279 /* site level update */
280 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
281 arp_message.set_token( 'TABLE_NAME', 'SO_LINE_SERVICE_DETAILS', FALSE );
282
283 UPDATE SO_LINE_SERVICE_DETAILS a
284 set (installation_site_use_id) = (select distinct m.customer_site_id
285 from ra_customer_merges m
286 where a.installation_site_use_id =
287 m.duplicate_site_id
288 and m.request_id = req_id
289 and m.process_flag = 'N'
290 and m.set_number = set_num),
291 last_update_date = sysdate,
292 last_updated_by = arp_standard.profile.user_id,
293 last_update_login = arp_standard.profile.last_update_login
294 where installation_site_use_id in (select m.duplicate_site_id
295 from ra_customer_merges m
296 where m.process_flag = 'N'
297 and m.request_id = req_id
298 and m.set_number = set_num);
299
300 g_count := sql%rowcount;
301
302 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
303 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
304
305 END IF;
306
307 arp_message.set_line( 'OEP_CMERGE_OEORD.OE_SD()-' );
308
309
310 EXCEPTION
311 when others then
312 arp_message.set_error( 'OEP_CMERGE_OEORD.OE_SD');
313 raise;
314
315 END;
316
317
318 PROCEDURE oe_oa (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
319
320 CURSOR c1 is
321 select order_approval_id
322 from so_order_approvals
323 where customer_id in (select m.duplicate_id
324 from ra_customer_merges m
325 where m.process_flag = 'N'
326 and m.request_id = req_id
327 and m.set_number = set_num)
328 for update nowait;
329
330 BEGIN
331
332 arp_message.set_line( 'OEP_CMERGE_OEORD.OE_OA()+' );
333
334 /*-----------------------------+
335 | SO_ORDER_APPROVALS |
336 +-----------------------------*/
337 /* both customer and site level */
338
339 IF( process_mode = 'LOCK' ) THEN
340
341 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
342 arp_message.set_token( 'TABLE_NAME', 'SO_ORDER_APPROVALS', FALSE );
343
344 open c1;
345 close c1;
346
347 ELSE
348
349 /* customer level update */
350 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
351 arp_message.set_token( 'TABLE_NAME', 'SO_ORDER_APPROVALS', FALSE );
352
353 UPDATE SO_ORDER_APPROVALS a
354 set customer_id = (select distinct m.customer_id
355 from ra_customer_merges m
356 where a.customer_id =
357 m.duplicate_id
358 and m.process_flag = 'N'
359 and m.request_id = req_id
360 and m.set_number = set_num),
361 last_update_date = sysdate,
362 last_updated_by = arp_standard.profile.user_id,
363 last_update_login = arp_standard.profile.last_update_login
364 where customer_id in (select m.duplicate_id
365 from ra_customer_merges m
366 where m.process_flag = 'N'
367 and m.request_id = req_id
368 and m.set_number = set_num);
369
370 g_count := sql%rowcount;
371
372 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
373 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
374
375 END IF;
376
377 arp_message.set_line( 'OEP_CMERGE_OEORD.OE_OA()-' );
378
379
380 EXCEPTION
381 when others then
382 arp_message.set_error( 'OEP_CMERGE_OEORD.OE_OA');
383 raise;
384
385 END;
386
387
388 PROCEDURE oe_vr (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
389
390 CURSOR c1 is
391 select standard_value_rule_id
392 from so_standard_value_rules
393 where attribute_value in (select to_char(m.duplicate_site_id)
394 from ra_customer_merges m
395 where m.process_flag = 'N'
396 and m.request_id = req_id
397 and m.set_number = set_num)
398 and standard_value_source_id = 1
399 and attribute_id = 10026
400 for update nowait;
401
402 CURSOR c2 is
403 select standard_value_rule_id
404 from so_standard_value_rules
405 where attribute_value in (select to_char(m.duplicate_site_id)
406 from ra_customer_merges m
407 where m.process_flag = 'N'
408 and m.request_id = req_id
409 and m.set_number = set_num)
410 and standard_value_source_id = 1
411 and attribute_id = 10028
412 for update nowait;
413
414
415 BEGIN
416
417 arp_message.set_line( 'OEP_CMERGE_OEORD.OE_VR()+' );
418
419
420
421 /*-----------------------------+
422 | SO_STANDARD_VALUE_RULES |
423 +-----------------------------*/
424 /* both customer and site level */
425
426 IF( process_mode = 'LOCK' ) THEN
427
428 arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
429 arp_message.set_token( 'TABLE_NAME', 'SO_STANDARD_VALUE_RULES', FALSE );
430
431 open c1;
432 close c1;
433
434
435 open c2;
436 close c2;
437
438 ELSE
439
440
441 /* site level update */
442 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
443 arp_message.set_token( 'TABLE_NAME', 'SO_STANDARD_VALUE_RULES', FALSE );
444
445
446
447 UPDATE SO_STANDARD_VALUE_RULES a
448 set attribute_value = (select distinct to_char(m.customer_site_id)
449 from ra_customer_merges m
450 where a.attribute_value =
451 to_char(m.duplicate_site_id)
452 and m.request_id = req_id
453 and m.process_flag = 'N'
454 and m.set_number = set_num),
455 last_update_date = sysdate,
456 last_updated_by = arp_standard.profile.user_id,
457 last_update_login = arp_standard.profile.last_update_login
458 where attribute_value in (select to_char(m.duplicate_site_id)
459 from ra_customer_merges m
460 where m.process_flag = 'N'
461 and m.request_id = req_id
462 and m.set_number = set_num)
463 and standard_value_source_id = 1
464 and attribute_id = 10026;
465
466 g_count := sql%rowcount;
467
468 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
469 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
470
471
472 /* site level update */
473 arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
474 arp_message.set_token( 'TABLE_NAME', 'SO_STANDARD_VALUE_RULES', FALSE );
475
476
477
478 UPDATE SO_STANDARD_VALUE_RULES a
479 set attribute_value = (select distinct to_char(m.customer_site_id)
480 from ra_customer_merges m
481 where a.attribute_value =
482 to_char(m.duplicate_site_id)
483 and m.request_id = req_id
484 and m.process_flag = 'N'
485 and m.set_number = set_num),
486 last_update_date = sysdate,
487 last_updated_by = arp_standard.profile.user_id,
488 last_update_login = arp_standard.profile.last_update_login
489 where attribute_value in (select to_char(m.duplicate_site_id)
490 from ra_customer_merges m
491 where m.process_flag = 'N'
492 and m.request_id = req_id
493 and m.set_number = set_num)
494 and standard_value_source_id = 1
495 and attribute_id = 10028;
496
497 g_count := sql%rowcount;
498
499 arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
500 arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
501
502
503 END IF;
504
505 arp_message.set_line( 'OEP_CMERGE_OEORD.OE_VR()-' );
506
507
508 EXCEPTION
509 when others then
510 arp_message.set_error( 'OEP_CMERGE_OEORD.OE_VR');
511 raise;
512
513 END;
514
515
516
517 /*---------------------------- PUBLIC ROUTINES ------------------------------*/
518
519
520 PROCEDURE MERGE (REQ_ID NUMBER, SET_NUM NUMBER, PROCESS_MODE VARCHAR2) IS
521 BEGIN
522
523 arp_message.set_line( 'OEP_CMERGE_OEORD.MERGE()+' );
524
525 oe_sh( req_id, set_num, process_mode );
526 oe_sl( req_id, set_num, process_mode );
527 oe_oa( req_id, set_num, process_mode );
528 oe_sd( req_id, set_num, process_mode );
529 oe_vr( req_id, set_num, process_mode );
530
531 arp_message.set_line( 'OEP_CMERGE_OEORD.MERGE()-' );
532
533 EXCEPTION
534 when others then
535 raise;
536
537 END MERGE;
538 END OEP_CMERGE_OEORD;