[Home] [Help]
5: -- Package name : CS_CH_PARTY_MERGE_PKG
6: -- Purpose : Merges duplicate party_sites in Service tables. The
7: -- Service tables that need to be considered
8: -- are:
9: -- CS_ESTIMATE_DETAILS and CS_CHG_SUB_RESTRICTIONS.
10: --
11: -- History
12: -- MM-DD-YYYY NAME MODIFICATIONS
13: -- 11-20-2000 aseethep Created.
13: -- 11-20-2000 aseethep Created.
14: -- 10-22-2001 tkochend Removed commit statement
15: -- 11-08-2002 mviswana Added NOCOPY Functionality to file
16: -- 12-04-2002 tkochend Moved to correct driver phase
17: -- 05-04-2003 mviswana Added the new 11.5.9 TCA FK from cs_estimate_details
18: -- 06-04-2003 mviswana Changed the procedure to merge sites to follow TCA stds of using
19: -- p_from_fk_id
20: -- 08-12-2003 cnemalik For 11.5.10, added the new Bill To Customer Restriction in the
21: -- Auto Submission Restriction Table.
32: TYPE ROWID_TBL IS TABLE OF ROWID
33: INDEX BY BINARY_INTEGER;
34:
35:
36: -- The following procedure merges CS_ESTIMATE_DETAILS columns:
37: -- bill_to_party_id
38: -- ship_to_party_id
39: -- bill_to_contact_id
40: -- ship_to_contact_id
54: IS
55: -- cursor fetches all the records that need to be merged.
56: cursor c1 is
57: select rowid
58: from cs_estimate_details
59: where p_from_fk_id in (bill_to_contact_id, ship_to_contact_id,
60: bill_to_party_id, ship_to_party_id )
61: for update nowait;
62:
105: if p_from_fk_id <> p_to_fk_id then
106: begin
107: -- obtain lock on records to be updated.
108: arp_message.set_name('AR', 'AR_LOCKING_TABLE');
109: arp_message.set_token('TABLE_NAME', 'CS_ESTIMATE_DETAILS', FALSE);
110:
111: open c1;
112: fetch c1 bulk collect into l_rowid_tbl;
113: close c1;
117: RETURN;
118: end if;
119:
120: forall i in 1..l_rowid_tbl.count
121: update cs_estimate_details
122: set bill_to_contact_id = decode(bill_to_contact_id, p_from_fk_id, p_to_fk_id, bill_to_contact_id),
123: ship_to_contact_id = decode(ship_to_contact_id, p_from_fk_id, p_to_fk_id, ship_to_contact_id),
124: bill_to_party_id = decode(bill_to_party_id, p_from_fk_id, p_to_fk_id, bill_to_party_id),
125: ship_to_party_id = decode(ship_to_party_id, p_from_fk_id, p_to_fk_id, ship_to_party_id),
136:
137: exception
138: when resource_busy then
139: arp_message.set_line(g_proc_name || '.' || l_api_name || '; Could not obtain lock for records in table ' ||
140: 'CS_ESTIMATE_DETAILS for bill_to_party_id / ship_to_party_id ' ||
141: 'bill_to_contact_id / ship_to_contact_id = ' || p_from_fk_id );
142: x_return_status := FND_API.G_RET_STS_ERROR;
143: raise;
144:
152:
153: END CS_CHG_ALL_MERGE_PARTY ;
154:
155:
156: -- The following procedure merges CS_ESTIMATE_DETAILS columns:
157: -- invoice_to_org_id
158: -- ship_to_org_id
159:
160: PROCEDURE CS_CHG_ALL_MERGE_SITE_ID(
173: -- Added for bug # 2983666
174: -- cursor fetches all the records that need to be merged.
175: cursor c1 is
176: select rowid
177: from cs_estimate_details
178: where p_from_fk_id in (invoice_to_org_id, ship_to_org_id)
179: for update nowait;
180:
181: l_rowid_tbl ROWID_TBL;
220: -- dependent record exists on the new parent. If a duplicate exists then do
221: -- not transfer and return the id of the duplicate record as the Merged To Id
222:
223:
224: -- In the case of CS_ESTIMATE_DETAILS table, we store invoice_to_org_id
225: -- and ship_to_org_id which are forign keys to HZ_PARTY_SITES.PARTY_SITE_ID.
226: -- If the party who is tied to this site has been merged then, it is possible
227: -- that this site use id is being transferred under the new party or it
228: -- may have been deleted if its a duplicate party_site_use_id
231: if p_from_fk_id <> p_to_fk_id then
232: begin
233: -- obtain lock on records to be updated.
234: arp_message.set_name('AR', 'AR_LOCKING_TABLE');
235: arp_message.set_token('TABLE_NAME', 'CS_ESTIMATE_DETAILS', FALSE);
236:
237: open c1;
238: fetch c1 bulk collect into l_rowid_tbl;
239: close c1;
247: -- Commented for bug # 2983666
248: /*
249:
250: -- dbms_output.put_line('Beggining of Update');
251: update cs_estimate_details
252: set invoice_to_org_id = decode(invoice_to_org_id, p_from_fk_id, p_to_fk_id, invoice_to_org_id),
253: ship_to_org_id = decode(ship_to_org_id,p_from_fk_id,p_to_fk_id,ship_to_org_id),
254: last_update_date = SYSDATE,
255: last_updated_by = G_USER_ID,
260:
261: -- Added for bug # 2983666
262: -- dbms_output.put_line('Beggining of Update');
263: forall i in 1..l_rowid_tbl.count
264: update cs_estimate_details
265: set invoice_to_org_id = decode(invoice_to_org_id, p_from_fk_id, p_to_fk_id, invoice_to_org_id),
266: ship_to_org_id = decode(ship_to_org_id,p_from_fk_id,p_to_fk_id,ship_to_org_id),
267: last_update_date = SYSDATE,
268: last_updated_by = G_USER_ID,
279: exception
280: when resource_busy then
281: arp_message.set_line(g_proc_name || '.' || l_api_name ||
282: '; Could not obtain lock for records in table ' ||
283: 'CS_ESTIMATE_DETAILS for invoice_to_org_id/ship_to_org_id = ' || p_from_fk_id );
284: x_return_status := FND_API.G_RET_STS_ERROR;
285: raise;
286:
287: when others then