1 PACKAGE BODY JTF_IH_PARTY_MERGE_PKG AS
2 /* $Header: JTFIHPMB.pls 120.2 2006/02/16 23:33:17 nchouras ship $ */
3
4 -- Start of Comments
5 -- Package name : JTF_IH_PARTY_MERGE_PKG
6 -- Purpose : Merges duplicate parties in JTF_IH_INTERACTIONS table.
7
8 --
9 -- History
10 -- MM-DD-YYYY NAME MODIFICATIONS
11 -- 01-04-2001 James Baldo Jr. Created.
12 -- 04-01-2002 Igor Aleshin Fixed Bug 2295015 - itcrm tca merg: crmperf:jtf:party merge perf fixes
13 -- on crmimp (copy of crmap)
14 -- 06-24-2003 Igor Aleshin Enh# 1846960 - Added support for Contact Party Ids columns
15 -- 05-07-2004 Igor Aleshin Fixed File.sql.35 issue
16 --
17 -- End of Comments
18
19
20 G_PROC_NAME CONSTANT VARCHAR2(30) := 'JTF_IH_PARTY_MERGE_PKG';
21 G_USER_ID CONSTANT NUMBER(15) := FND_GLOBAL.USER_ID;
22 G_LOGIN_ID CONSTANT NUMBER(15) := FND_GLOBAL.LOGIN_ID;
23
24
25
26 PROCEDURE JTF_IH_MERGE_PARTY (
27 p_entity_name IN VARCHAR2,
28 p_from_id IN NUMBER,
29 x_to_id OUT NOCOPY NUMBER,
30 p_from_fk_id IN NUMBER,
31 p_to_fk_id IN NUMBER,
32 p_parent_entity_name IN VARCHAR2,
33 p_batch_id IN NUMBER,
34 p_batch_party_id IN NUMBER,
35 x_return_status OUT NOCOPY VARCHAR2)
36 IS
37 cursor c1 is
38 select 1
39 from jtf_ih_interactions
40 where party_id = p_from_fk_id
41 for update nowait;
42
43
44 l_merge_reason_code VARCHAR2(30);
45 l_api_name VARCHAR2(30);
46 l_count NUMBER(10);
47
48 RESOURCE_BUSY EXCEPTION;
49 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
50 s_Party_Type VARCHAR2(30);
51 BEGIN
52 l_api_name := 'JTF_IH_MERGE_PARTY';
53 l_count := 0;
54 arp_message.set_line('JTF_IH_PARTY_MERGE_PKG.JTF_IH_MERGE_PARTY()+');
55
56 x_return_status := FND_API.G_RET_STS_SUCCESS;
57 select merge_reason_code
58 into l_merge_reason_code
59 from hz_merge_batch
60 where batch_id = p_batch_id;
61 if l_merge_reason_code = 'DUPLICATE' then
62 -- if reason code is duplicate then allow the party merge to happen without
63 -- any validations.
64 null;
65 else
66 -- if there are any validations to be done, include it in this section
67 null;
68 end if;
69
70 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
71 -- needs to be done. Set Merged To Id is same as Merged From Id and return
72
73 if p_from_fk_id = p_to_fk_id then
74 x_to_id := p_from_id;
75 return;
76 end if;
77
78 -- If the parent has changed(ie. Parent is getting merged) then transfer the
79 -- dependent record to the new parent. Before transferring check if a similar
80 -- dependent record exists on the new parent. If a duplicate exists then do
81 -- not transfer and return the id of the duplicate record as the Merged To Id
82
83
84 -- In the case of JTF_IH_INTERACTIONS table, if party id 1000 got merged to party id 2000
85 -- then, we have to update all records with customer_id = 1000 to 2000
86
87 if p_from_fk_id <> p_to_fk_id then
88 begin
89 -- obtain lock on records to be updated.
90 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
91 arp_message.set_token('TABLE_NAME', 'JTF_IH_INTERACTIONS', FALSE);
92
93 open c1;
94 close c1;
95 select party_type into s_Party_Type from hz_parties where party_id = p_to_fk_id;
96 update jtf_ih_interactions
97 -- set party_id = decode(party_id, p_to_fk_id, p_to_fk_id, party_id),
98 set party_id = p_to_fk_id,
99 last_update_date = hz_utility_pub.last_update_date,
100 last_updated_by = hz_utility_pub.user_id,
101 last_update_login = hz_utility_pub.last_update_login,
102 -- Enh# 1846960
103 -- If Primary_Party_Id equals Party_Id then update it to new value.
104 primary_party_id = decode(primary_party_id, party_id, p_to_fk_id,primary_party_id),
105 -- If Contact_Party_Id equals Primary_Party_ID and Primary_Party_ID is going to be
106 -- not a person, then update Contact_Party_ID to NULL
107 contact_party_id = decode(nvl(contact_party_id,-1),-1,NULL,
108 decode(contact_party_id,primary_party_id,
109 (decode(s_Party_Type,'PERSON',p_to_fk_id,NULL)), contact_party_id)),
110 request_id = hz_utility_pub.request_id,
111 program_application_id = hz_utility_pub.program_application_id,
112 program_id = hz_utility_pub.program_id,
113 program_update_date = sysdate,
114 object_version_number = object_version_number + 1 -- Bug# 2295015
115 -- where interaction_id = p_from_id;
116 where party_id = p_from_fk_id; -- Bug 2295015
117
118 ----where customer_id = p_from_fk_id;
119
120 l_count := sql%rowcount;
121
122 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
123 arp_message.set_token('NUM_ROWS', to_char(l_count) );
124
125 exception
126 when resource_busy then
127 arp_message.set_line(g_proc_name || '.' || l_api_name ||
128 '; Could not obtain lock for records in table ' ||
129 'JTF_IH_INTERACTIONS for party_id = ' || p_from_fk_id );
130 x_return_status := FND_API.G_RET_STS_ERROR;
131 --dbms_output.put_line('Busy');
132 raise;
133
134 when others then
135 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
136 --dbms_output.put_line('Other ');
137 x_return_status := FND_API.G_RET_STS_ERROR;
138 raise;
139 end;
140 -- update the doc_id value in JTF_IH_ACTIVITES for doc_ref
141 -- (object_code) values that populate doc_id with party_id values
142 update jtf_ih_activities
143 set doc_id = p_to_fk_id
144 where doc_id = p_from_fk_id
145 and doc_ref IN (
146 select object_code
147 from jtf_objects_b
148 where from_table = 'HZ_PARTIES'
149 );
150 end if;
151 END JTF_IH_MERGE_PARTY;
152 END JTF_IH_PARTY_MERGE_PKG;
[Home] [Help]