1 PACKAGE BODY AS_INTEREST_MERGE_PKG as
2 /* $Header: asxpmitb.pls 120.1 2005/11/25 01:30:25 subabu noship $ */
3 --
4 -- NAME
5 -- AS_INTEREST_MERGE_PKG
6 --
7 -- HISTORY
8 -- 02/15/2001 ACNG CREATED
9 --
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):='AS_INTEREST_MERGE_PKG';
12
13 PROCEDURE INTERESTS_MERGE
14 ( p_entity_name IN VARCHAR2
15 ,p_from_id IN NUMBER
16 ,p_to_id IN OUT NOCOPY NUMBER
17 ,p_from_fk_id IN NUMBER
18 ,p_to_fk_id IN NUMBER
19 ,p_parent_entity_name IN VARCHAR2
20 ,p_batch_id IN NUMBER
21 ,p_batch_party_id IN NUMBER
22 ,x_return_status IN OUT NOCOPY VARCHAR2
23 ) is
24 l_api_name CONSTANT VARCHAR2(30) := 'INTEREST_MERGE';
25 l_api_version_number CONSTANT NUMBER := 2.0;
26 l_merge_reason_code VARCHAR2(30);
27
28 BEGIN
29
30 FND_FILE.PUT_LINE(FND_FILE.LOG,'AS_INTEREST_MERGE_PKG.INTEREST_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
31
32 x_return_status := FND_API.G_RET_STS_SUCCESS;
33
34 select merge_reason_code into l_merge_reason_code
35 from HZ_MERGE_BATCH
36 where batch_id = p_batch_id;
37
38 IF l_merge_reason_code = 'DUPLICATE' THEN
39 -- ***************************************************************************
40 -- if reason code is duplicate then allow the party merge to happen without
41 -- any validations.
42 -- ***************************************************************************
43 null;
44 ELSE
45 -- ***************************************************************************
46 -- if there are any validations to be done, include it in this section
47 -- ***************************************************************************
48 null;
49 END IF;
50
51 -- ***************************************************************************
52 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
53 -- needs to be done. Set Merged To Id is same as Merged From Id and return
54 -- ***************************************************************************
55 if p_from_fk_id = p_to_fk_id then
56 p_to_id := p_from_id;
57 return;
58 end if;
59
60 -- ***************************************************************************
61 -- If the parent has changed(ie. Parent is getting merged) then transfer the
62 -- dependent record to the new parent. Before transferring check if a similar
63 -- dependent record exists on the new parent. If a duplicate exists then do
64 -- not transfer and return the id of the duplicate record as the Merged To Id
65 -- ***************************************************************************
66
67 -- ***************************************************************************
68 -- Add your own logic if you need to take care of the following cases
69 -- Check the if record duplicate if change party_id from merge-from
70 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
71 -- situation
72 --
73 -- customer_id address_id contact_id
74 -- =========== ========== ==========
75 -- 1200 1100
76 -- 1300 1400
77 --
78 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
79 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
80 -- therefore, if changing 1200 to 1300 (customer_id)
81 -- and 1100 to 1400 (address_id), then it will cause unique
82 -- key violation assume that all other fields are the same
83 -- So, please check if you need to check for record duplication
84 -- ***************************************************************************
85
86 IF p_from_fk_id <> p_to_fk_id THEN
87 BEGIN
88 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
89 UPDATE AS_INTERESTS_ALL
90 set object_version_number = nvl(object_version_number,0) + 1, customer_id = p_to_fk_id,
91 last_update_date = hz_utility_pub.last_update_date,
92 last_updated_by = hz_utility_pub.user_id,
93 last_update_login = hz_utility_pub.last_update_login,
94 program_application_id = hz_utility_pub.program_application_id,
95 program_id = hz_utility_pub.program_id,
96 program_update_date = sysdate
97 where customer_id = p_from_fk_id;
98 ELSIF p_parent_entity_name = 'HZ_PARTY_SITES' THEN -- merge party_site
99 UPDATE AS_INTERESTS_ALL
100 set object_version_number = nvl(object_version_number,0) + 1, address_id = p_to_fk_id,
101 last_update_date = hz_utility_pub.last_update_date,
102 last_updated_by = hz_utility_pub.user_id,
103 last_update_login = hz_utility_pub.last_update_login,
104 program_application_id = hz_utility_pub.program_application_id,
105 program_id = hz_utility_pub.program_id,
106 program_update_date = sysdate
107 where address_id = p_from_fk_id;
108 ELSIF p_parent_entity_name = 'HZ_ORG_CONTACTS' THEN -- merge org_contact
109 UPDATE AS_INTERESTS_ALL
110 set object_version_number = nvl(object_version_number,0) + 1, contact_id = p_to_fk_id,
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 program_application_id = hz_utility_pub.program_application_id,
115 program_id = hz_utility_pub.program_id,
116 program_update_date = sysdate
117 where contact_id = p_from_fk_id;
118 END IF;
119 EXCEPTION
120 WHEN OTHERS THEN
121 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
122 x_return_status := FND_API.G_RET_STS_ERROR;
123 raise;
124 END;
125 END IF;
126
127 FND_FILE.PUT_LINE(FND_FILE.LOG,'AS_INTEREST_MERGE_PKG.INTEREST_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
128
129 END INTERESTS_MERGE;
130
131 PROCEDURE CURRENT_ENV_MERGE
132 ( p_entity_name IN VARCHAR2
133 ,p_from_id IN NUMBER
134 ,p_to_id IN OUT NOCOPY NUMBER
135 ,p_from_fk_id IN NUMBER
136 ,p_to_fk_id IN NUMBER
137 ,p_parent_entity_name IN VARCHAR2
138 ,p_batch_id IN NUMBER
139 ,p_batch_party_id IN NUMBER
140 ,x_return_status IN OUT NOCOPY VARCHAR2
141 ) is
142 l_api_name CONSTANT VARCHAR2(30) := 'CURRENT_ENV_MERGE';
143 l_api_version_number CONSTANT NUMBER := 2.0;
144 l_merge_reason_code VARCHAR2(30);
145 BEGIN
146
147 FND_FILE.PUT_LINE(FND_FILE.LOG,'AS_INTEREST_MERGE_PKG.CURRENT_ENV_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
148
149 x_return_status := FND_API.G_RET_STS_SUCCESS;
150
154
151 select merge_reason_code into l_merge_reason_code
152 from HZ_MERGE_BATCH
153 where batch_id = p_batch_id;
155 IF l_merge_reason_code = 'DUPLICATE' THEN
156 -- ***************************************************************************
157 -- if reason code is duplicate then allow the party merge to happen without
158 -- any validations.
159 -- ***************************************************************************
160 null;
161 ELSE
162 -- ***************************************************************************
163 -- if there are any validations to be done, include it in this section
164 -- ***************************************************************************
165 null;
166 END IF;
167
168 -- ***************************************************************************
169 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
170 -- needs to be done. Set Merged To Id is same as Merged From Id and return
171 -- ***************************************************************************
172 if p_from_fk_id = p_to_fk_id then
173 p_to_id := p_from_id;
174 return;
175 end if;
176
177 -- ***************************************************************************
178 -- If the parent has changed(ie. Parent is getting merged) then transfer the
179 -- dependent record to the new parent. Before transferring check if a similar
180 -- dependent record exists on the new parent. If a duplicate exists then do
181 -- not transfer and return the id of the duplicate record as the Merged To Id
182 -- ***************************************************************************
183
184 -- ***************************************************************************
185 -- Add your own logic if you need to take care of the following cases
186 -- Check the if record duplicate if change party_id from merge-from
187 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
188 -- situation
189 --
190 -- customer_id address_id contact_id
191 -- =========== ========== ==========
192 -- 1200 1100
193 -- 1300 1400
194 --
195 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
196 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
197 -- therefore, if changing 1200 to 1300 (customer_id)
198 -- and 1100 to 1400 (address_id), then it will cause unique
199 -- key violation assume that all other fields are the same
200 -- So, please check if you need to check for record duplication
201 -- ***************************************************************************
202
203 IF p_from_fk_id <> p_to_fk_id THEN
204 BEGIN
205 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
206 UPDATE AS_CURRENT_ENVIRONMENT
207 set customer_id = p_to_fk_id,
208 last_update_date = hz_utility_pub.last_update_date,
209 last_updated_by = hz_utility_pub.user_id,
210 last_update_login = hz_utility_pub.last_update_login
211 where customer_id = p_from_fk_id;
212 ELSIF p_parent_entity_name = 'HZ_PARTY_SITES' THEN -- merge party_site
213 UPDATE AS_CURRENT_ENVIRONMENT
214 set address_id = p_to_fk_id,
215 last_update_date = hz_utility_pub.last_update_date,
216 last_updated_by = hz_utility_pub.user_id,
217 last_update_login = hz_utility_pub.last_update_login
218 where address_id = p_from_fk_id;
219 END IF;
220 EXCEPTION
221 WHEN OTHERS THEN
222 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
223 x_return_status := FND_API.G_RET_STS_ERROR;
224 raise;
225 END;
226 END IF;
227
228 FND_FILE.PUT_LINE(FND_FILE.LOG,'AS_INTEREST_MERGE_PKG.CURRENT_ENV_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
229
230 END CURRENT_ENV_MERGE;
231
232 END AS_INTEREST_MERGE_PKG;
[Home] [Help]