[Home] [Help]
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;
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;
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),
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);
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),
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;
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;
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;
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;
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;
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
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:
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
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:
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
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:
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
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: