1 PACKAGE JTF_TASK_PARTY_MERGE_PKG AUTHID CURRENT_USER as
2 /* $Header: jtftkpms.pls 115.8 2002/12/04 02:13:19 cjang ship $ */
3 --/**==================================================================*
4 -- Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA
5 -- All rights reserved.
6 --+====================================================================+
7 -- Start of comments
8 -- API name : JTF_TASK_PARTY_MERGE_PKG
9 -- Type : Public.
10 -- Function : Party merge package. Merges duplicate parties
11 -- in TASKs tables.
12 -- Pre-reqs : None.
13 -- Parameters :
14 -- name direction type
15 -- ---- --------- ----
16 -- p_entity_name IN VARCHAR2 - Name of the entity that is being merged
17 -- p_from_id IN NUMBER - Id of the record that is being merged
18 -- x_to_id OUT NUMBER - Id of the record under the new parent
19 -- that its merged to
20 -- p_from_fk_id IN NUMBER - Id of the Old Parent
21 -- p_to_fk_id IN NUMBER - Id of the New Parent
22 -- p_parent_entity_name IN VARCHAR2 - Parent entity name
23 -- p_batch_id IN NUMBER - Id of the Batch
24 -- p_batch_party_id IN NUMBER - Id of the batch and party record
25
26 -- x_return_status OUT VARCHAR2 - Return the status of the procedure
27 --
28 -- All of the parameters are required
29 --
30 --
31 -- Version : 1.0
32 -------------------------------------------------------------------------------------------
33 -- History
34 -------------------------------------------------------------------------------------------
35 -- 16-FEB-01 tivanov Created.
36 ---------------------------------------------------------------------------------
37 --
38 -- End of comments
39 -------------------------------------------------------------------------------------------
40 --
41 -- The following foreign keys will be merged:
42 -- jtf_tasks_b.customer_id
43 -- jtf_tasks_b.address_id
44 -- jtf_tasks_b.source_object_id
45 -- jtf_tasks_b.source_object_name
46 -- jtf_task_audits_b.new_customer_id
47 -- jtf_task_audits_b.old_customer_id
48 -- jtf_task_audits_b.new_address_id
49 -- jtf_task_audits_b.old_address_id
50 -- jtf_task_audits_b.new_source_object_id
51 -- jtf_task_audits_b.old_source_object_id
52 -- jtf_task_audits_b.new_source_object_name
53 -- jtf_task_audits_b.old_source_object_name
54 -- jtf_task_references_b.object_id
55 -- jtf_task_references_b.object_name
56 -- jtf_task_contacts.contact_id
57 -- jtf_task_phones.phone_id
58 -------------------------------------------------------------------------------------------
59
60 ------------------------------------------------------------------------------------------
61 -- Procedure: TASK_MERGE_PARTY - Performs party ids merge in JTF_TASKS_B table.
62 -- Updates CUSTOMER_ID column with the merged to party_id value
63 -- e.g. if party_id 1000 got merged to party_id 2000 then, we have to update
64 -- all records with customer_id = 1000 to 2000.
65 ------------------------------------------------------------------------------------------
66
67 PROCEDURE TASK_MERGE_PARTY(
68 p_entity_name IN VARCHAR2,
69 p_from_id IN NUMBER,
70 x_to_id OUT NOCOPY NUMBER,
71 p_from_fk_id IN NUMBER,
72 p_to_fk_id IN NUMBER,
73 p_parent_entity_name IN VARCHAR2,
74 p_batch_id IN NUMBER,
75 p_batch_party_id IN NUMBER,
76 x_return_status OUT NOCOPY VARCHAR2);
77
78 ------------------------------------------------------------------------------------------
79 -- Procedure: TASK_MERGE_ADDRESS - Performs party_site merge in JTF_TASKS_B table.
80 -- Updates JTF_TASKS_B.ADDRESS_ID column with the merged to party_site_id
81 -- e.g. if party_site_id 1111 got merged to party_site_id 2222 then,
82 -- we have to update all records with address_id = 1111 to 2222
83 ------------------------------------------------------------------------------------------
84
85 PROCEDURE TASK_MERGE_ADDRESS(
86 p_entity_name IN VARCHAR2,
87 p_from_id IN NUMBER,
88 x_to_id OUT NOCOPY NUMBER,
89 p_from_fk_id IN NUMBER,
90 p_to_fk_id IN NUMBER,
91 p_parent_entity_name IN VARCHAR2,
92 p_batch_id IN NUMBER,
93 p_batch_party_id IN NUMBER,
94 x_return_status OUT NOCOPY VARCHAR2);
95
96 ------------------------------------------------------------------------------------------
97 -- Procedure: TASK_MERGE_SOURCE_OBJECT - Performs party ids merge in JTF_TASKS_B table.
98 -- Updates SOURCE_OBJECT_ID, SOURCE_OBJECT_NAME columns.
99 -- The SOURCE_OBJECT_ID, SOURCE_OBJECT_NAME , SOURCE_OBJECT_TYPE_CODE data
100 -- in JTF_TASKS table is dynamically retrieved from JTF_OBJECTS table.
101 -- The source object could be of type 'Party', 'Person (Party)' ,
102 -- 'Relationships(Party)' , 'Party Site' etc. The merge procedure updates
103 -- JTF_TASKS_B.SOURCE_OBJECT_ID and JTF_TASKS_B.SOURCE_OBJECT_NAME columns
104 -- with the merged to respectively party_id and party_name in all records
105 -- that have SOURCE_OBJECT_TYPE_CODE related to HZ_PARTY.PARTY_ID.
106 ------------------------------------------------------------------------------------------
107
108 PROCEDURE TASK_MERGE_SOURCE_OBJECT(
109 p_entity_name IN VARCHAR2,
110 p_from_id IN NUMBER,
111 x_to_id OUT NOCOPY NUMBER,
112 p_from_fk_id IN NUMBER,
113 p_to_fk_id IN NUMBER,
114 p_parent_entity_name IN VARCHAR2,
115 p_batch_id IN NUMBER,
116 p_batch_party_id IN NUMBER,
117 x_return_status OUT NOCOPY VARCHAR2);
118
119 ------------------------------------------------------------------------------------------
120 -- Procedure: TASK_AUDIT_MERGE_NEW_S_OBJECT - Performs party ids merge in
121 -- JTF_TASK_AUDITS_B table.
122 -- Updated columns - NEW_SOURCE_OBJECT_ID , NEW_SOURCE_OBJECT_NAME.
123 -- The NEW_SOURCE_OBJECT_ID, NEW_SOURCE_OBJECT_NAME ,
124 -- NEW_SOURCE_OBJECT_TYPE_CODE columns in JTF_TASK_AUDITS_B table
125 -- are copies of the corresponding columns in JTF_TASKS_B table which
126 -- are dynamically retrieved from JTF_OBJECTS table. The source object
127 -- could be of type 'Party', 'Person (Party)' , 'Relationships(Party)',
128 -- 'Party Site' etc. The merge procedure updates
129 -- JTF_TASK_AUDITS_B.NEW_SOURCE_OBJECT_ID and
130 -- JTF_TASK_AUDITS_B.SOURCE_OBJECT_NAME columns with the merged to
131 -- respectively party_id and party_name in all records that have
132 -- NEW_SOURCE_OBJECT_TYPE_CODE related to HZ_PARTY.PARTY_ID.
133 ------------------------------------------------------------------------------------------
134
135 PROCEDURE TASK_AUDIT_MERGE_NEW_S_OBJECT(
136 p_entity_name IN VARCHAR2,
137 p_from_id IN NUMBER,
138 x_to_id OUT NOCOPY NUMBER,
139 p_from_fk_id IN NUMBER,
140 p_to_fk_id IN NUMBER,
141 p_parent_entity_name IN VARCHAR2,
142 p_batch_id IN NUMBER,
143 p_batch_party_id IN NUMBER,
144 x_return_status OUT NOCOPY VARCHAR2);
145
146
147 ------------------------------------------------------------------------------------------
148 -- Procedure: TASK_AUDIT_MERGE_OLD_S_OBJECT - Performs party ids merge in
149 -- JTF_TASK_AUDITS_B table.
150 -- Updated columns - OLD_SOURCE_OBJECT_ID , OLD_SOURCE_OBJECT_NAME.
151 -- The OLD_SOURCE_OBJECT_ID, OLD_SOURCE_OBJECT_NAME ,
152 -- OLD_SOURCE_OBJECT_TYPE_CODE columns in JTF_TASK_AUDITS_B table are copies
153 -- of the corresponding columns in JTF_TASKS_B table which are dynamically
154 -- retrieved from JTF_OBJECTS table. The source object could be of type
155 -- 'Party', 'Person (Party)' , 'Relationships(Party)', 'Party Site' etc.
156 -- The merge procedure updates JTF_TASK_AUDITS_B.OLD_SOURCE_OBJECT_ID
157 -- and JTF_TASK_AUDITS_B.SOURCE_OBJECT_NAME columns with the merged
158 -- to respectively party_id and party_name in all records that have
159 -- OLD_SOURCE_OBJECT_TYPE_CODE related to HZ_PARTY.PARTY_ID.
160 ------------------------------------------------------------------------------------------
161
162
163 PROCEDURE TASK_AUDIT_MERGE_OLD_S_OBJECT(
164 p_entity_name IN VARCHAR2,
165 p_from_id IN NUMBER,
166 x_to_id OUT NOCOPY NUMBER,
167 p_from_fk_id IN NUMBER,
168 p_to_fk_id IN NUMBER,
169 p_parent_entity_name IN VARCHAR2,
170 p_batch_id IN NUMBER,
171 p_batch_party_id IN NUMBER,
172 x_return_status OUT NOCOPY VARCHAR2);
173
174 ------------------------------------------------------------------------------------------
175 -- Procedure: TASK_AUDIT_MERGE_NEW_CUSTOMER - Performs party ids merge in
176 -- JTF_TASK_AUDITS_B table.
177 -- Updates NEW_CUSTOMER_ID column with the merged to party_id value e.g.
178 -- if party_id 1000 got merged to party_id 2000 then, we have to update
179 -- all records with new_customer_id = 1000 to 2000.
180 ------------------------------------------------------------------------------------------
181
182
183 PROCEDURE TASK_AUDIT_MERGE_NEW_CUSTOMER(
184 p_entity_name IN VARCHAR2,
185 p_from_id IN NUMBER,
186 x_to_id OUT NOCOPY NUMBER,
187 p_from_fk_id IN NUMBER,
188 p_to_fk_id IN NUMBER,
189 p_parent_entity_name IN VARCHAR2,
190 p_batch_id IN NUMBER,
191 p_batch_party_id IN NUMBER,
192 x_return_status OUT NOCOPY VARCHAR2);
193
194 ------------------------------------------------------------------------------------------
195 -- Procedure: TASK_AUDIT_MERGE_OLD_CUSTOMER - Performs party ids merge in
196 -- JTF_TASK_AUDITS_B table.
197 -- Updates OLD_CUSTOMER_ID column with the merged to party_id value
198 -- e.g. if party_id 1000 got merged to party_id 2000 then, we have
199 -- to update all records with old_customer_id = 1000 to 2000.
200 ------------------------------------------------------------------------------------------
201
202 PROCEDURE TASK_AUDIT_MERGE_OLD_CUSTOMER(
203 p_entity_name IN VARCHAR2,
204 p_from_id IN NUMBER,
205 x_to_id OUT NOCOPY NUMBER,
206 p_from_fk_id IN NUMBER,
207 p_to_fk_id IN NUMBER,
208 p_parent_entity_name IN VARCHAR2,
209 p_batch_id IN NUMBER,
210 p_batch_party_id IN NUMBER,
211 x_return_status OUT NOCOPY VARCHAR2);
212
213 ------------------------------------------------------------------------------------------
214 -- Procedure: TASK_AUDIT_MERGE_NEW_ADDRESS - Performs party_site merge in
215 -- JTF_TASK_AUDITS_B table. Updated columns - NEW_ADDRESS_ID.
216 -- Updates JTF_TASK_AUDITS_B.NEW_ADDRESS_ID column with the merged
217 -- to party_site_id e.g. if party_site_id 1111 got merged
218 -- to party_site_id 2222 then, we have to update all records
219 -- with new_address_id = 1111 to 2222
220 ------------------------------------------------------------------------------------------
221
222 PROCEDURE TASK_AUDIT_MERGE_NEW_ADDRESS(
223 p_entity_name IN VARCHAR2,
224 p_from_id IN NUMBER,
225 x_to_id OUT NOCOPY NUMBER,
226 p_from_fk_id IN NUMBER,
227 p_to_fk_id IN NUMBER,
228 p_parent_entity_name IN VARCHAR2,
229 p_batch_id IN NUMBER,
230 p_batch_party_id IN NUMBER,
231 x_return_status OUT NOCOPY VARCHAR2);
232
233 ------------------------------------------------------------------------------------------
234 -- Procedure: TASK_AUDIT_MERGE_OLD_ADDRESS - Performs party_site merge in
235 -- JTF_TASK_AUDITS_B table.
236 -- Updates JTF_TASK_AUDITS_B.OLD_ADDRESS_ID column with the merged
237 -- to party_site_id e.g. if party_site_id 1111 got merged
238 -- to party_site_id 2222 then, we have to update all records
239 -- with old_address_id = 1111 to 2222
240 ------------------------------------------------------------------------------------------
241 PROCEDURE TASK_AUDIT_MERGE_OLD_ADDRESS(
242 p_entity_name IN VARCHAR2,
243 p_from_id IN NUMBER,
244 x_to_id OUT NOCOPY NUMBER,
245 p_from_fk_id IN NUMBER,
246 p_to_fk_id IN NUMBER,
247 p_parent_entity_name IN VARCHAR2,
248 p_batch_id IN NUMBER,
249 p_batch_party_id IN NUMBER,
250 x_return_status OUT NOCOPY VARCHAR2);
251
252 ------------------------------------------------------------------------------------------
253 -- Procedure: TASK_REF_MERGE_PARTY_OBJECT - Performs party ids merge in
254 -- JTF_TASK_REFERENCES_B table. Updated columns - OBJECT_ID, OBJECT_NAME
255 -- The OBJECT_ID, OBJECT_NAME , OBJECT_TYPE_CODE data in JTF_TASK_REFEENCES_B
256 -- table is dynamically retrieved from JTF_OBJECTS table. The source object
257 -- could be of type 'Party', 'Person (Party)' , 'Relationships(Party)' ,
258 -- 'Party Site' etc. The merge procedure updates JTF_TASK_REFEENCES_B.
259 -- OBJECT_ID and JTF_TASK_REFEENCES_B. OBJECT_NAME columns with the merged
260 -- to respectively party _id and party_name in all records that have
261 -- SOURCE_OBJECT_TYPE_CODE related to HZ_PARTY_SITES.PARTY_ ID.
262 ------------------------------------------------------------------------------------------
263
264 PROCEDURE TASK_REF_MERGE_PARTY_OBJECT(
265 p_entity_name IN VARCHAR2,
266 p_from_id IN NUMBER,
267 x_to_id OUT NOCOPY NUMBER,
268 p_from_fk_id IN NUMBER,
269 p_to_fk_id IN NUMBER,
270 p_parent_entity_name IN VARCHAR2,
271 p_batch_id IN NUMBER,
272 p_batch_party_id IN NUMBER,
273 x_return_status OUT NOCOPY VARCHAR2);
274
275 ------------------------------------------------------------------------------------------
276 -- Procedure: TASK_REF_MERGE_PSITE_OBJECT - Performs party site ids merge in
277 -- JTF_TASK_REFERENCES_B table.
278 -- The OBJECT_ID, OBJECT_NAME , OBJECT_TYPE_CODE data in JTF_TASK_REFEENCES_B
279 -- table is dynamically retrieved from JTF_OBJECTS table. The source object
280 -- could be of type 'Party', 'Person (Party)' , 'Relationships(Party)' ,
281 -- 'Party Site' etc. The merge procedure updates JTF_TASK_REFEENCES_B.
282 -- OBJECT_ID and JTF_TASK_REFEENCES_B. OBJECT_NAME columns with the merged
283 -- to respectively party_site_id and party_site_name in all records that have
284 -- SOURCE_OBJECT_TYPE_CODE related to HZ_PARTY_SITES.PARTY_SITE_ID.
285 ------------------------------------------------------------------------------------------
286
287 PROCEDURE TASK_REF_MERGE_PSITE_OBJECT(
288 p_entity_name IN VARCHAR2,
289 p_from_id IN NUMBER,
290 x_to_id OUT NOCOPY NUMBER,
291 p_from_fk_id IN NUMBER,
292 p_to_fk_id IN NUMBER,
293 p_parent_entity_name IN VARCHAR2,
294 p_batch_id IN NUMBER,
295 p_batch_party_id IN NUMBER,
296 x_return_status OUT NOCOPY VARCHAR2);
297
298 ------------------------------------------------------------------------------------------
299 -- Procedure: TASK_MERGE_CONTACTS - Performs party ids merge in JTF_TASK_CONTACTS table.
300 -- Updates CONTACT_ID column in JTF_TASK_CONTACTS table with the merged
301 -- to contact id (party_id) value e.g. if party_id 1000 got merged
302 -- to party_id 2000 then, we have to update all records
303 -- with contact_id = 1000 to 2000.
304 ------------------------------------------------------------------------------------------
305
306 PROCEDURE TASK_MERGE_CONTACTS(
310 p_from_fk_id IN NUMBER,
307 p_entity_name IN VARCHAR2,
308 p_from_id IN NUMBER,
309 x_to_id OUT NOCOPY NUMBER,
311 p_to_fk_id IN NUMBER,
312 p_parent_entity_name IN VARCHAR2,
313 p_batch_id IN NUMBER,
314 p_batch_party_id IN NUMBER,
315 x_return_status OUT NOCOPY VARCHAR2);
316
317 ------------------------------------------------------------------------------------------
318 -- Procedure: TASK_MERGE_CONTACT_POINTS - Performs contact_point_id merge in
319 -- JTF_TASK_PHONES table.
320 -- If contact_point_id 1000 got merged to contact_point_id 2000 then
321 -- we have to update all records with phone_id = 1000 to 2000
322 ------------------------------------------------------------------------------------------
323
324 PROCEDURE TASK_MERGE_CONTACT_POINTS(
325 p_entity_name IN VARCHAR2,
326 p_from_id IN NUMBER,
327 x_to_id OUT NOCOPY NUMBER,
328 p_from_fk_id IN NUMBER,
329 p_to_fk_id IN NUMBER,
330 p_parent_entity_name IN VARCHAR2,
331 p_batch_id IN NUMBER,
332 p_batch_party_id IN NUMBER,
333 x_return_status OUT NOCOPY VARCHAR2);
334
335 ------------------------------------------------------------------------------------------
336 -- Procedure: SEARCH_MERGE_NUMBER_PARTY_ID - Performs party ids merge in
337 -- JTF_PERZ_QUERY_PARAM table for Customer Number saved searches.
338 -- Columns: PARAMETER_VALUE where PARAMETER_NAME='CUSTOMER_ID'
339 ------------------------------------------------------------------------------------------
340
341
342 PROCEDURE SEARCH_MERGE_NUMBER_PARTY_ID(
343 p_entity_name IN VARCHAR2,
344 p_from_id IN NUMBER,
345 x_to_id OUT NOCOPY NUMBER,
346 p_from_fk_id IN NUMBER,
347 p_to_fk_id IN NUMBER,
348 p_parent_entity_name IN VARCHAR2,
349 p_batch_id IN NUMBER,
353 ------------------------------------------------------------------------------------------
350 p_batch_party_id IN NUMBER,
351 x_return_status OUT NOCOPY VARCHAR2);
352
354 -- Procedure: SEARCH_MERGE_NAME_PARTY_ID - Performs party ids merge in
355 -- JTF_PERZ_QUERY_PARAM table for Customer Name saved searches.
356 -- Columns: PARAMETER_VALUE where PARAMETER_NAME='CUSTOMER_ID'
357 ------------------------------------------------------------------------------------------
358
359 PROCEDURE SEARCH_MERGE_NAME_PARTY_ID(
360 p_entity_name IN VARCHAR2,
361 p_from_id IN NUMBER,
362 x_to_id OUT NOCOPY NUMBER,
363 p_from_fk_id IN NUMBER,
364 p_to_fk_id IN NUMBER,
365 p_parent_entity_name IN VARCHAR2,
366 p_batch_id IN NUMBER,
367 p_batch_party_id IN NUMBER,
368 x_return_status OUT NOCOPY VARCHAR2);
369
370
371
372 ------------------------------------------------------------------------------------------
373 -- Procedure: TASK_ASSIGNMENTS_MERGE - Performs party ids merge in
374 -- JTF_TASK_ASSIGNMENTS table.
375 -- Columns: Updates RESOURCE_ID where RESOURCE_TYPE is of party type
376 ------------------------------------------------------------------------------------------
377
378 PROCEDURE TASK_ASSIGNMENTS_MERGE(
379 p_entity_name IN VARCHAR2,
380 p_from_id IN NUMBER,
381 x_to_id OUT NOCOPY NUMBER,
382 p_from_fk_id IN NUMBER,
383 p_to_fk_id IN NUMBER,
384 p_parent_entity_name IN VARCHAR2,
385 p_batch_id IN NUMBER,
386 p_batch_party_id IN NUMBER,
387 x_return_status OUT NOCOPY VARCHAR2);
388
389 END JTF_TASK_PARTY_MERGE_PKG;
390
[Home] [Help]