[Home] [Help]
PACKAGE BODY: APPS.QP_CUST_MRG_DATA_CLEANUP
Source
1 PACKAGE BODY QP_CUST_MRG_DATA_CLEANUP AS
2 /* $Header: QPXCMDCB.pls 120.0 2005/06/02 00:32:10 appldev noship $ */
3
4 g_count NUMBER := 0;
5
6 PROCEDURE Agreement_Merge(req_id NUMBER, set_num NUMBER, process_mode VARCHAR2)
7 IS
8
9 CURSOR c1
10 IS
11 select agreement_id
12 from oe_agreements_b
13 where invoice_to_org_id in (select m.duplicate_site_id
14 from ra_customer_merges m
15 where m.process_flag = 'Y'
16 and m.request_id = req_id
17 and m.set_number = set_num)
18 for update nowait;
19
20 CURSOR c2
21 IS
22 select agreement_id
23 from oe_agreements_b
24 where sold_to_org_id in (select m.duplicate_id
25 from ra_customer_merges m
26 where m.process_flag = 'Y'
27 and m.request_id = req_id
28 and m.set_number = set_num)
29 for update nowait;
30
31 BEGIN
32
33 oe_debug_pub.add('Begin QP_CUST_MRG_DATA_CLEANUP.Agreement_Merge()');
34
35 /**************************************************
36 Merge Agreements at both site and customer Level
37 **************************************************/
38
39 IF (process_mode = 'LOCK') THEN
40
41 oe_debug_pub.add('Locking Table OE_AGREEMENTS_B');
42
43 open c1;
44 close c1;
45
46 open c2;
47 close c2;
48
49 ELSE
50
51 /** site level update **/
52
53 oe_debug_pub.add('Updating Table OE_AGREEMENTS_B for Customer Site Data');
54
55 UPDATE oe_agreements_b a
56 SET (invoice_to_org_id) = (select distinct m.customer_site_id
57 from ra_customer_merges m
58 where a.invoice_to_org_id = m.duplicate_site_id
59 and m.request_id = req_id
60 and m.process_flag = 'Y'
61 and m.set_number = set_num),
62 last_update_date = sysdate,
63 last_updated_by = -1,
64 last_update_login = -1
65
66 WHERE exists (select 'X'
67 from ra_customer_merges m
68 where a.invoice_to_org_id = m.duplicate_site_id
69 and m.process_flag = 'Y'
70 and m.request_id = req_id
71 and m.set_number = set_num);
72
73 g_count := sql%rowcount;
74 oe_debug_pub.add(g_count || ' rows updated');
75
76 /**customer level update**/
77
78 oe_debug_pub.add('Updating Table OE_AGREEMENTS_B for Customer Data');
79
80 UPDATE oe_agreements_b a
81 SET sold_to_org_id = (select distinct m.customer_id
82 from ra_customer_merges m
83 where a.sold_to_org_id = m.duplicate_id
84 and m.process_flag = 'Y'
85 and m.request_id = req_id
86 and m.set_number = set_num),
87 last_update_date = sysdate,
88 last_updated_by = -1,
89 last_update_login = -1
90
91 WHERE sold_to_org_id in (select m.duplicate_id
92 from ra_customer_merges m
93 where m.process_flag = 'Y'
94 and m.request_id = req_id
95 and m.set_number = set_num);
96 g_count := sql%rowcount;
97 oe_debug_pub.add(g_count || ' rows updated');
98
99 END IF;
100
101 oe_debug_pub.add('End QP_CUST_MRG_DATA_CLEANUP.Agreement_Merge()');
102
103 EXCEPTION
104 WHEN OTHERS THEN
105 oe_debug_pub.add('Error in QP_CUST_MRG_DATA_CLEANUP.Agreement_Merge()');
106 oe_debug_pub.add(substr(sqlerrm, 1, 2000));
107 raise;
108
109 END Agreement_Merge;
110
111
112
113 PROCEDURE Qualifier_Merge(req_id NUMBER, set_num NUMBER, process_mode VARCHAR2)
114 IS
115
116 --For 'Ship To' Qualifier Attribute
117 CURSOR c1
118 IS
119 select qualifier_id
120 from qp_qualifiers
121 where qualifier_context = 'CUSTOMER'
122 and qualifier_attribute = 'QUALIFIER_ATTRIBUTE11'
123 and qualifier_attr_value in (select to_char(m.duplicate_site_id)
124 from ra_customer_merges m
125 where m.process_flag = 'Y'
126 and m.request_id = req_id
127 and m.set_number = set_num)
128 for update nowait;
129
130 --For 'Site Use' Qualifier Attribute
131 CURSOR c2
132 IS
133 select qualifier_id
134 from qp_qualifiers
135 where qualifier_context = 'CUSTOMER'
136 and qualifier_attribute = 'QUALIFIER_ATTRIBUTE5'
137 and qualifier_attr_value in (select to_char(m.duplicate_site_id)
138 from ra_customer_merges m
139 where m.process_flag = 'Y'
140 and m.request_id = req_id
141 and m.set_number = set_num)
142 for update nowait;
143
144 --For 'Bill To' Qualifier Attribute
145 CURSOR c3
146 IS
147 select qualifier_id
148 from qp_qualifiers
149 where qualifier_context = 'CUSTOMER'
150 and qualifier_attribute = 'QUALIFIER_ATTRIBUTE14'
151 and qualifier_attr_value in (select to_char(m.duplicate_site_id)
152 from ra_customer_merges m
153 where m.process_flag = 'Y'
154 and m.request_id = req_id
155 and m.set_number = set_num)
156 for update nowait;
157
158 --For 'Customer Name' Qualifier Attribute
159 CURSOR c4
160 IS
161 select qualifier_id
162 from qp_qualifiers
163 where qualifier_context = 'CUSTOMER'
164 and qualifier_attribute = 'QUALIFIER_ATTRIBUTE2'
165 and qualifier_attr_value in (select to_char(m.duplicate_id)
166 from ra_customer_merges m
167 where m.process_flag = 'Y'
168 and m.request_id = req_id
169 and m.set_number = set_num)
170 for update nowait;
171
172 BEGIN
173
174 oe_debug_pub.add('Begin QP_CUST_MRG_DATA_CLEANUP.Qualifier_Merge()');
175
176 /**************************************************
177 Merge Qualifiers at both site and customer Level
178 **************************************************/
179
180 IF (process_mode = 'LOCK') THEN
181
182 oe_debug_pub.add('Locking Table QP_QUALIFIERS');
183
184 open c1;
185 close c1;
186
187 open c2;
188 close c2;
189
190 open c3;
191 close c3;
192
193 open c4;
194 close c4;
195
196 ELSE
197
198 /** site level update **/
199
200 oe_debug_pub.add('Updating Table QP_QUALIFIERS for Customer Site Data - Ship To');
201
202 --For 'Ship To' Qualifier Attribute
203 UPDATE qp_qualifiers
204 SET qualifier_attr_value = (select distinct to_char(m.customer_site_id)
205 from ra_customer_merges m
206 where m.duplicate_site_id =
207 to_number(qualifier_attr_value)
208 and m.process_flag = 'Y'
209 and m.request_id = req_id
210 and m.set_number = set_num),
211 last_update_date = sysdate,
212 last_updated_by = -1,
213 last_update_login = -1
214
215 WHERE qualifier_context = 'CUSTOMER'
216 AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE11'
217 AND qualifier_attr_value in (select to_char(m.duplicate_site_id)
218 from ra_customer_merges m
219 where m.process_flag = 'Y'
220 and m.request_id = req_id
221 and m.set_number = set_num);
222
223 g_count := sql%rowcount;
224 oe_debug_pub.add(g_count || ' rows updated');
225
226
227 oe_debug_pub.add('Updating Table QP_QUALIFIERS for Customer Site Data - Site Use');
228
229 --For 'Site Use' Qualifier Attribute
230 UPDATE qp_qualifiers
231 SET qualifier_attr_value = (select distinct to_char(m.customer_site_id)
232 from ra_customer_merges m
233 where m.duplicate_site_id =
234 to_number(qualifier_attr_value)
235 and m.process_flag = 'Y'
236 and m.request_id = req_id
237 and m.set_number = set_num),
238 last_update_date = sysdate,
239 last_updated_by = -1,
240 last_update_login = -1
241
242 WHERE qualifier_context = 'CUSTOMER'
243 AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE5'
244 AND qualifier_attr_value in (select to_char(m.duplicate_site_id)
245 from ra_customer_merges m
246 where m.process_flag = 'Y'
247 and m.request_id = req_id
248 and m.set_number = set_num);
249
250 g_count := sql%rowcount;
251 oe_debug_pub.add(g_count || ' rows updated');
252
253
254 oe_debug_pub.add('Updating Table QP_QUALIFIERS for Customer Site Data - Bill To');
255
256 --For 'Bill To' Qualifier Attribute
257 UPDATE qp_qualifiers
258 SET qualifier_attr_value = (select distinct to_char(m.customer_site_id)
259 from ra_customer_merges m
260 where m.duplicate_site_id =
261 to_number(qualifier_attr_value)
262 and m.process_flag = 'Y'
263 and m.request_id = req_id
264 and m.set_number = set_num),
265 last_update_date = sysdate,
266 last_updated_by = -1,
267 last_update_login = -1
268
269 WHERE qualifier_context = 'CUSTOMER'
270 AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE14'
271 AND qualifier_attr_value in (select to_char(m.duplicate_site_id)
272 from ra_customer_merges m
273 where m.process_flag = 'Y'
274 and m.request_id = req_id
275 and m.set_number = set_num);
276
277 g_count := sql%rowcount;
278 oe_debug_pub.add(g_count || ' rows updated');
279
280
281 /**customer level update**/
282
283 oe_debug_pub.add('Updating Table QP_QUALIFIERS for Customer Data');
284
285 --For 'Customer Name' Qualifier Attribute
286 UPDATE qp_qualifiers
287 SET qualifier_attr_value = (select distinct to_char(m.customer_id)
288 from ra_customer_merges m
289 where m.duplicate_id =
290 to_number(qualifier_attr_value)
291 and m.process_flag = 'Y'
292 and m.request_id = req_id
293 and m.set_number = set_num),
294 last_update_date = sysdate,
295 last_updated_by = -1,
296 last_update_login = -1
297
298 WHERE qualifier_context = 'CUSTOMER'
299 AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE2'
300 AND qualifier_attr_value in (select to_char(m.duplicate_id)
301 from ra_customer_merges m
302 where m.process_flag = 'Y'
303 and m.request_id = req_id
304 and m.set_number = set_num);
305
306 g_count := sql%rowcount;
307 oe_debug_pub.add(g_count || ' rows updated');
308
309
310 END IF;
311
312 oe_debug_pub.add('End QP_CUST_MRG_DATA_CLEANUP.Qualifier_Merge()');
313
314 EXCEPTION
315 WHEN OTHERS THEN
316 oe_debug_pub.add('Error in QP_CUST_MRG_DATA_CLEANUP.Qualifier_Merge()');
317 oe_debug_pub.add(substr(sqlerrm, 1, 2000));
318 raise;
319
320 END Qualifier_Merge;
321
322
323 PROCEDURE Merge (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) IS
324 BEGIN
325
326 oe_debug_pub.add('Begin QP_CUST_MRG_DATA_CLEANUP.Merge()');
327
328 Agreement_Merge(req_id, set_num, process_mode);
329 Qualifier_Merge(req_id, set_num, process_mode);
330
331 oe_debug_pub.add('End QP_CUST_MRG_DATA_CLEANUP.Merge()');
332
333 EXCEPTION
334 when others then
335 raise;
336
337 END Merge;
338
339 END QP_CUST_MRG_DATA_CLEANUP;