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