[Home] [Help]
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: **************************************************/
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:
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
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');
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
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()');
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()');
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;
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:
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: **************************************************/
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:
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)
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:
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)
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:
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)
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:
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)
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:
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()');
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;
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:
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;