1 PACKAGE BODY JTF_FM_PARTY_MERGE_PKG AS
2 /* $Header: JTFFMPMB.pls 120.0 2005/05/11 09:06:45 appldev ship $ */
3
4
5 G_PROC_NAME CONSTANT VARCHAR2(30) := 'JTF_FM_PARTY_MERGE_PKG';
6 G_USER_ID CONSTANT NUMBER(15) := FND_GLOBAL.USER_ID;
7 G_LOGIN_ID CONSTANT NUMBER(15) := FND_GLOBAL.LOGIN_ID;
8
9
10
11 PROCEDURE JTF_FM_MERGE_PARTY (
12 p_entity_name IN VARCHAR2,
13 p_from_id IN NUMBER,
14 x_to_id OUT NOCOPY NUMBER,
15 p_from_fk_id IN NUMBER,
16 p_to_fk_id IN NUMBER,
17 p_parent_entity_name IN VARCHAR2,
18 p_batch_id IN NUMBER,
19 p_batch_party_id IN NUMBER,
20 x_return_status OUT NOCOPY VARCHAR2)
21 IS
22
23 cursor c2 is
24 select party_name
25 from hz_parties
26 where party_id = p_to_fk_id;
27
28
29
30 l_merge_reason_code VARCHAR2(30);
31 l_api_name VARCHAR2(30) := 'JTF_FM_MERGE_PARTY';
32 l_count NUMBER(10) := 0;
33 l_party_name VARCHAR2(240);
34 l_content_number NUMBER;
35 l_hist_req_id NUMBER;
36 l_submit_dt_tm DATE;
37 l_batch_number NUMBER;
38
39 RESOURCE_BUSY EXCEPTION;
40 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
41
42 BEGIN
43 arp_message.set_line('JTF_FM_PARTY_MERGE_PKG.JTF_FM_MERGE_PARTY()+');
44
45 x_return_status := FND_API.G_RET_STS_SUCCESS;
46
47 select merge_reason_code
48 into l_merge_reason_code
49 from hz_merge_batch
50 where batch_id = p_batch_id;
51
52 if l_merge_reason_code = 'DUPLICATE' then
53 -- if reason code is duplicate then allow the party merge to happen without
54 -- any validations.
55 null;
56 else
57 -- if there are any validations to be done, include it in this section
58 null;
59 end if;
60
61 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
62 -- needs to be done. Set Merged To Id is same as Merged From Id and return
63
64 if p_from_fk_id = p_to_fk_id then
65 x_to_id := p_from_id;
66 return;
67 end if;
68
69 -- If the parent has changed(ie. Parent is getting merged) then transfer the
70 -- dependent record to the new parent. Before transferring check if a similar
71 -- dependent record exists on the new parent. If a duplicate exists then do
72 -- not transfer and return the id of the duplicate record as the Merged To Id
73
74
75 -- In the case of JTF_FM_CONTENT_HISTORY table, if party id 1000 got merged to party id 2000
76 -- then, we have to update all records with customer_id = 1000 to 2000
77
78 if p_from_fk_id <> p_to_fk_id then
79 begin
80 -- obtain lock on records to be updated.
81 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
82 arp_message.set_token('TABLE_NAME', p_entity_name, FALSE);
83
84
85 open c2;
86 fetch c2 into l_party_name;
87 close c2;
88
89 IF (upper(p_entity_name) = 'JTF_FM_PREVIEWS_V')
90 THEN
91 update JTF_FM_PREVIEWS
92 set fm_party_id = p_to_fk_id,
93 last_update_date = hz_utility_pub.last_update_date,
94 last_updated_by = hz_utility_pub.user_id,
95 last_update_login = hz_utility_pub.last_update_login
96 where fm_party_id = p_from_fk_id;
97
98 ELSIF (upper(p_entity_name) = 'JTF_FM_PROCESSED_V')
99 THEN
100 update JTF_FM_PROCESSED
101 set party_id = p_to_fk_id,
102 party_name = l_party_name,
103 last_update_date = hz_utility_pub.last_update_date,
104 last_updated_by = hz_utility_pub.user_id,
105 last_update_login = hz_utility_pub.last_update_login
106 where party_id = p_from_fk_id;
107 ELSE
108 update JTF_FM_CONTENT_HISTORY
109 set party_id = p_to_fk_id,
110 party_name = l_party_name,
111 last_update_date = hz_utility_pub.last_update_date,
112 last_updated_by = hz_utility_pub.user_id,
113 last_update_login = hz_utility_pub.last_update_login
114 where party_id = p_from_fk_id;
115 END IF;
116
117 l_count := sql%rowcount;
118
119 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
120 arp_message.set_token('NUM_ROWS', to_char(l_count) );
121
122
123 exception
124 when resource_busy then
125 arp_message.set_line(g_proc_name || '.' || l_api_name ||
126 '; Could not obtain lock for records in table ' ||
127 p_entity_name ||
128 ' for party_id = ' || p_from_fk_id );
129 x_return_status := FND_API.G_RET_STS_ERROR;
130 raise;
131
132 when others then
133 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
134 x_return_status := FND_API.G_RET_STS_ERROR;
135 raise;
136 end;
137 end if;
138 END JTF_FM_MERGE_PARTY;
139
140 END JTF_FM_PARTY_MERGE_PKG;
[Home] [Help]