[Home] [Help]
PACKAGE BODY: APPS.HZ_MERGE_ENTITY_ATTRI_PVT
Source
1 PACKAGE BODY HZ_MERGE_ENTITY_ATTRI_PVT AS
2 /*$Header: ARHMPATB.pls 120.39.12010000.2 2008/09/09 07:06:12 kguggila ship $ */
3 PROCEDURE log(
4 message IN VARCHAR2,
5 newline IN BOOLEAN DEFAULT TRUE);
6
7 function get_party_last_upd_date(p_attribute_party_id in number, p_entity_name in varchar2) return date;
8
9 PROCEDURE log(
10 message IN VARCHAR2,
11 newline IN BOOLEAN DEFAULT TRUE) IS
12 BEGIN
13
14 IF message = 'NEWLINE' THEN
15 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
16 ELSIF (newline) THEN
17 FND_FILE.put_line(fnd_file.log,message);
18 ELSE
19 FND_FILE.put(fnd_file.log,message);
20 END IF;
21 END log;
22
23 function get_healthCarePartyId(p_merge_batch_id in number) return number is
24 cursor get_healthCarePartyId_csr is
25 SELECT PP.PARTY_ID
26 FROM HZ_PERSON_PROFILES PP,HZ_DUP_SETS DS, HZ_DUP_SET_PARTIES DSP, HZ_DUP_BATCH DB
27 WHERE PP.PARTY_ID =DSP.DUP_PARTY_ID
28 AND DB.DUP_BATCH_ID = DS.DUP_BATCH_ID
29 AND DS.DUP_SET_ID = DSP.DUP_SET_ID
30 AND sysdate between pp.effective_start_date and nvl(pp.effective_end_date,sysdate)
31 AND DSP.DUP_SET_ID= p_merge_batch_id
32 AND PP.CREATED_BY_MODULE = 'CTB_PERSON_REGISTRY_SERVICES'
33 AND NVL(DSP.MERGE_FLAG,'Y') <> 'N'
34 AND ROWNUM = 1;
35 l_party_id number;
36 begin
37 open get_healthCarePartyId_csr;
38 fetch get_healthCarePartyId_csr into l_party_id;
39 close get_healthCarePartyId_csr;
40 return l_party_id;
41
42 end get_healthCarePartyId;
43
44 function get_InternalFlagPartyId(p_merge_batch_id in number,p_entity_name in VARCHAR2) return number is
45 cursor get_PPInternalFlagPartyId_csr is
46 SELECT PP.PARTY_ID
47 FROM HZ_PERSON_PROFILES PP,HZ_DUP_SETS DS, HZ_DUP_SET_PARTIES DSP, HZ_DUP_BATCH DB
48 WHERE PP.PARTY_ID =DSP.DUP_PARTY_ID
49 AND DB.DUP_BATCH_ID = DS.DUP_BATCH_ID
50 AND DS.DUP_SET_ID = DSP.DUP_SET_ID
51 AND sysdate between pp.effective_start_date and nvl(pp.effective_end_date,sysdate)
52 AND DSP.DUP_SET_ID= p_merge_batch_id
53 AND PP.INTERNAL_FLAG = 'Y'
54 AND NVL(DSP.MERGE_FLAG,'Y') <> 'N'
55 AND ROWNUM = 1;
56 cursor get_OPInternalFlagPartyId_csr is
57 SELECT OP.PARTY_ID
58 FROM HZ_ORGANIZATION_PROFILES OP,HZ_DUP_SETS DS, HZ_DUP_SET_PARTIES DSP, HZ_DUP_BATCH DB
59 WHERE OP.PARTY_ID =DSP.DUP_PARTY_ID
60 AND DB.DUP_BATCH_ID = DS.DUP_BATCH_ID
61 AND DS.DUP_SET_ID = DSP.DUP_SET_ID
62 AND sysdate between op.effective_start_date and nvl(op.effective_end_date,sysdate)
63 AND DSP.DUP_SET_ID= p_merge_batch_id
64 AND OP.INTERNAL_FLAG = 'Y'
65 AND NVL(DSP.MERGE_FLAG,'Y') <> 'N'
66 AND ROWNUM = 1;
67
68 l_party_id number;
69 begin
70 if p_entity_name = 'HZ_PERSON_PROFILES' then
71 open get_PPInternalFlagPartyId_csr;
72 fetch get_PPInternalFlagPartyId_csr into l_party_id;
73 close get_PPInternalFlagPartyId_csr;
74 end if;
75 if p_entity_name = 'HZ_ORGANIZATION_PROFILES' then
76 open get_OPInternalFlagPartyId_csr;
77 fetch get_OPInternalFlagPartyId_csr into l_party_id;
78 close get_OPInternalFlagPartyId_csr;
79 end if;
80
81 return l_party_id;
82
83
84 end get_InternalFlagPartyId;
85
86 /* private procedures, called in do_profile_attribute_merge */
87 PROCEDURE construct_organization_rec(
88 p_merge_batch_id IN NUMBER,
89 p_merge_to_party_id IN NUMBER,
90 x_organization_rec OUT NOCOPY HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
91 x_return_status OUT NOCOPY VARCHAR2
92 ) is
93 cursor get_org_rec_csr is
94 select attribute_name, attribute_value, attribute_type, attribute_party_id
95 from HZ_MERGE_ENTITY_ATTRIBUTES
96 where merge_to_party_id = p_merge_to_party_id
97 and entity_name = 'HZ_ORGANIZATION_PROFILES'
98 and merge_batch_id = p_merge_batch_id;
99
100 l_attribute_name varchar2(30);
101 l_attribute_value varchar2(2000);
102 l_attribute_type varchar2(30);
103 l_attribute_party_id number;
104 l_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
105 l_attribute_date_value date; -- Bug No:3729832
106 begin
107 -- Initialize return status to SUCCESS
108 x_return_status := FND_API.G_RET_STS_SUCCESS;
109
110 open get_org_rec_csr;
111 loop
112 fetch get_org_rec_csr into l_attribute_name, l_attribute_value, l_attribute_type,l_attribute_party_id;
113 exit when get_org_rec_csr %NOTFOUND;
114 if l_attribute_type = 'D' then
115 --l_attribute_value := to_date(l_attribute_value,'YYYY/MM/DD');
116 l_attribute_date_value := to_date(l_attribute_value, 'YYYY/MM/DD'); -- Bug No:3729832
117 elsif l_attribute_type = 'N' then
118 l_attribute_value := to_number(l_attribute_value);
119 end if;
120
121 -- The following code is generated from fnd_columns, commented
122 -- out NOCOPY the columns are not supported in hz_party_v2pub.organization_rec
123 IF L_ATTRIBUTE_NAME = 'BEST_TIME_CONTACT_END' THEN
124 L_ORGANIZATION_REC.BEST_TIME_CONTACT_END := l_attribute_date_value;--L_ATTRIBUTE_VALUE; -- Bug No:3729832
125 ELSIF L_ATTRIBUTE_NAME = 'ORGANIZATION_NAME_PHONETIC' THEN
126 L_ORGANIZATION_REC.ORGANIZATION_NAME_PHONETIC := L_ATTRIBUTE_VALUE;
127 --ELSIF L_ATTRIBUTE_NAME = 'TAX_NAME' THEN
128 -- L_ORGANIZATION_REC.TAX_NAME := L_ATTRIBUTE_VALUE;
129 ELSIF L_ATTRIBUTE_NAME = 'TAX_REFERENCE' THEN
133 ELSIF L_ATTRIBUTE_NAME = 'JGZZ_FISCAL_CODE' THEN
130 L_ORGANIZATION_REC.TAX_REFERENCE := L_ATTRIBUTE_VALUE;
131 ELSIF L_ATTRIBUTE_NAME = 'GSA_INDICATOR_FLAG' THEN
132 L_ORGANIZATION_REC.GSA_INDICATOR_FLAG := L_ATTRIBUTE_VALUE;
134 L_ORGANIZATION_REC.JGZZ_FISCAL_CODE := L_ATTRIBUTE_VALUE;
135 ELSIF L_ATTRIBUTE_NAME = 'ANALYSIS_FY' THEN
136 L_ORGANIZATION_REC.ANALYSIS_FY := L_ATTRIBUTE_VALUE;
137 ELSIF L_ATTRIBUTE_NAME = 'FISCAL_YEAREND_MONTH' THEN
138 L_ORGANIZATION_REC.FISCAL_YEAREND_MONTH := L_ATTRIBUTE_VALUE;
139 ELSIF L_ATTRIBUTE_NAME = 'CURR_FY_POTENTIAL_REVENUE' THEN
140 L_ORGANIZATION_REC.CURR_FY_POTENTIAL_REVENUE := L_ATTRIBUTE_VALUE;
141 ELSIF L_ATTRIBUTE_NAME = 'MISSION_STATEMENT' THEN
142 L_ORGANIZATION_REC.MISSION_STATEMENT := L_ATTRIBUTE_VALUE;
143 ELSIF L_ATTRIBUTE_NAME = 'ORGANIZATION_TYPE' THEN
144 L_ORGANIZATION_REC.ORGANIZATION_TYPE := L_ATTRIBUTE_VALUE;
145 ELSIF L_ATTRIBUTE_NAME = 'BUSINESS_SCOPE' THEN
146 L_ORGANIZATION_REC.BUSINESS_SCOPE := L_ATTRIBUTE_VALUE;
147 ELSIF L_ATTRIBUTE_NAME = 'CORPORATION_CLASS' THEN
148 L_ORGANIZATION_REC.CORPORATION_CLASS := L_ATTRIBUTE_VALUE;
149 ELSIF L_ATTRIBUTE_NAME = 'KNOWN_AS' THEN
150 L_ORGANIZATION_REC.KNOWN_AS := L_ATTRIBUTE_VALUE;
151 ELSIF L_ATTRIBUTE_NAME = 'LOCAL_BUS_IDENTIFIER' THEN
152 L_ORGANIZATION_REC.LOCAL_BUS_IDENTIFIER := L_ATTRIBUTE_VALUE;
153 ELSIF L_ATTRIBUTE_NAME = 'PREF_FUNCTIONAL_CURRENCY' THEN
154 L_ORGANIZATION_REC.PREF_FUNCTIONAL_CURRENCY := L_ATTRIBUTE_VALUE;
155 ELSIF L_ATTRIBUTE_NAME = 'REGISTRATION_TYPE' THEN
156 L_ORGANIZATION_REC.REGISTRATION_TYPE := L_ATTRIBUTE_VALUE;
157 ELSIF L_ATTRIBUTE_NAME = 'TOTAL_EMPLOYEES_TEXT' THEN
158 L_ORGANIZATION_REC.TOTAL_EMPLOYEES_TEXT := L_ATTRIBUTE_VALUE;
159 ELSIF L_ATTRIBUTE_NAME = 'TOTAL_EMP_EST_IND' THEN
160 L_ORGANIZATION_REC.TOTAL_EMP_EST_IND := L_ATTRIBUTE_VALUE;
161 ELSIF L_ATTRIBUTE_NAME = 'INCORP_YEAR' THEN
162 L_ORGANIZATION_REC.INCORP_YEAR := L_ATTRIBUTE_VALUE;
163 --ELSIF L_ATTRIBUTE_NAME = 'PRIMARY_CONTACT_ID' THEN
164 -- L_ORGANIZATION_REC.PRIMARY_CONTACT_ID := L_ATTRIBUTE_VALUE;
165 /* ELSIF L_ATTRIBUTE_NAME = 'LAST_UPDATE_DATE' THEN
166 L_ORGANIZATION_REC.LAST_UPDATE_DATE := L_ATTRIBUTE_VALUE;
167 ELSIF L_ATTRIBUTE_NAME = 'CREATION_DATE' THEN
168 L_ORGANIZATION_REC.CREATION_DATE := L_ATTRIBUTE_VALUE;
169 ELSIF L_ATTRIBUTE_NAME = 'CREATED_BY' THEN
170 L_ORGANIZATION_REC.CREATED_BY := L_ATTRIBUTE_VALUE;
171 ELSIF L_ATTRIBUTE_NAME = 'LAST_UPDATE_LOGIN' THEN
172 L_ORGANIZATION_REC.LAST_UPDATE_LOGIN := L_ATTRIBUTE_VALUE;
173 ELSIF L_ATTRIBUTE_NAME = 'WH_UPDATE_DATE' THEN
174 L_ORGANIZATION_REC.WH_UPDATE_DATE := L_ATTRIBUTE_VALUE;
175 ELSIF L_ATTRIBUTE_NAME = 'PROGRAM_ID' THEN
176 L_ORGANIZATION_REC.PROGRAM_ID := L_ATTRIBUTE_VALUE;
177 ELSIF L_ATTRIBUTE_NAME = 'PROGRAM_UPDATE_DATE' THEN
178 L_ORGANIZATION_REC.PROGRAM_UPDATE_DATE := L_ATTRIBUTE_VALUE;
179 ELSIF L_ATTRIBUTE_NAME = 'CONTENT_SOURCE_TYPE' THEN
180 L_ORGANIZATION_REC.CONTENT_SOURCE_TYPE := L_ATTRIBUTE_VALUE;
181 */
182 ELSIF L_ATTRIBUTE_NAME = 'CONTENT_SOURCE_NUMBER' THEN
183 L_ORGANIZATION_REC.CONTENT_SOURCE_NUMBER := L_ATTRIBUTE_VALUE;
184 /* ELSIF L_ATTRIBUTE_NAME = 'EFFECTIVE_START_DATE' THEN
185 L_ORGANIZATION_REC.EFFECTIVE_START_DATE := L_ATTRIBUTE_VALUE;
186 ELSIF L_ATTRIBUTE_NAME = 'EFFECTIVE_END_DATE' THEN
187 L_ORGANIZATION_REC.EFFECTIVE_END_DATE := L_ATTRIBUTE_VALUE;
188 */
189 ELSIF L_ATTRIBUTE_NAME = 'SIC_CODE_TYPE' THEN
190 L_ORGANIZATION_REC.SIC_CODE_TYPE := L_ATTRIBUTE_VALUE;
191 ELSIF L_ATTRIBUTE_NAME = 'LOCAL_ACTIVITY_CODE' THEN
192 L_ORGANIZATION_REC.LOCAL_ACTIVITY_CODE := L_ATTRIBUTE_VALUE;
193 ELSIF L_ATTRIBUTE_NAME = 'EMP_AT_PRIMARY_ADR' THEN
194 L_ORGANIZATION_REC.EMP_AT_PRIMARY_ADR := L_ATTRIBUTE_VALUE;
195 ELSIF L_ATTRIBUTE_NAME = 'EMP_AT_PRIMARY_ADR_TEXT' THEN
196 L_ORGANIZATION_REC.EMP_AT_PRIMARY_ADR_TEXT := L_ATTRIBUTE_VALUE;
197 ELSIF L_ATTRIBUTE_NAME = 'EMP_AT_PRIMARY_ADR_EST_IND' THEN
198 L_ORGANIZATION_REC.EMP_AT_PRIMARY_ADR_EST_IND := L_ATTRIBUTE_VALUE;
199 ELSIF L_ATTRIBUTE_NAME = 'EMP_AT_PRIMARY_ADR_MIN_IND' THEN
200 L_ORGANIZATION_REC.EMP_AT_PRIMARY_ADR_MIN_IND := L_ATTRIBUTE_VALUE;
201 ELSIF L_ATTRIBUTE_NAME = 'AVG_HIGH_CREDIT' THEN
202 L_ORGANIZATION_REC.AVG_HIGH_CREDIT := L_ATTRIBUTE_VALUE;
203 ELSIF L_ATTRIBUTE_NAME = 'TOTAL_PAYMENTS' THEN
204 L_ORGANIZATION_REC.TOTAL_PAYMENTS := L_ATTRIBUTE_VALUE;
205 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE12' THEN
206 L_ORGANIZATION_REC.ATTRIBUTE12 := L_ATTRIBUTE_VALUE;
207 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE13' THEN
208 L_ORGANIZATION_REC.ATTRIBUTE13 := L_ATTRIBUTE_VALUE;
209 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE14' THEN
210 L_ORGANIZATION_REC.ATTRIBUTE14 := L_ATTRIBUTE_VALUE;
211 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE15' THEN
212 L_ORGANIZATION_REC.ATTRIBUTE15 := L_ATTRIBUTE_VALUE;
213 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE16' THEN
214 L_ORGANIZATION_REC.ATTRIBUTE16 := L_ATTRIBUTE_VALUE;
215 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE17' THEN
216 L_ORGANIZATION_REC.ATTRIBUTE17 := L_ATTRIBUTE_VALUE;
217 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE18' THEN
218 L_ORGANIZATION_REC.ATTRIBUTE18 := L_ATTRIBUTE_VALUE;
219 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE19' THEN
220 L_ORGANIZATION_REC.ATTRIBUTE19 := L_ATTRIBUTE_VALUE;
221 /* ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE_CATEGORY' THEN
222 L_ORGANIZATION_REC.GLOBAL_ATTRIBUTE_CATEGORY := L_ATTRIBUTE_VALUE;
223 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE1' THEN
224 L_ORGANIZATION_REC.GLOBAL_ATTRIBUTE1 := L_ATTRIBUTE_VALUE;
225 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE2' THEN
226 L_ORGANIZATION_REC.GLOBAL_ATTRIBUTE2 := L_ATTRIBUTE_VALUE;
227 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE3' THEN
228 L_ORGANIZATION_REC.GLOBAL_ATTRIBUTE3 := L_ATTRIBUTE_VALUE;
229 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE4' THEN
230 L_ORGANIZATION_REC.GLOBAL_ATTRIBUTE4 := L_ATTRIBUTE_VALUE;
231 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE5' THEN
232 L_ORGANIZATION_REC.GLOBAL_ATTRIBUTE5 := L_ATTRIBUTE_VALUE;
233 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE6' THEN
234 L_ORGANIZATION_REC.GLOBAL_ATTRIBUTE6 := L_ATTRIBUTE_VALUE;
235 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE7' THEN
236 L_ORGANIZATION_REC.GLOBAL_ATTRIBUTE7 := L_ATTRIBUTE_VALUE;
237 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE9' THEN
238 L_ORGANIZATION_REC.GLOBAL_ATTRIBUTE9 := L_ATTRIBUTE_VALUE;
239 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE10' THEN
240 L_ORGANIZATION_REC.GLOBAL_ATTRIBUTE10 := L_ATTRIBUTE_VALUE;
241 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE11' THEN
242 L_ORGANIZATION_REC.GLOBAL_ATTRIBUTE11 := L_ATTRIBUTE_VALUE;
243 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE13' THEN
244 L_ORGANIZATION_REC.GLOBAL_ATTRIBUTE13 := L_ATTRIBUTE_VALUE;
245 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE14' THEN
246 L_ORGANIZATION_REC.GLOBAL_ATTRIBUTE14 := L_ATTRIBUTE_VALUE;
247 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE16' THEN
248 L_ORGANIZATION_REC.GLOBAL_ATTRIBUTE16 := L_ATTRIBUTE_VALUE;
249 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE17' THEN
250 L_ORGANIZATION_REC.GLOBAL_ATTRIBUTE17 := L_ATTRIBUTE_VALUE;
251 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE19' THEN
252 L_ORGANIZATION_REC.GLOBAL_ATTRIBUTE19 := L_ATTRIBUTE_VALUE;
253 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE20' THEN
254 L_ORGANIZATION_REC.GLOBAL_ATTRIBUTE20 := L_ATTRIBUTE_VALUE; */
255 --ELSIF L_ATTRIBUTE_NAME = 'DUNS_NUMBER' THEN
256 -- L_ORGANIZATION_REC.DUNS_NUMBER := L_ATTRIBUTE_VALUE;
257 ELSIF L_ATTRIBUTE_NAME = 'ENQUIRY_DUNS' THEN
258 L_ORGANIZATION_REC.ENQUIRY_DUNS := L_ATTRIBUTE_VALUE;
259 ELSIF L_ATTRIBUTE_NAME = 'CEO_NAME' THEN
260 L_ORGANIZATION_REC.CEO_NAME := L_ATTRIBUTE_VALUE;
261 ELSIF L_ATTRIBUTE_NAME = 'CEO_TITLE' THEN
262 L_ORGANIZATION_REC.CEO_TITLE := L_ATTRIBUTE_VALUE;
263 ELSIF L_ATTRIBUTE_NAME = 'PRINCIPAL_NAME' THEN
264 L_ORGANIZATION_REC.PRINCIPAL_NAME := L_ATTRIBUTE_VALUE;
265 ELSIF L_ATTRIBUTE_NAME = 'PRINCIPAL_TITLE' THEN
266 L_ORGANIZATION_REC.PRINCIPAL_TITLE := L_ATTRIBUTE_VALUE;
267 ELSIF L_ATTRIBUTE_NAME = 'LEGAL_STATUS' THEN
268 L_ORGANIZATION_REC.LEGAL_STATUS := L_ATTRIBUTE_VALUE;
269 ELSIF L_ATTRIBUTE_NAME = 'CONTROL_YR' THEN
270 L_ORGANIZATION_REC.CONTROL_YR := L_ATTRIBUTE_VALUE;
271 ELSIF L_ATTRIBUTE_NAME = 'EMPLOYEES_TOTAL' THEN
272 L_ORGANIZATION_REC.EMPLOYEES_TOTAL := L_ATTRIBUTE_VALUE;
273 ELSIF L_ATTRIBUTE_NAME = 'BRANCH_FLAG' THEN
274 L_ORGANIZATION_REC.BRANCH_FLAG := L_ATTRIBUTE_VALUE;
275 ELSIF L_ATTRIBUTE_NAME = 'OOB_IND' THEN
276 L_ORGANIZATION_REC.OOB_IND := L_ATTRIBUTE_VALUE;
277 ELSIF L_ATTRIBUTE_NAME = 'LINE_OF_BUSINESS' THEN
278 L_ORGANIZATION_REC.LINE_OF_BUSINESS := L_ATTRIBUTE_VALUE;
279 ELSIF L_ATTRIBUTE_NAME = 'CONG_DIST_CODE' THEN
280 L_ORGANIZATION_REC.CONG_DIST_CODE := L_ATTRIBUTE_VALUE;
281 ELSIF L_ATTRIBUTE_NAME = 'SIC_CODE' THEN
282 L_ORGANIZATION_REC.SIC_CODE := L_ATTRIBUTE_VALUE;
283 ELSIF L_ATTRIBUTE_NAME = 'IMPORT_IND' THEN
284 L_ORGANIZATION_REC.IMPORT_IND := L_ATTRIBUTE_VALUE;
285 ELSIF L_ATTRIBUTE_NAME = 'EXPORT_IND' THEN
286 L_ORGANIZATION_REC.EXPORT_IND := L_ATTRIBUTE_VALUE;
287 ELSIF L_ATTRIBUTE_NAME = 'DEBARMENT_IND' THEN
288 L_ORGANIZATION_REC.DEBARMENT_IND := L_ATTRIBUTE_VALUE;
289 ELSIF L_ATTRIBUTE_NAME = 'MINORITY_OWNED_TYPE' THEN
290 L_ORGANIZATION_REC.MINORITY_OWNED_TYPE := L_ATTRIBUTE_VALUE;
291 ELSIF L_ATTRIBUTE_NAME = 'WOMAN_OWNED_IND' THEN
292 L_ORGANIZATION_REC.WOMAN_OWNED_IND := L_ATTRIBUTE_VALUE;
293 ELSIF L_ATTRIBUTE_NAME = 'DISADV_8A_IND' THEN
294 L_ORGANIZATION_REC.DISADV_8A_IND := L_ATTRIBUTE_VALUE;
295 ELSIF L_ATTRIBUTE_NAME = 'SMALL_BUS_IND' THEN
296 L_ORGANIZATION_REC.SMALL_BUS_IND := L_ATTRIBUTE_VALUE;
297 ELSIF L_ATTRIBUTE_NAME = 'DEBARMENTS_COUNT' THEN
298 L_ORGANIZATION_REC.DEBARMENTS_COUNT := L_ATTRIBUTE_VALUE;
299 ELSIF L_ATTRIBUTE_NAME = 'DEBARMENTS_DATE' THEN
300 L_ORGANIZATION_REC.DEBARMENTS_DATE := l_attribute_date_value;--L_ATTRIBUTE_VALUE; -- Bug No:3729832
301 ELSIF L_ATTRIBUTE_NAME = 'FAILURE_SCORE' THEN
302 L_ORGANIZATION_REC.FAILURE_SCORE := L_ATTRIBUTE_VALUE;
303 --ELSIF L_ATTRIBUTE_NAME = 'FAILURE_SCORE_NATL_PERCENTILE' THEN --Commented out as this is an obsoleted column
304 --L_ORGANIZATION_REC.FAILURE_SCORE_NATNL_PERCENTILE := L_ATTRIBUTE_VALUE;
305 ELSIF L_ATTRIBUTE_NAME = 'FAILURE_SCORE_OVERRIDE_CODE' THEN
306 L_ORGANIZATION_REC.FAILURE_SCORE_OVERRIDE_CODE := L_ATTRIBUTE_VALUE;
307 ELSIF L_ATTRIBUTE_NAME = 'FAILURE_SCORE_COMMENTARY' THEN
308 L_ORGANIZATION_REC.FAILURE_SCORE_COMMENTARY := L_ATTRIBUTE_VALUE;
309 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_FAILURE_SCORE' THEN
310 L_ORGANIZATION_REC.GLOBAL_FAILURE_SCORE := L_ATTRIBUTE_VALUE;
311 ELSIF L_ATTRIBUTE_NAME = 'DB_RATING' THEN
312 L_ORGANIZATION_REC.DB_RATING := L_ATTRIBUTE_VALUE;
313 ELSIF L_ATTRIBUTE_NAME = 'CREDIT_SCORE' THEN
314 L_ORGANIZATION_REC.CREDIT_SCORE := L_ATTRIBUTE_VALUE;
315 ELSIF L_ATTRIBUTE_NAME = 'CREDIT_SCORE_COMMENTARY' THEN
316 L_ORGANIZATION_REC.CREDIT_SCORE_COMMENTARY := L_ATTRIBUTE_VALUE;
317 ELSIF L_ATTRIBUTE_NAME = 'PAYDEX_SCORE' THEN
318 L_ORGANIZATION_REC.PAYDEX_SCORE := L_ATTRIBUTE_VALUE;
319 ELSIF L_ATTRIBUTE_NAME = 'BEST_TIME_CONTACT_BEGIN' THEN
320 L_ORGANIZATION_REC.BEST_TIME_CONTACT_BEGIN := l_attribute_date_value;--L_ATTRIBUTE_VALUE; -- Bug No:3729832
321 --ELSIF L_ATTRIBUTE_NAME = 'ORGANIZATION_PROFILE_ID' THEN
322 -- L_ORGANIZATION_REC.ORGANIZATION_PROFILE_ID := L_ATTRIBUTE_VALUE;
323 --ELSIF L_ATTRIBUTE_NAME = 'PARTY_ID' THEN
324 -- L_ORGANIZATION_REC.PARTY_REC.PARTY_ID := L_ATTRIBUTE_VALUE;
325 ELSIF L_ATTRIBUTE_NAME = 'ORGANIZATION_NAME' THEN
326 L_ORGANIZATION_REC.ORGANIZATION_NAME := L_ATTRIBUTE_VALUE;
327 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE_CATEGORY' THEN
328 L_ORGANIZATION_REC.ATTRIBUTE_CATEGORY := L_ATTRIBUTE_VALUE;
329 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE1' THEN
330 L_ORGANIZATION_REC.ATTRIBUTE1 := L_ATTRIBUTE_VALUE;
331 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE2' THEN
332 L_ORGANIZATION_REC.ATTRIBUTE2 := L_ATTRIBUTE_VALUE;
333 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE3' THEN
334 L_ORGANIZATION_REC.ATTRIBUTE3 := L_ATTRIBUTE_VALUE;
335 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE4' THEN
336 L_ORGANIZATION_REC.ATTRIBUTE4 := L_ATTRIBUTE_VALUE;
337 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE5' THEN
338 L_ORGANIZATION_REC.ATTRIBUTE5 := L_ATTRIBUTE_VALUE;
339 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE6' THEN
340 L_ORGANIZATION_REC.ATTRIBUTE6 := L_ATTRIBUTE_VALUE;
341 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE7' THEN
342 L_ORGANIZATION_REC.ATTRIBUTE7 := L_ATTRIBUTE_VALUE;
343 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE8' THEN
344 L_ORGANIZATION_REC.ATTRIBUTE8 := L_ATTRIBUTE_VALUE;
345 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE9' THEN
346 L_ORGANIZATION_REC.ATTRIBUTE9 := L_ATTRIBUTE_VALUE;
347 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE10' THEN
348 L_ORGANIZATION_REC.ATTRIBUTE10 := L_ATTRIBUTE_VALUE;
349 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE11' THEN
350 L_ORGANIZATION_REC.ATTRIBUTE11 := L_ATTRIBUTE_VALUE;
351 ELSIF L_ATTRIBUTE_NAME = 'CREDIT_SCORE_AGE' THEN
352 L_ORGANIZATION_REC.CREDIT_SCORE_AGE := L_ATTRIBUTE_VALUE;
353 ELSIF L_ATTRIBUTE_NAME = 'CREDIT_SCORE_CLASS' THEN
354 L_ORGANIZATION_REC.CREDIT_SCORE_CLASS := L_ATTRIBUTE_VALUE;
355 ELSIF L_ATTRIBUTE_NAME = 'CREDIT_SCORE_COMMENTARY10' THEN
356 L_ORGANIZATION_REC.CREDIT_SCORE_COMMENTARY10 := L_ATTRIBUTE_VALUE;
357 ELSIF L_ATTRIBUTE_NAME = 'CREDIT_SCORE_COMMENTARY2' THEN
358 L_ORGANIZATION_REC.CREDIT_SCORE_COMMENTARY2 := L_ATTRIBUTE_VALUE;
359 ELSIF L_ATTRIBUTE_NAME = 'CREDIT_SCORE_COMMENTARY3' THEN
360 L_ORGANIZATION_REC.CREDIT_SCORE_COMMENTARY3 := L_ATTRIBUTE_VALUE;
361 ELSIF L_ATTRIBUTE_NAME = 'CREDIT_SCORE_COMMENTARY4' THEN
362 L_ORGANIZATION_REC.CREDIT_SCORE_COMMENTARY4 := L_ATTRIBUTE_VALUE;
363 ELSIF L_ATTRIBUTE_NAME = 'CREDIT_SCORE_COMMENTARY5' THEN
364 L_ORGANIZATION_REC.CREDIT_SCORE_COMMENTARY5 := L_ATTRIBUTE_VALUE;
365 ELSIF L_ATTRIBUTE_NAME = 'CREDIT_SCORE_COMMENTARY6' THEN
366 L_ORGANIZATION_REC.CREDIT_SCORE_COMMENTARY6 := L_ATTRIBUTE_VALUE;
367 ELSIF L_ATTRIBUTE_NAME = 'CREDIT_SCORE_COMMENTARY7' THEN
368 L_ORGANIZATION_REC.CREDIT_SCORE_COMMENTARY7 := L_ATTRIBUTE_VALUE;
369 ELSIF L_ATTRIBUTE_NAME = 'CREDIT_SCORE_COMMENTARY8' THEN
370 L_ORGANIZATION_REC.CREDIT_SCORE_COMMENTARY8 := L_ATTRIBUTE_VALUE;
371 ELSIF L_ATTRIBUTE_NAME = 'CREDIT_SCORE_COMMENTARY9' THEN
372 L_ORGANIZATION_REC.CREDIT_SCORE_COMMENTARY9 := L_ATTRIBUTE_VALUE;
373 ELSIF L_ATTRIBUTE_NAME = 'CREDIT_SCORE_DATE' THEN
374 L_ORGANIZATION_REC.CREDIT_SCORE_DATE := l_attribute_date_value;--L_ATTRIBUTE_VALUE; -- Bug No:3729832
375 ELSIF L_ATTRIBUTE_NAME = 'CREDIT_SCORE_INCD_DEFAULT' THEN
376 L_ORGANIZATION_REC.CREDIT_SCORE_INCD_DEFAULT := L_ATTRIBUTE_VALUE;
377 ELSIF L_ATTRIBUTE_NAME = 'CREDIT_SCORE_NATL_PERCENTILE' THEN
378 L_ORGANIZATION_REC.CREDIT_SCORE_NATL_PERCENTILE := L_ATTRIBUTE_VALUE;
379 ELSIF L_ATTRIBUTE_NAME = 'DISPLAYED_DUNS_PARTY_ID' THEN
380 L_ORGANIZATION_REC.DISPLAYED_DUNS_PARTY_ID := L_ATTRIBUTE_VALUE;
381 ELSIF L_ATTRIBUTE_NAME = 'FAILURE_SCORE_AGE' THEN
382 L_ORGANIZATION_REC.FAILURE_SCORE_AGE := L_ATTRIBUTE_VALUE;
383 ELSIF L_ATTRIBUTE_NAME = 'FAILURE_SCORE_CLASS' THEN
384 L_ORGANIZATION_REC.FAILURE_SCORE_CLASS := L_ATTRIBUTE_VALUE;
385 ELSIF L_ATTRIBUTE_NAME = 'FAILURE_SCORE_COMMENTARY10' THEN
386 L_ORGANIZATION_REC.FAILURE_SCORE_COMMENTARY10 := L_ATTRIBUTE_VALUE;
387 ELSIF L_ATTRIBUTE_NAME = 'FAILURE_SCORE_COMMENTARY2' THEN
388 L_ORGANIZATION_REC.FAILURE_SCORE_COMMENTARY2 := L_ATTRIBUTE_VALUE;
389 ELSIF L_ATTRIBUTE_NAME = 'FAILURE_SCORE_COMMENTARY3' THEN
390 L_ORGANIZATION_REC.FAILURE_SCORE_COMMENTARY3 := L_ATTRIBUTE_VALUE;
391 ELSIF L_ATTRIBUTE_NAME = 'FAILURE_SCORE_COMMENTARY4' THEN
392 L_ORGANIZATION_REC.FAILURE_SCORE_COMMENTARY4 := L_ATTRIBUTE_VALUE;
393 ELSIF L_ATTRIBUTE_NAME = 'FAILURE_SCORE_COMMENTARY5' THEN
394 L_ORGANIZATION_REC.FAILURE_SCORE_COMMENTARY5 := L_ATTRIBUTE_VALUE;
395 ELSIF L_ATTRIBUTE_NAME = 'FAILURE_SCORE_COMMENTARY6' THEN
396 L_ORGANIZATION_REC.FAILURE_SCORE_COMMENTARY6 := L_ATTRIBUTE_VALUE;
397 ELSIF L_ATTRIBUTE_NAME = 'FAILURE_SCORE_COMMENTARY7' THEN
398 L_ORGANIZATION_REC.FAILURE_SCORE_COMMENTARY7 := L_ATTRIBUTE_VALUE;
399 ELSIF L_ATTRIBUTE_NAME = 'FAILURE_SCORE_COMMENTARY8' THEN
400 L_ORGANIZATION_REC.FAILURE_SCORE_COMMENTARY8 := L_ATTRIBUTE_VALUE;
401 ELSIF L_ATTRIBUTE_NAME = 'FAILURE_SCORE_COMMENTARY9' THEN
402 L_ORGANIZATION_REC.FAILURE_SCORE_COMMENTARY9 := L_ATTRIBUTE_VALUE;
403 ELSIF L_ATTRIBUTE_NAME = 'FAILURE_SCORE_DATE' THEN
404 L_ORGANIZATION_REC.FAILURE_SCORE_DATE := l_attribute_date_value; --L_ATTRIBUTE_VALUE; -- Bug No:3729832
405 ELSIF L_ATTRIBUTE_NAME = 'FAILURE_SCORE_INCD_DEFAULT' THEN
406 L_ORGANIZATION_REC.FAILURE_SCORE_INCD_DEFAULT := L_ATTRIBUTE_VALUE;
407 ELSIF L_ATTRIBUTE_NAME = 'FAILURE_SCORE_NATNL_PERCENTILE' THEN
408 L_ORGANIZATION_REC.FAILURE_SCORE_NATNL_PERCENTILE := L_ATTRIBUTE_VALUE;
409 /* ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE12' THEN
410 L_ORGANIZATION_REC.GLOBAL_ATTRIBUTE12 := L_ATTRIBUTE_VALUE;
411 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE15' THEN
412 L_ORGANIZATION_REC.GLOBAL_ATTRIBUTE15 := L_ATTRIBUTE_VALUE;
413 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE18' THEN
414 L_ORGANIZATION_REC.GLOBAL_ATTRIBUTE18 := L_ATTRIBUTE_VALUE;
415 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE8' THEN
416 L_ORGANIZATION_REC.GLOBAL_ATTRIBUTE8 := L_ATTRIBUTE_VALUE;
417 */
418 ELSIF L_ATTRIBUTE_NAME = 'HIGH_CREDIT' THEN
419 L_ORGANIZATION_REC.HIGH_CREDIT := L_ATTRIBUTE_VALUE;
420 ELSIF L_ATTRIBUTE_NAME = 'HQ_BRANCH_IND' THEN
421 L_ORGANIZATION_REC.HQ_BRANCH_IND := L_ATTRIBUTE_VALUE;
422 ELSIF L_ATTRIBUTE_NAME = 'INTERNAL_FLAG' THEN
423 L_ORGANIZATION_REC.INTERNAL_FLAG := L_ATTRIBUTE_VALUE;
424 ELSIF L_ATTRIBUTE_NAME = 'KNOWN_AS2' THEN
425 L_ORGANIZATION_REC.KNOWN_AS2 := L_ATTRIBUTE_VALUE;
426 ELSIF L_ATTRIBUTE_NAME = 'KNOWN_AS3' THEN
427 L_ORGANIZATION_REC.KNOWN_AS3 := L_ATTRIBUTE_VALUE;
428 ELSIF L_ATTRIBUTE_NAME = 'KNOWN_AS4' THEN
429 L_ORGANIZATION_REC.KNOWN_AS4 := L_ATTRIBUTE_VALUE;
430 ELSIF L_ATTRIBUTE_NAME = 'KNOWN_AS5' THEN
431 L_ORGANIZATION_REC.KNOWN_AS5 := L_ATTRIBUTE_VALUE;
432 ELSIF L_ATTRIBUTE_NAME = 'LABOR_SURPLUS_IND' THEN
433 L_ORGANIZATION_REC.LABOR_SURPLUS_IND := L_ATTRIBUTE_VALUE;
434 --ELSIF L_ATTRIBUTE_NAME = 'LAST_UPDATED_BY' THEN
435 -- L_ORGANIZATION_REC.LAST_UPDATED_BY := L_ATTRIBUTE_VALUE;
436 ELSIF L_ATTRIBUTE_NAME = 'LOCAL_ACTIVITY_CODE_TYPE' THEN
437 L_ORGANIZATION_REC.LOCAL_ACTIVITY_CODE_TYPE := L_ATTRIBUTE_VALUE;
438 ELSIF L_ATTRIBUTE_NAME = 'LOCAL_BUS_IDEN_TYPE' THEN
439 L_ORGANIZATION_REC.LOCAL_BUS_IDEN_TYPE := L_ATTRIBUTE_VALUE;
440 ELSIF L_ATTRIBUTE_NAME = 'MAXIMUM_CREDIT_CURRENCY_CODE' THEN
441 L_ORGANIZATION_REC.MAXIMUM_CREDIT_CURRENCY_CODE := L_ATTRIBUTE_VALUE;
442 ELSIF L_ATTRIBUTE_NAME = 'MAXIMUM_CREDIT_RECOMMENDATION' THEN
443 L_ORGANIZATION_REC.MAXIMUM_CREDIT_RECOMMENDATION := L_ATTRIBUTE_VALUE;
444 ELSIF L_ATTRIBUTE_NAME = 'MINORITY_OWNED_IND' THEN
445 L_ORGANIZATION_REC.MINORITY_OWNED_IND := L_ATTRIBUTE_VALUE;
446 ELSIF L_ATTRIBUTE_NAME = 'NEXT_FY_POTENTIAL_REVENUE' THEN
447 L_ORGANIZATION_REC.NEXT_FY_POTENTIAL_REVENUE := L_ATTRIBUTE_VALUE;
448 ELSIF L_ATTRIBUTE_NAME = 'PARENT_SUB_IND' THEN
449 L_ORGANIZATION_REC.PARENT_SUB_IND := L_ATTRIBUTE_VALUE;
450 ELSIF L_ATTRIBUTE_NAME = 'PAYDEX_NORM' THEN
451 L_ORGANIZATION_REC.PAYDEX_NORM := L_ATTRIBUTE_VALUE;
452 ELSIF L_ATTRIBUTE_NAME = 'PAYDEX_THREE_MONTHS_AGO' THEN
453 L_ORGANIZATION_REC.PAYDEX_THREE_MONTHS_AGO := L_ATTRIBUTE_VALUE;
454 --ELSIF L_ATTRIBUTE_NAME = 'PROGRAM_APPLICATION_ID' THEN
455 -- L_ORGANIZATION_REC.PROGRAM_APPLICATION_ID := L_ATTRIBUTE_VALUE;
456 ELSIF L_ATTRIBUTE_NAME = 'PUBLIC_PRIVATE_OWNERSHIP_FLAG' THEN
457 L_ORGANIZATION_REC.PUBLIC_PRIVATE_OWNERSHIP_FLAG := L_ATTRIBUTE_VALUE;
458 ELSIF L_ATTRIBUTE_NAME = 'RENT_OWN_IND' THEN
459 L_ORGANIZATION_REC.RENT_OWN_IND := L_ATTRIBUTE_VALUE;
460 --ELSIF L_ATTRIBUTE_NAME = 'REQUEST_ID' THEN
461 -- L_ORGANIZATION_REC.REQUEST_ID := L_ATTRIBUTE_VALUE;
462 ELSIF L_ATTRIBUTE_NAME = 'TOTAL_EMPLOYEES_IND' THEN
463 L_ORGANIZATION_REC.TOTAL_EMPLOYEES_IND := L_ATTRIBUTE_VALUE;
464 ELSIF L_ATTRIBUTE_NAME = 'TOTAL_EMP_MIN_IND' THEN
465 L_ORGANIZATION_REC.TOTAL_EMP_MIN_IND := L_ATTRIBUTE_VALUE;
466 ELSIF L_ATTRIBUTE_NAME = 'YEAR_ESTABLISHED' THEN
467 L_ORGANIZATION_REC.YEAR_ESTABLISHED := L_ATTRIBUTE_VALUE;
468 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE20' THEN
469 L_ORGANIZATION_REC.ATTRIBUTE20 := L_ATTRIBUTE_VALUE;
470 --ELSIF L_ATTRIBUTE_NAME = 'BUSINESS_REPORT' THEN
471 --ELSIF L_ATTRIBUTE_NAME = 'STATUS' THEN
472 -- L_ORGANIZATION_REC.STATUS := L_ATTRIBUTE_VALUE;
476 -- L_ORGANIZATION_REC.CREATED_BY_MODULE := L_ATTRIBUTE_VALUE;
473 --ELSIF L_ATTRIBUTE_NAME = 'APPLICATION_ID' THEN
474 -- L_ORGANIZATION_REC.APPLICATION_ID := L_ATTRIBUTE_VALUE;
475 --ELSIF L_ATTRIBUTE_NAME = 'CREATED_BY_MODULE' THEN
477 ELSIF L_ATTRIBUTE_NAME = 'DUNS_NUMBER_C' THEN
478 L_ORGANIZATION_REC.DUNS_NUMBER_C := L_ATTRIBUTE_VALUE;
479 --ELSIF L_ATTRIBUTE_NAME = 'OBJECT_VERSION_NUMBER' THEN
480 -- L_ORGANIZATION_REC.OBJECT_VERSION_NUMBER := L_ATTRIBUTE_VALUE;
481 END IF;
482
483 End loop;
484 Close get_org_rec_csr;
485 L_ORGANIZATION_REC.PARTY_REC.PARTY_ID := P_MERGE_TO_PARTY_ID;
486 X_organization_rec := l_organization_rec;
487 EXCEPTION
488 WHEN OTHERS THEN
489 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
490 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
491 FND_MSG_PUB.ADD;
492 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
493
494 End construct_organization_rec;
495
496 /* private procedures, called in do_profile_attribute_merge */
497 PROCEDURE construct_person_rec(
498 p_merge_batch_id IN NUMBER,
499 p_merge_to_party_id IN NUMBER,
500 p_orig_sys_ref IN VARCHAR2,
501 x_person_rec OUT NOCOPY HZ_PARTY_V2PUB.PERSON_REC_TYPE,
502 x_return_status OUT NOCOPY VARCHAR2
503 ) is
504 cursor get_person_rec_csr is
505 select attribute_name, attribute_value, attribute_type
506 from HZ_MERGE_ENTITY_ATTRIBUTES
507 where merge_to_party_id = p_merge_to_party_id
508 and entity_name = 'HZ_PERSON_PROFILES'
509 and merge_batch_id = p_merge_batch_id;
510
511 l_attribute_name varchar2(30);
512 l_attribute_value varchar2(2000);
513 l_attribute_type varchar2(30);
514 l_person_rec HZ_PARTY_V2PUB.PERSON_REC_TYPE;
515 l_attribute_date_value date; -- Bug No:3729832
516 begin
517 -- Initialize return status to SUCCESS
518 x_return_status := FND_API.G_RET_STS_SUCCESS;
519
520 open get_person_rec_csr;
521 loop
522 fetch get_person_rec_csr into l_attribute_name, l_attribute_value, l_attribute_type;
523 exit when get_person_rec_csr %NOTFOUND;
524
525 if l_attribute_type = 'D' then
526 --l_attribute_value := to_date(l_attribute_value,'YYYY/MM/DD');
527 l_attribute_date_value := to_date(l_attribute_value, 'YYYY/MM/DD'); -- Bug No:3729832
528 elsif l_attribute_type = 'N' then
529 l_attribute_value := to_number(l_attribute_value);
530 end if;
531
532 -- The following code is generated from fnd_columns, commented
533 -- out NOCOPY the columns are not supported in hz_party_v2pub.person_rec
534
535 IF (NVL(p_orig_sys_ref,'X') LIKE 'PER:%') THEN -- bug no: 4193165
536
537 IF (NVL(FND_PROFILE.VALUE('HZ_CREATED_BY_MODULE'), '-222') <> 'HR API') THEN
538
539 IF (NVL(FND_PROFILE.VALUE('HZ_PROTECT_HR_PERSON_INFO'),'N') ='Y') THEN --Update HR sensitive columns
540
541 IF L_ATTRIBUTE_NAME = 'GENDER' THEN
542 L_PERSON_REC.GENDER := L_ATTRIBUTE_VALUE;
543 ELSIF L_ATTRIBUTE_NAME = 'DATE_OF_BIRTH' THEN
544 L_PERSON_REC.DATE_OF_BIRTH := l_attribute_date_value; -- L_ATTRIBUTE_VALUE; -- Bug No:3729832
545 ELSIF L_ATTRIBUTE_NAME = 'PLACE_OF_BIRTH' THEN
546 L_PERSON_REC.PLACE_OF_BIRTH := L_ATTRIBUTE_VALUE;
547 ELSIF L_ATTRIBUTE_NAME = 'MARITAL_STATUS' THEN
548 L_PERSON_REC.MARITAL_STATUS := L_ATTRIBUTE_VALUE;
549 END IF;
550
551 END IF;
552
553 ELSE --Only HR
554 IF L_ATTRIBUTE_NAME = 'PERSON_FIRST_NAME' THEN
555 L_PERSON_REC.PERSON_FIRST_NAME := L_ATTRIBUTE_VALUE;
556 ELSIF L_ATTRIBUTE_NAME = 'PERSON_LAST_NAME' THEN
557 L_PERSON_REC.PERSON_LAST_NAME := L_ATTRIBUTE_VALUE;
558 ELSIF L_ATTRIBUTE_NAME = 'PERSON_MIDDLE_NAME' THEN
559 L_PERSON_REC.PERSON_MIDDLE_NAME := L_ATTRIBUTE_VALUE;
560 ELSIF L_ATTRIBUTE_NAME = 'PERSON_NAME_SUFFIX' THEN
561 L_PERSON_REC.PERSON_NAME_SUFFIX := L_ATTRIBUTE_VALUE;
562 ELSIF L_ATTRIBUTE_NAME = 'PERSON_PREVIOUS_LAST_NAME' THEN
563 L_PERSON_REC.PERSON_PREVIOUS_LAST_NAME := L_ATTRIBUTE_VALUE;
564 ELSIF L_ATTRIBUTE_NAME = 'PERSON_TITLE' THEN
565 L_PERSON_REC.PERSON_TITLE := L_ATTRIBUTE_VALUE;
566 ELSIF L_ATTRIBUTE_NAME = 'KNOWN_AS' THEN
567 L_PERSON_REC.KNOWN_AS := L_ATTRIBUTE_VALUE;
568 ELSIF L_ATTRIBUTE_NAME = 'PERSON_FIRST_NAME_PHONETIC' THEN
569 L_PERSON_REC.PERSON_FIRST_NAME_PHONETIC := L_ATTRIBUTE_VALUE;
570 ELSIF L_ATTRIBUTE_NAME = 'PERSON_LAST_NAME_PHONETIC' THEN
571 L_PERSON_REC.PERSON_LAST_NAME_PHONETIC := L_ATTRIBUTE_VALUE;
572 ELSIF L_ATTRIBUTE_NAME = 'PERSON_NAME_PHONETIC' THEN
573 L_PERSON_REC.PERSON_NAME_PHONETIC := L_ATTRIBUTE_VALUE;
574 ELSIF L_ATTRIBUTE_NAME = 'GENDER' THEN
575 L_PERSON_REC.GENDER := L_ATTRIBUTE_VALUE;
576 ELSIF L_ATTRIBUTE_NAME = 'DATE_OF_BIRTH' THEN
577 L_PERSON_REC.DATE_OF_BIRTH := l_attribute_date_value;
578 ELSIF L_ATTRIBUTE_NAME = 'PLACE_OF_BIRTH' THEN
579 L_PERSON_REC.PLACE_OF_BIRTH := L_ATTRIBUTE_VALUE;
580 ELSIF L_ATTRIBUTE_NAME = 'MARITAL_STATUS' THEN
581 L_PERSON_REC.MARITAL_STATUS := L_ATTRIBUTE_VALUE;
582 END IF;
583
584 END IF; --For IF (NVL(FND_PROFILE.VALUE('HZ_CREATED_BY_MODULE'), '-222') <> 'HR API')
585
586 ELSE --orig_sys_ref other than 'PER:%'
587 IF L_ATTRIBUTE_NAME = 'PERSON_FIRST_NAME' THEN
588 L_PERSON_REC.PERSON_FIRST_NAME := L_ATTRIBUTE_VALUE;
589 ELSIF L_ATTRIBUTE_NAME = 'PERSON_LAST_NAME' THEN
590 L_PERSON_REC.PERSON_LAST_NAME := L_ATTRIBUTE_VALUE;
591 ELSIF L_ATTRIBUTE_NAME = 'PERSON_MIDDLE_NAME' THEN
592 L_PERSON_REC.PERSON_MIDDLE_NAME := L_ATTRIBUTE_VALUE;
593 ELSIF L_ATTRIBUTE_NAME = 'PERSON_NAME_SUFFIX' THEN
594 L_PERSON_REC.PERSON_NAME_SUFFIX := L_ATTRIBUTE_VALUE;
595 ELSIF L_ATTRIBUTE_NAME = 'PERSON_PREVIOUS_LAST_NAME' THEN
596 L_PERSON_REC.PERSON_PREVIOUS_LAST_NAME := L_ATTRIBUTE_VALUE;
597 ELSIF L_ATTRIBUTE_NAME = 'PERSON_TITLE' THEN
598 L_PERSON_REC.PERSON_TITLE := L_ATTRIBUTE_VALUE;
599 ELSIF L_ATTRIBUTE_NAME = 'KNOWN_AS' THEN
600 L_PERSON_REC.KNOWN_AS := L_ATTRIBUTE_VALUE;
601 ELSIF L_ATTRIBUTE_NAME = 'PERSON_FIRST_NAME_PHONETIC' THEN
602 L_PERSON_REC.PERSON_FIRST_NAME_PHONETIC := L_ATTRIBUTE_VALUE;
603 ELSIF L_ATTRIBUTE_NAME = 'PERSON_LAST_NAME_PHONETIC' THEN
604 L_PERSON_REC.PERSON_LAST_NAME_PHONETIC := L_ATTRIBUTE_VALUE;
605 ELSIF L_ATTRIBUTE_NAME = 'PERSON_NAME_PHONETIC' THEN
606 L_PERSON_REC.PERSON_NAME_PHONETIC := L_ATTRIBUTE_VALUE;
607 ELSIF L_ATTRIBUTE_NAME = 'GENDER' THEN
608 L_PERSON_REC.GENDER := L_ATTRIBUTE_VALUE;
609 ELSIF L_ATTRIBUTE_NAME = 'DATE_OF_BIRTH' THEN
610 L_PERSON_REC.DATE_OF_BIRTH := l_attribute_date_value;
611 ELSIF L_ATTRIBUTE_NAME = 'PLACE_OF_BIRTH' THEN
612 L_PERSON_REC.PLACE_OF_BIRTH := L_ATTRIBUTE_VALUE;
613 ELSIF L_ATTRIBUTE_NAME = 'MARITAL_STATUS' THEN
614 L_PERSON_REC.MARITAL_STATUS := L_ATTRIBUTE_VALUE;
615 END IF;
616
617 END IF; -- For IF (NVL(p_orig_sys_ref,'X') LIKE 'PER:%')
618
619 --update other attributes
620 IF L_ATTRIBUTE_NAME = 'MARITAL_STATUS_EFFECTIVE_DATE' THEN
621 L_PERSON_REC.MARITAL_STATUS_EFFECTIVE_DATE := l_attribute_date_value; --L_ATTRIBUTE_VALUE; -- Bug No:3729832
622 ELSIF L_ATTRIBUTE_NAME = 'PERSONAL_INCOME' THEN
623 L_PERSON_REC.PERSONAL_INCOME := L_ATTRIBUTE_VALUE;
624 ELSIF L_ATTRIBUTE_NAME = 'HEAD_OF_HOUSEHOLD_FLAG' THEN
625 L_PERSON_REC.HEAD_OF_HOUSEHOLD_FLAG := L_ATTRIBUTE_VALUE;
626 ELSIF L_ATTRIBUTE_NAME = 'HOUSEHOLD_INCOME' THEN
627 L_PERSON_REC.HOUSEHOLD_INCOME := L_ATTRIBUTE_VALUE;
628 ELSIF L_ATTRIBUTE_NAME = 'HOUSEHOLD_SIZE' THEN
629 L_PERSON_REC.HOUSEHOLD_SIZE := L_ATTRIBUTE_VALUE;
630 ELSIF L_ATTRIBUTE_NAME = 'RENT_OWN_IND' THEN
631 L_PERSON_REC.RENT_OWN_IND := L_ATTRIBUTE_VALUE;
632 ELSIF L_ATTRIBUTE_NAME = 'LAST_KNOWN_GPS' THEN
633 L_PERSON_REC.LAST_KNOWN_GPS := L_ATTRIBUTE_VALUE;
634 /* ELSIF L_ATTRIBUTE_NAME = 'BEST_TIME_CONTACT_BEGIN' THEN
635 L_PERSON_REC.BEST_TIME_CONTACT_BEGIN := L_ATTRIBUTE_VALUE;
636 ELSIF L_ATTRIBUTE_NAME = 'BEST_TIME_CONTACT_END' THEN
637 L_PERSON_REC.BEST_TIME_CONTACT_END := L_ATTRIBUTE_VALUE;
638 ELSIF L_ATTRIBUTE_NAME = 'EFFECTIVE_START_DATE' THEN
639 L_PERSON_REC.EFFECTIVE_START_DATE := L_ATTRIBUTE_VALUE;
640 ELSIF L_ATTRIBUTE_NAME = 'EFFECTIVE_END_DATE' THEN
641 L_PERSON_REC.EFFECTIVE_END_DATE := L_ATTRIBUTE_VALUE;
642 ELSIF L_ATTRIBUTE_NAME = 'CONTENT_SOURCE_TYPE' THEN
643 L_PERSON_REC.CONTENT_SOURCE_TYPE := L_ATTRIBUTE_VALUE;
644 ELSIF L_ATTRIBUTE_NAME = 'CONTENT_SOURCE_NUMBER' THEN
645 L_PERSON_REC.CONTENT_SOURCE_NUMBER := L_ATTRIBUTE_VALUE;
646 ELSIF L_ATTRIBUTE_NAME = 'PROGRAM_UPDATE_DATE' THEN
647 L_PERSON_REC.PROGRAM_UPDATE_DATE := L_ATTRIBUTE_VALUE; */
648 ELSIF L_ATTRIBUTE_NAME = 'PERSON_PRE_NAME_ADJUNCT' THEN
649 L_PERSON_REC.PERSON_PRE_NAME_ADJUNCT := L_ATTRIBUTE_VALUE;
650 ELSIF L_ATTRIBUTE_NAME = 'PERSON_ACADEMIC_TITLE' THEN
651 L_PERSON_REC.PERSON_ACADEMIC_TITLE := L_ATTRIBUTE_VALUE;
652 ELSIF L_ATTRIBUTE_NAME = 'PERSON_INITIALS' THEN
653 L_PERSON_REC.PERSON_INITIALS := L_ATTRIBUTE_VALUE;
654 --ELSIF L_ATTRIBUTE_NAME = 'TAX_NAME' THEN
655 -- L_PERSON_REC.TAX_NAME := L_ATTRIBUTE_VALUE;
656 ELSIF L_ATTRIBUTE_NAME = 'TAX_REFERENCE' THEN
657 L_PERSON_REC.TAX_REFERENCE := L_ATTRIBUTE_VALUE;
658 ELSIF L_ATTRIBUTE_NAME = 'JGZZ_FISCAL_CODE' THEN
662 ELSIF L_ATTRIBUTE_NAME = 'PERSON_IDEN_TYPE' THEN
659 L_PERSON_REC.JGZZ_FISCAL_CODE := L_ATTRIBUTE_VALUE;
660 --ELSIF L_ATTRIBUTE_NAME = 'PRIMARY_CONTACT_ID' THEN
661 -- L_PERSON_REC.PRIMARY_CONTACT_ID := L_ATTRIBUTE_VALUE;
663 L_PERSON_REC.PERSON_IDEN_TYPE := L_ATTRIBUTE_VALUE;
664 ELSIF L_ATTRIBUTE_NAME = 'PERSON_IDENTIFIER' THEN
665 L_PERSON_REC.PERSON_IDENTIFIER := L_ATTRIBUTE_VALUE;
666 ELSIF L_ATTRIBUTE_NAME = 'DATE_OF_DEATH' THEN
667 L_PERSON_REC.DATE_OF_DEATH := l_attribute_date_value;-- L_ATTRIBUTE_VALUE; -- Bug No:3729832
668 ELSIF L_ATTRIBUTE_NAME = 'DECLARED_ETHNICITY' THEN
669 L_PERSON_REC.DECLARED_ETHNICITY := L_ATTRIBUTE_VALUE;
670 --ELSIF L_ATTRIBUTE_NAME = 'PERSON_PROFILE_ID' THEN
671 -- L_PERSON_REC.PERSON_PROFILE_ID := L_ATTRIBUTE_VALUE;
672 --ELSIF L_ATTRIBUTE_NAME = 'PARTY_ID' THEN
673 -- L_PERSON_REC.PARTY_ID := L_ATTRIBUTE_VALUE;
674 --ELSIF L_ATTRIBUTE_NAME = 'PERSON_NAME' THEN
675 -- L_PERSON_REC.PERSON_NAME := L_ATTRIBUTE_VALUE;
676 ELSIF L_ATTRIBUTE_NAME = 'INTERNAL_FLAG' THEN
677 L_PERSON_REC.INTERNAL_FLAG := L_ATTRIBUTE_VALUE;
678 ELSIF L_ATTRIBUTE_NAME = 'KNOWN_AS2' THEN
679 L_PERSON_REC.KNOWN_AS2 := L_ATTRIBUTE_VALUE;
680 ELSIF L_ATTRIBUTE_NAME = 'KNOWN_AS3' THEN
681 L_PERSON_REC.KNOWN_AS3 := L_ATTRIBUTE_VALUE;
682 ELSIF L_ATTRIBUTE_NAME = 'KNOWN_AS4' THEN
683 L_PERSON_REC.KNOWN_AS4 := L_ATTRIBUTE_VALUE;
684 ELSIF L_ATTRIBUTE_NAME = 'KNOWN_AS5' THEN
685 L_PERSON_REC.KNOWN_AS5 := L_ATTRIBUTE_VALUE;
686 ELSIF L_ATTRIBUTE_NAME = 'MIDDLE_NAME_PHONETIC' THEN
687 L_PERSON_REC.MIDDLE_NAME_PHONETIC := L_ATTRIBUTE_VALUE;
688 --ELSIF L_ATTRIBUTE_NAME = 'STATUS' THEN
689 -- L_PERSON_REC.STATUS := L_ATTRIBUTE_VALUE;
690 -- ELSIF L_ATTRIBUTE_NAME = 'APPLICATION_ID' THEN
691 --L_PERSON_REC.APPLICATION_ID := L_ATTRIBUTE_VALUE;
692 --ELSIF L_ATTRIBUTE_NAME = 'CREATED_BY_MODULE' THEN
693 --ELSIF L_ATTRIBUTE_NAME = 'OBJECT_VERSION_NUMBER' THEN
694 -- L_PERSON_REC.OBJECT_VERSION_NUMBER := L_ATTRIBUTE_VALUE;
695 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE_CATEGORY' THEN
696 L_PERSON_REC.ATTRIBUTE_CATEGORY := L_ATTRIBUTE_VALUE;
697 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE2' THEN
698 L_PERSON_REC.ATTRIBUTE2 := L_ATTRIBUTE_VALUE;
699 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE3' THEN
700 L_PERSON_REC.ATTRIBUTE3 := L_ATTRIBUTE_VALUE;
701 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE4' THEN
702 L_PERSON_REC.ATTRIBUTE4 := L_ATTRIBUTE_VALUE;
703 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE5' THEN
704 L_PERSON_REC.ATTRIBUTE5 := L_ATTRIBUTE_VALUE;
705 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE6' THEN
706 L_PERSON_REC.ATTRIBUTE6 := L_ATTRIBUTE_VALUE;
707 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE7' THEN
708 L_PERSON_REC.ATTRIBUTE7 := L_ATTRIBUTE_VALUE;
709 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE9' THEN
710 L_PERSON_REC.ATTRIBUTE9 := L_ATTRIBUTE_VALUE;
711 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE10' THEN
712 L_PERSON_REC.ATTRIBUTE10 := L_ATTRIBUTE_VALUE;
713 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE11' THEN
714 L_PERSON_REC.ATTRIBUTE11 := L_ATTRIBUTE_VALUE;
715 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE12' THEN
716 L_PERSON_REC.ATTRIBUTE12 := L_ATTRIBUTE_VALUE;
717 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE13' THEN
718 L_PERSON_REC.ATTRIBUTE13 := L_ATTRIBUTE_VALUE;
719 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE14' THEN
720 L_PERSON_REC.ATTRIBUTE14 := L_ATTRIBUTE_VALUE;
721 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE16' THEN
722 L_PERSON_REC.ATTRIBUTE16 := L_ATTRIBUTE_VALUE;
723 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE17' THEN
724 L_PERSON_REC.ATTRIBUTE17 := L_ATTRIBUTE_VALUE;
725 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE18' THEN
726 L_PERSON_REC.ATTRIBUTE18 := L_ATTRIBUTE_VALUE;
727 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE19' THEN
728 L_PERSON_REC.ATTRIBUTE19 := L_ATTRIBUTE_VALUE;
729 /* ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE_CATEGORY' THEN
730 L_PERSON_REC.GLOBAL_ATTRIBUTE_CATEGORY := L_ATTRIBUTE_VALUE;
731 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE1' THEN
732 L_PERSON_REC.GLOBAL_ATTRIBUTE1 := L_ATTRIBUTE_VALUE;
733 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE2' THEN
734 L_PERSON_REC.GLOBAL_ATTRIBUTE2 := L_ATTRIBUTE_VALUE;
735 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE3' THEN
736 L_PERSON_REC.GLOBAL_ATTRIBUTE3 := L_ATTRIBUTE_VALUE;
737 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE4' THEN
738 L_PERSON_REC.GLOBAL_ATTRIBUTE4 := L_ATTRIBUTE_VALUE;
739 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE5' THEN
740 L_PERSON_REC.GLOBAL_ATTRIBUTE5 := L_ATTRIBUTE_VALUE;
741 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE6' THEN
742 L_PERSON_REC.GLOBAL_ATTRIBUTE6 := L_ATTRIBUTE_VALUE;
743 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE7' THEN
744 L_PERSON_REC.GLOBAL_ATTRIBUTE7 := L_ATTRIBUTE_VALUE;
745 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE8' THEN
746 L_PERSON_REC.GLOBAL_ATTRIBUTE8 := L_ATTRIBUTE_VALUE;
747 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE10' THEN
748 L_PERSON_REC.GLOBAL_ATTRIBUTE10 := L_ATTRIBUTE_VALUE;
749 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE11' THEN
750 L_PERSON_REC.GLOBAL_ATTRIBUTE11 := L_ATTRIBUTE_VALUE;
751 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE12' THEN
752 L_PERSON_REC.GLOBAL_ATTRIBUTE12 := L_ATTRIBUTE_VALUE;
753 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE13' THEN
754 L_PERSON_REC.GLOBAL_ATTRIBUTE13 := L_ATTRIBUTE_VALUE;
755 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE14' THEN
756 L_PERSON_REC.GLOBAL_ATTRIBUTE14 := L_ATTRIBUTE_VALUE;
757 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE15' THEN
758 L_PERSON_REC.GLOBAL_ATTRIBUTE15 := L_ATTRIBUTE_VALUE;
759 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE16' THEN
760 L_PERSON_REC.GLOBAL_ATTRIBUTE16 := L_ATTRIBUTE_VALUE;
761 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE17' THEN
762 L_PERSON_REC.GLOBAL_ATTRIBUTE17 := L_ATTRIBUTE_VALUE;
763 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE18' THEN
764 L_PERSON_REC.GLOBAL_ATTRIBUTE18 := L_ATTRIBUTE_VALUE;
765 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE19' THEN
766 L_PERSON_REC.GLOBAL_ATTRIBUTE19 := L_ATTRIBUTE_VALUE;
767 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE20' THEN
768 L_PERSON_REC.GLOBAL_ATTRIBUTE20 := L_ATTRIBUTE_VALUE;*/
769 /* ELSIF L_ATTRIBUTE_NAME = 'LAST_UPDATE_DATE' THEN
770 L_PERSON_REC.LAST_UPDATE_DATE := L_ATTRIBUTE_VALUE;
771 ELSIF L_ATTRIBUTE_NAME = 'LAST_UPDATED_BY' THEN
772 L_PERSON_REC.LAST_UPDATED_BY := L_ATTRIBUTE_VALUE;
773 ELSIF L_ATTRIBUTE_NAME = 'CREATION_DATE' THEN
774 L_PERSON_REC.CREATION_DATE := L_ATTRIBUTE_VALUE;
775 ELSIF L_ATTRIBUTE_NAME = 'CREATED_BY' THEN
776 L_PERSON_REC.CREATED_BY := L_ATTRIBUTE_VALUE;
777 ELSIF L_ATTRIBUTE_NAME = 'LAST_UPDATE_LOGIN' THEN
778 L_PERSON_REC.LAST_UPDATE_LOGIN := L_ATTRIBUTE_VALUE;
779 ELSIF L_ATTRIBUTE_NAME = 'WH_UPDATE_DATE' THEN
780 L_PERSON_REC.WH_UPDATE_DATE := L_ATTRIBUTE_VALUE;
781 ELSIF L_ATTRIBUTE_NAME = 'REQUEST_ID' THEN
782 L_PERSON_REC.REQUEST_ID := L_ATTRIBUTE_VALUE;
783 ELSIF L_ATTRIBUTE_NAME = 'PROGRAM_APPLICATION_ID' THEN
784 L_PERSON_REC.PROGRAM_APPLICATION_ID := L_ATTRIBUTE_VALUE;
785 ELSIF L_ATTRIBUTE_NAME = 'GLOBAL_ATTRIBUTE9' THEN
786 L_PERSON_REC.GLOBAL_ATTRIBUTE9 := L_ATTRIBUTE_VALUE; */
787 --ELSIF L_ATTRIBUTE_NAME = 'PROGRAM_ID' THEN
788 -- L_PERSON_REC.PROGRAM_ID := L_ATTRIBUTE_VALUE;
789 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE1' THEN
790 L_PERSON_REC.ATTRIBUTE1 := L_ATTRIBUTE_VALUE;
791 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE15' THEN
792 L_PERSON_REC.ATTRIBUTE15 := L_ATTRIBUTE_VALUE;
793 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE20' THEN
794 L_PERSON_REC.ATTRIBUTE20 := L_ATTRIBUTE_VALUE;
795 ELSIF L_ATTRIBUTE_NAME = 'ATTRIBUTE8' THEN
796 L_PERSON_REC.ATTRIBUTE8 := L_ATTRIBUTE_VALUE;
797 END IF;
798
799 End loop;
800 Close get_person_rec_csr;
801 L_PERSON_REC.PARTY_REC.PARTY_ID := P_MERGE_TO_PARTY_ID;
802 X_person_rec := l_person_rec;
803 EXCEPTION
804 WHEN OTHERS THEN
805 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
806 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
807 FND_MSG_PUB.ADD;
808 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
809 end construct_person_rec;
810
811 /* procedure called in HZ_MERGE_PKG */
812 PROCEDURE do_profile_attribute_merge(
813 p_merge_batch_id IN NUMBER,
814 p_merge_to_party_id IN NUMBER,
815 p_entity_name IN VARCHAR2,
816 x_return_status OUT NOCOPY VARCHAR2
817 ) is
818
819 l_org_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
820 l_person_rec HZ_PARTY_V2PUB.PERSON_REC_TYPE;
821 l_profile_id number;
822 l_msg_count number;
823 l_msg_data varchar2(2000);
824 l_object_version_number number;
825 l_attribute_party_id number;
826 l_party_id number;
827 l_orig_sys_ref varchar2(240);
828 l_automerge_flag varchar2(1);
829
830 cursor get_obj_version_csr is
831 select object_version_number,orig_system_reference
832 from hz_parties
833 where party_id = p_merge_to_party_id;
834
835 cursor get_attri_party_csr is
836 select attribute_party_id
837 from HZ_MERGE_ENTITY_ATTRIBUTES
838 where merge_to_party_id = p_merge_to_party_id
839 and entity_name = 'HZ_ORGANIZATION_PROFILES'
840 and merge_batch_id = p_merge_batch_id
841 and attribute_name = 'BUSINESS_REPORT';
842
843 cursor get_automerge_flag_csr is
844 select nvl(db.automerge_flag,'N')
845 from hz_dup_batch db, hz_dup_sets ds
846 where db.dup_batch_id = ds.dup_batch_id
847 and ds.dup_set_id = p_merge_batch_id
848 and rownum=1;
849
850 begin
851 -- Initialize return status to SUCCESS
852 x_return_status := FND_API.G_RET_STS_SUCCESS;
853
854 open get_automerge_flag_csr;
855 fetch get_automerge_flag_csr into l_automerge_flag;
856 close get_automerge_flag_csr;
857
858 if p_entity_name = 'HZ_ORGANIZATION_PROFILES'
859 then
860 if l_automerge_flag = 'N' or (l_automerge_flag = 'Y' and nvl(fnd_profile.value('HZ_PROF_ATTR_DEFAULT'), 'MASTER') <> 'MASTER')
861 then
862 open get_obj_version_csr;
863 fetch get_obj_version_csr into l_object_version_number,l_orig_sys_ref;
864 close get_obj_version_csr;
865
866 Construct_organization_rec(p_merge_batch_id,p_merge_to_party_id,l_org_rec,x_return_status);
867
868 Hz_party_v2pub.update_organization (
869 p_init_msg_list => FND_API.G_FALSE,
870 p_organization_rec => l_org_rec,
871 p_party_object_version_number =>l_object_version_number,
872 x_profile_id => l_profile_id,
873 x_return_status => x_return_status,
874 x_msg_count => l_msg_count,
875 x_msg_data =>l_msg_data
876 );
877
878 open get_attri_party_csr;
879 fetch get_attri_party_csr into l_attribute_party_id;
880 close get_attri_party_csr;
881
882 UPDATE hz_organization_profiles
883 SET business_report = (select business_report
884 from hz_organization_profiles
885 where party_id = l_attribute_party_id
886 and sysdate between effective_start_date and nvl(effective_end_date,sysdate)
887 and rownum=1),
888 last_update_date = SYSDATE,
889 last_update_login = FND_GLOBAL.LOGIN_ID,
890 last_updated_by = FND_GLOBAL.USER_ID,
891 object_version_number = nvl(object_version_number,1)+1
892 where party_id = p_merge_to_party_id
893 and sysdate between effective_start_date and nvl(effective_end_date,sysdate);
894 end if; -- if l_automerge_flag = 'N'
895 elsif p_entity_name = 'HZ_PERSON_PROFILES' then
896 if l_automerge_flag = 'N' or (l_automerge_flag = 'Y' and nvl(fnd_profile.value('HZ_PROF_ATTR_DEFAULT'), 'MASTER') <> 'MASTER')
897 then
898 open get_obj_version_csr;
899 fetch get_obj_version_csr into l_object_version_number, l_orig_sys_ref;
900 close get_obj_version_csr;
901
902 Construct_person_rec(p_merge_batch_id, p_merge_to_party_id, l_orig_sys_ref,l_person_rec,x_return_status);
903
904 Hz_party_v2pub.update_person (
905 p_init_msg_list => FND_API.G_FALSE,
906 p_person_rec => l_person_rec,
907 p_party_object_version_number => l_object_version_number,
908 x_profile_id => l_profile_id,
909 x_return_status => x_return_status,
910 x_msg_count => l_msg_count,
911 x_msg_data =>l_msg_data
912 );
913 end if; -- if l_automerge_flag = 'N'
914 -- update_org/person api doesn't allow update created_by_module
915 -- per chris, call update directly.
916
917 l_party_id :=null;
918 l_party_id := get_healthCarePartyId(p_merge_batch_id);
919 if l_party_id is not null
920 then
921 update hz_person_profiles
922 set created_by_module = 'CTB_PERSON_REGISTRY_SERVICES',
923 last_update_date = SYSDATE,
924 last_update_login = FND_GLOBAL.LOGIN_ID,
925 last_updated_by = FND_GLOBAL.USER_ID,
926 object_version_number = nvl(object_version_number,1)+1
927 where party_id = p_merge_to_party_id
928 and sysdate between effective_start_date and nvl(effective_end_date,sysdate);
929
930 end if;
931 end if;
932 l_party_id := get_InternalFlagPartyId(p_merge_batch_id,p_entity_name);
933
934 if l_party_id is not null then
935 update HZ_MERGE_ENTITY_ATTRIBUTES
936 set attribute_value = 'Y',
937 attribute_party_id = l_party_id,
938 last_update_date = SYSDATE,
939 last_update_login = FND_GLOBAL.LOGIN_ID,
940 last_updated_by = FND_GLOBAL.USER_ID,
941 object_version_number = nvl(object_version_number,1)+1
942 where attribute_name = 'INTERNAL_FLAG'
943 and merge_batch_id = p_merge_batch_id
944 and merge_to_party_id = p_merge_to_party_id;
945 end if;
946
947
948 EXCEPTION
949 WHEN OTHERS THEN
950 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
951 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
952 FND_MSG_PUB.ADD;
953 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
954
955 end do_profile_attribute_merge;
956
957 -- private procedures, called in create_merge_attributes
958
959 function get_attribute_value(p_attribute_name in varchar2, p_party_id in number,p_entity_name in varchar2)
960 return varchar2 is
961
962 l_str varchar2(2000);
963 l_value varchar2(2000);
964
965 begin
966 if (instr(p_attribute_name,'TIME') > 0 or instr(p_attribute_name,'DATE')> 0 )
967 and (p_attribute_name not in ('LAST_UPDATED_BY', 'LAST_UPDATE_LOGIN')) -- bug 4726700
968 then
969 l_str := 'select to_char('||p_attribute_name||','||'''YYYY/MM/DD'')'||
970 ' from '||p_entity_name||
971 ' where party_id = :party_id'||
972 ' and sysdate between effective_start_date and nvl(effective_end_date,sysdate)';
973 -- fnd_file.put_line(fnd_file.log, l_str);
974 execute immediate l_str into l_value using p_party_id;
975
976 else
977 l_str := 'select to_char('||p_attribute_name||')'||
978 ' from '||p_entity_name||
979 ' where party_id = :party_id'||
980 ' and sysdate between effective_start_date and nvl(effective_end_date,sysdate)';
981 --fnd_file.put_line(fnd_file.log, l_str);
982 execute immediate l_str into l_value using p_party_id;
983 end if;
984 return l_value;
985 end;
986
987 -- private procedure. Group includes both single attribute group and multi attributes group
988 procedure update_group_attribute_value(p_attribute_name in varchar2,
989 p_candidate_party_id in number,
990 p_merge_batch_id IN NUMBER,
991 p_merge_to_party_id IN NUMBER,
992 p_derived_last_update_date IN DATE,
993 p_entity_name IN VARCHAR2,
994 p_proc_type IN VARCHAR2 DEFAULT 'C') is
995
996 -- get all attributes for the passing in group leader attribute name
997 cursor get_group_attributes_csr(p_attribute_name varchar2, p_lookup_type varchar2) is
998 select c1.lookup_code
999 from ar_lookups c, ar_lookups t, ar_lookups c1
1000 where c.lookup_type = t.lookup_code
1001 and c1.lookup_type = t.lookup_code
1002 and substrb(c.externally_visible_flag,6,3) = substrb(c1.externally_visible_flag,6,3)
1003 and c.lookup_type = c1.lookup_type
1004 and t.lookup_type = p_lookup_type
1005 and c.lookup_code = p_attribute_name
1006 and c1.enabled_flag = 'Y';
1007
1008 l_group_attribute_name varchar2(150);
1009 l_attribute_name varchar2(30);
1010 l_value varchar2(2000);
1011 l_lookup_type varchar2(30);
1012 begin
1013 if p_entity_name = 'HZ_ORGANIZATION_PROFILES'
1014 then
1015 l_lookup_type := 'ORG_PROFILE_CATEGORY';
1016 elsif p_entity_name = 'HZ_PERSON_PROFILES'
1017 then l_lookup_type := 'PERSON_PROFILE_CATEGORY';
1018 end if;
1019 open get_group_attributes_csr(p_attribute_name,l_lookup_type);
1020 loop
1021 fetch get_group_attributes_csr into l_group_attribute_name;
1022 exit when get_group_attributes_csr%NOTFOUND;
1023 l_value := get_attribute_value(l_group_attribute_name,p_candidate_party_id,p_entity_name);
1024
1025 if p_proc_type = 'C'
1026 then
1027 update hz_merge_entity_attributes
1028 set attribute_value = l_value, -- l_value has already formatted
1029 --attribute_value = decode(attribute_type,'D',to_char(to_date(l_value),'YYYY/MM/DD'),l_value), -- Bug No:3729832
1030 attribute_party_id = p_candidate_party_id,
1031 derived_last_update_date = p_derived_last_update_date,
1032 last_update_date = SYSDATE,
1033 last_update_login = FND_GLOBAL.LOGIN_ID,
1034 last_updated_by = FND_GLOBAL.USER_ID,
1035 object_version_number = nvl(object_version_number,1)+1
1036 where attribute_name = l_group_attribute_name
1037 and merge_batch_id = p_merge_batch_id
1038 and entity_name = p_entity_name
1039 and merge_to_party_id =p_merge_to_party_id
1040 and attribute_value is null;
1041 else
1042 update hz_merge_entity_attributes
1043 set attribute_value = l_value, -- l_value has already formatted
1044 --attribute_value = decode(attribute_type,'D',to_char(to_date(l_value),'YYYY/MM/DD'),l_value), -- Bug No:3729832
1045 attribute_party_id = p_candidate_party_id,
1046 derived_last_update_date = p_derived_last_update_date,
1047 last_update_date = SYSDATE,
1048 last_update_login = FND_GLOBAL.LOGIN_ID,
1049 last_updated_by = FND_GLOBAL.USER_ID,
1050 object_version_number = nvl(object_version_number,1)+1
1051 where attribute_name = l_group_attribute_name
1052 and merge_batch_id = p_merge_batch_id
1053 and entity_name = p_entity_name
1054 and merge_to_party_id =p_merge_to_party_id;
1055 end if;
1056
1057 end loop;
1058 close get_group_attributes_csr;
1059 end;
1060
1061 /* Profile attribute merge defaulting rule:
1062 Default to selected party profile attribute values which are not null.
1063 For nullable leader attribute values, default to candidate party
1064 profile attribute values based on profile values, also, update master party
1065 profile to leader attribute group values.*/
1066
1067 procedure default_attribute_values(
1068 p_merge_batch_id IN NUMBER,
1069 p_merge_to_party_id IN NUMBER,
1070 p_entity_name IN VARCHAR2) is
1071
1072 -- get master party attributes where leader is null
1073 cursor get_leader_attribute_name_csr(p_lookup_type varchar2) is
1074 select attri.attribute_name
1075 from hz_merge_entity_attributes attri, ar_lookups c, ar_lookups t
1076 where attri.attribute_name = c.lookup_code
1077 and c.lookup_type = t.lookup_code
1078 and t.lookup_type = p_lookup_type
1079 and attribute_value is null and substrb(c.externally_visible_flag,10,1) = 'L'
1080 and merge_batch_id = p_merge_batch_id
1081 and merge_to_party_id = p_merge_to_party_id
1082 and c.enabled_flag = 'Y';
1083
1084 -- get candidate parties with desc order for last update date
1085 cursor get_org_candidate_party_csr is
1086 select mp.from_party_id, pro.last_update_date
1087 from hz_organization_profiles pro, hz_merge_parties mp
1088 where pro.party_id = mp.from_party_id
1089 and mp.batch_id = p_merge_batch_id
1090 and sysdate between pro.effective_start_date and nvl(pro.effective_end_date,sysdate)
1091 order by pro.last_update_date desc;
1092
1093 -- get candidate parties with desc order for latest creation date
1094 cursor get_org_candidate_party_lc_csr is
1095 select mp.from_party_id, pro.last_update_date
1096 from hz_organization_profiles pro, hz_merge_parties mp
1097 where pro.party_id = mp.from_party_id
1098 and mp.batch_id = p_merge_batch_id
1099 and sysdate between pro.effective_start_date and nvl(pro.effective_end_date,sysdate)
1100 order by pro.creation_date desc, pro.party_id desc;
1101
1102 -- get candidate parties with desc order for earlist creation date
1106 where pro.party_id = mp.from_party_id
1103 cursor get_org_candidate_party_ec_csr is
1104 select mp.from_party_id, pro.last_update_date
1105 from hz_organization_profiles pro, hz_merge_parties mp
1107 and mp.batch_id = p_merge_batch_id
1108 and sysdate between pro.effective_start_date and nvl(pro.effective_end_date,sysdate)
1109 order by pro.creation_date, pro.party_id;
1110
1111 -- get candidate parties with desc order for last update date
1112 cursor get_per_candidate_party_csr is
1113 select mp.from_party_id, pro.last_update_date
1114 from hz_person_profiles pro, hz_merge_parties mp
1115 where pro.party_id = mp.from_party_id
1116 and mp.batch_id = p_merge_batch_id
1117 and sysdate between pro.effective_start_date and nvl(pro.effective_end_date,sysdate)
1118 order by pro.last_update_date desc;
1119
1120 -- get candidate parties with desc order for last creation date
1121 cursor get_per_candidate_party_lc_csr is
1122 select mp.from_party_id, pro.last_update_date
1123 from hz_person_profiles pro, hz_merge_parties mp
1124 where pro.party_id = mp.from_party_id
1125 and mp.batch_id = p_merge_batch_id
1126 and sysdate between pro.effective_start_date and nvl(pro.effective_end_date,sysdate)
1127 order by pro.creation_date desc, pro.party_id desc;
1128
1129 -- get candidate parties with desc order for earlist creation date
1130 cursor get_per_candidate_party_ec_csr is
1131 select mp.from_party_id, pro.last_update_date
1132 from hz_person_profiles pro, hz_merge_parties mp
1133 where pro.party_id = mp.from_party_id
1134 and mp.batch_id = p_merge_batch_id
1135 and sysdate between pro.effective_start_date and nvl(pro.effective_end_date,sysdate)
1136 order by pro.creation_date, pro.party_id;
1137
1138
1139 l_attribute_name varchar2(30);
1140 l_party_id number;
1141 l_last_update_date DATE;
1142 l_value varchar2(2000);
1143 l_str varchar2(2000);
1144 l_group_attribute_name varchar2(2000);
1145 l_attri_def_prof varchar2(30);
1146 l_attri_def_next_prof varchar2(30);
1147 begin
1148
1149 -- l_attri_def_prof := nvl(fnd_profile.value('HZ_PROF_ATTR_DEFAULT'), 'MASTER');
1150 l_attri_def_next_prof := nvl(fnd_profile.value('HZ_PROF_ATTR_DEFAULT_NEXT'), 'LATEST_UPDATE_DATE');
1151
1152 if p_entity_name = 'HZ_ORGANIZATION_PROFILES'
1153 then
1154 open get_leader_attribute_name_csr('ORG_PROFILE_CATEGORY');
1155 loop
1156 fetch get_leader_attribute_name_csr into l_attribute_name;
1157 exit when get_leader_attribute_name_csr%NOTFOUND;
1158
1159 /************* LATEST_UPDATE_DATE CASE **************/
1160
1161 if l_attri_def_next_prof = 'LATEST_UPDATE_DATE'
1162 then
1163 open get_org_candidate_party_csr;
1164 loop
1165 fetch get_org_candidate_party_csr into l_party_id,l_last_update_date;
1166 exit when get_org_candidate_party_csr%NOTFOUND;
1167 if l_attribute_name in ('EMPLOYEES_TOTAL','TOTAL_EMPLOYEES_TEXT')
1168 then
1169 l_value := get_attribute_value('EMPLOYEES_TOTAL', l_party_id,p_entity_name)||
1170 get_attribute_value('TOTAL_EMPLOYEES_TEXT', l_party_id,p_entity_name);
1171 elsif l_attribute_name in ('EMP_AT_PRIMARY_ADR','EMP_AT_PRIMARY_ADR_TEXT')
1172 then
1173 l_value := get_attribute_value('EMP_AT_PRIMARY_ADR', l_party_id,p_entity_name)||
1174 get_attribute_value('EMP_AT_PRIMARY_ADR_TEXT', l_party_id, p_entity_name);
1175 else
1176 l_value := get_attribute_value(l_attribute_name, l_party_id,p_entity_name);
1177 end if;
1178 if l_value is not null
1179 then
1180 update_group_attribute_value(p_attribute_name=>l_attribute_name,
1181 p_candidate_party_id => l_party_id,
1182 p_merge_batch_id => p_merge_batch_id,
1183 p_merge_to_party_id =>p_merge_to_party_id,
1184 p_derived_last_update_date => l_last_update_date,
1185 p_entity_name => p_entity_name);
1186
1187 exit; -- exit from the second loop
1188
1189 end if;
1190 end loop; -- for each candidate party desc on last update date
1191 close get_org_candidate_party_csr;
1192 end if; -- latest update date case
1193
1194 /************* LATEST_CREATION_DATE CASE **************/
1195
1196 if l_attri_def_next_prof = 'LATEST_CREATION_DATE'
1197 then
1198 open get_org_candidate_party_lc_csr;
1199 loop
1200 fetch get_org_candidate_party_lc_csr into l_party_id,l_last_update_date;
1201 exit when get_org_candidate_party_lc_csr%NOTFOUND;
1202 if l_attribute_name in ('EMPLOYEES_TOTAL','TOTAL_EMPLOYEES_TEXT')
1203 then
1204 l_value := get_attribute_value('EMPLOYEES_TOTAL', l_party_id,p_entity_name)||
1205 get_attribute_value('TOTAL_EMPLOYEES_TEXT', l_party_id,p_entity_name);
1206 elsif l_attribute_name in ('EMP_AT_PRIMARY_ADR','EMP_AT_PRIMARY_ADR_TEXT')
1207 then
1208 l_value := get_attribute_value('EMP_AT_PRIMARY_ADR', l_party_id,p_entity_name)||
1209 get_attribute_value('EMP_AT_PRIMARY_ADR_TEXT', l_party_id, p_entity_name);
1210 else
1211 l_value := get_attribute_value(l_attribute_name, l_party_id,p_entity_name);
1212 end if;
1213 if l_value is not null
1214 then
1215 update_group_attribute_value(p_attribute_name=>l_attribute_name,
1216 p_candidate_party_id => l_party_id,
1217 p_merge_batch_id => p_merge_batch_id,
1218 p_merge_to_party_id =>p_merge_to_party_id,
1219 p_derived_last_update_date => l_last_update_date,
1220 p_entity_name => p_entity_name);
1221
1222 exit; -- exit from the second loop
1223
1224 end if;
1228
1225 end loop; -- for each candidate party desc on last creation date
1226 close get_org_candidate_party_lc_csr;
1227 end if; -- latest creation date case
1229 /************* EARLIEST_CREATION_DATE CASE **************/
1230
1231 if l_attri_def_next_prof = 'EARLIEST_CREATION_DATE'
1232 then
1233 open get_org_candidate_party_ec_csr;
1234 loop
1235 fetch get_org_candidate_party_ec_csr into l_party_id,l_last_update_date;
1236 exit when get_org_candidate_party_ec_csr%NOTFOUND;
1237 if l_attribute_name in ('EMPLOYEES_TOTAL','TOTAL_EMPLOYEES_TEXT')
1238 then
1239 l_value := get_attribute_value('EMPLOYEES_TOTAL', l_party_id,p_entity_name)||
1240 get_attribute_value('TOTAL_EMPLOYEES_TEXT', l_party_id,p_entity_name);
1241 elsif l_attribute_name in ('EMP_AT_PRIMARY_ADR','EMP_AT_PRIMARY_ADR_TEXT')
1242 then
1243 l_value := get_attribute_value('EMP_AT_PRIMARY_ADR', l_party_id,p_entity_name)||
1244 get_attribute_value('EMP_AT_PRIMARY_ADR_TEXT', l_party_id, p_entity_name);
1245 else
1246 l_value := get_attribute_value(l_attribute_name, l_party_id,p_entity_name);
1247 end if;
1248 if l_value is not null
1249 then
1250 update_group_attribute_value(p_attribute_name=>l_attribute_name,
1251 p_candidate_party_id => l_party_id,
1252 p_merge_batch_id => p_merge_batch_id,
1253 p_merge_to_party_id =>p_merge_to_party_id,
1254 p_derived_last_update_date => l_last_update_date,
1255 p_entity_name => p_entity_name);
1256
1257 exit; -- exit from the second loop
1258
1259 end if;
1260 end loop; -- for each candidate party desc on earliest creation date
1261 close get_org_candidate_party_ec_csr;
1262 end if; -- earliest creation date case
1263
1264 end loop;
1265 close get_leader_attribute_name_csr;
1266 elsif p_entity_name = 'HZ_PERSON_PROFILES'
1267 then
1268 open get_leader_attribute_name_csr('PERSON_PROFILE_CATEGORY');
1269 loop
1270 fetch get_leader_attribute_name_csr into l_attribute_name;
1271 exit when get_leader_attribute_name_csr%NOTFOUND;
1272
1273 /************* LATEST_UPDATE_DATE CASE **************/
1274
1275 if (l_attri_def_next_prof = 'LATEST_UPDATE_DATE')
1276 then
1277 open get_per_candidate_party_csr;
1278 loop
1279 fetch get_per_candidate_party_csr into l_party_id,l_last_update_date;
1280 exit when get_per_candidate_party_csr%NOTFOUND;
1281 l_value := get_attribute_value(l_attribute_name, l_party_id,p_entity_name);
1282
1283 if l_value is not null
1284 then
1285 update_group_attribute_value(p_attribute_name=>l_attribute_name,
1286 p_candidate_party_id => l_party_id,
1287 p_merge_batch_id => p_merge_batch_id,
1288 p_merge_to_party_id => p_merge_to_party_id,
1289 p_derived_last_update_date => l_last_update_date,
1290 p_entity_name => p_entity_name);
1291 exit; -- exit from the second loop
1292 end if;
1293 end loop; -- for each candidate party desc on last update date
1294 close get_per_candidate_party_csr;
1295 end if; -- person lastest update date case
1296
1297 /************* LATEST_CREATION_DATE CASE **************/
1298
1299 if (l_attri_def_next_prof = 'LATEST_CREATION_DATE')
1300 then
1301 open get_per_candidate_party_lc_csr;
1302 loop
1303 fetch get_per_candidate_party_lc_csr into l_party_id,l_last_update_date;
1304 exit when get_per_candidate_party_lc_csr%NOTFOUND;
1305 l_value := get_attribute_value(l_attribute_name, l_party_id,p_entity_name);
1306
1307 if l_value is not null
1308 then
1309 update_group_attribute_value(p_attribute_name=>l_attribute_name,
1310 p_candidate_party_id => l_party_id,
1311 p_merge_batch_id => p_merge_batch_id,
1312 p_merge_to_party_id => p_merge_to_party_id,
1313 p_derived_last_update_date => l_last_update_date,
1314 p_entity_name => p_entity_name);
1315 exit; -- exit from the second loop
1316 end if;
1317 end loop; -- for each candidate party desc on last creation date
1318 close get_per_candidate_party_lc_csr;
1319 end if; -- person lastest creation date case
1320
1321 /************* EARLIEST_CREATION_DATE CASE **************/
1322
1323 if (l_attri_def_next_prof = 'EARLIEST_CREATION_DATE')
1324 then
1325 open get_per_candidate_party_ec_csr;
1326 loop
1327 fetch get_per_candidate_party_ec_csr into l_party_id,l_last_update_date;
1328 exit when get_per_candidate_party_ec_csr%NOTFOUND;
1329 l_value := get_attribute_value(l_attribute_name, l_party_id,p_entity_name);
1330
1331 if l_value is not null
1332 then
1333 update_group_attribute_value(p_attribute_name=>l_attribute_name,
1334 p_candidate_party_id => l_party_id,
1335 p_merge_batch_id => p_merge_batch_id,
1336 p_merge_to_party_id => p_merge_to_party_id,
1337 p_derived_last_update_date => l_last_update_date,
1338 p_entity_name => p_entity_name);
1339 exit; -- exit from the second loop
1340 end if;
1341 end loop; -- for each candidate party desc on last creation date
1342 close get_per_candidate_party_ec_csr;
1343 end if; -- person earliest creation date case
1344
1345 end loop;
1346 close get_leader_attribute_name_csr;
1347 end if;
1348 end;
1349
1350 procedure default_business_report(
1351 p_merge_batch_id IN NUMBER,
1352 p_merge_to_party_id IN NUMBER) is
1353
1354 cursor master_business_rpt_csr is
1355 select 'x'
1356 from hz_organization_profiles
1357 where party_id = p_merge_to_party_id
1358 and business_report is not null
1359 and sysdate between effective_start_date and nvl(effective_end_date,sysdate);
1360
1361 -- get candidate parties with desc order for last update date
1362 cursor get_business_report_party_csr is
1363 select mp.from_party_id, pro.last_update_date
1364 from hz_organization_profiles pro, hz_merge_parties mp
1365 where pro.party_id = mp.from_party_id
1366 and mp.batch_id = p_merge_batch_id
1367 and pro.business_report is not null
1368 and sysdate between pro.effective_start_date and nvl(pro.effective_end_date,sysdate)
1369 order by pro.last_update_date desc;
1370
1371 l_from_party_id number;
1372 l_last_update_date date;
1373 l_tmp varchar2(1);
1374 begin
1375 open master_business_rpt_csr;
1376 fetch master_business_rpt_csr into l_tmp;
1377 if master_business_rpt_csr%NOTFOUND
1378 then
1379 open get_business_report_party_csr;
1380 loop
1381 fetch get_business_report_party_csr into l_from_party_id, l_last_update_date;
1382 exit when get_business_report_party_csr%NOTFOUND;
1383
1384 update hz_merge_entity_attributes
1385 set attribute_party_id = l_from_party_id,
1386 last_update_date = SYSDATE,
1387 last_update_login = FND_GLOBAL.LOGIN_ID,
1388 last_updated_by = FND_GLOBAL.USER_ID,
1389 object_version_number = nvl(object_version_number,1)+1,
1390 derived_last_update_date = l_last_update_date
1391 where attribute_name = 'BUSINESS_REPORT'
1392 and merge_batch_id = p_merge_batch_id
1393 and entity_name = 'HZ_ORGANIZATION_PROFILES'
1394 and merge_to_party_id =p_merge_to_party_id;
1395
1396 close master_business_rpt_csr;
1397 close get_business_report_party_csr;
1398 return; -- only need to get the first party_id which has latest update date.
1399 end loop;
1400 end if;
1401 close master_business_rpt_csr;
1402 end;
1403
1404
1405 PROCEDURE default_most_occur_attri(
1406 p_merge_batch_id IN NUMBER,
1407 p_merge_to_party_id IN NUMBER,
1408 p_entity_name IN VARCHAR2,
1409 x_return_status OUT NOCOPY VARCHAR2,
1410 x_msg_count OUT NOCOPY NUMBER,
1411 x_msg_data OUT NOCOPY VARCHAR2
1412 ) is
1413 cursor get_entity_attri_csr(l_table_name varchar2)
1414 is
1415 select col.column_name, col.column_type
1416 from fnd_tables tbl, fnd_columns col
1417 where tbl.table_id = col.table_id
1418 and tbl.table_name = l_table_name
1419 -- adding the following clause for performance 4956759
1420 and tbl.application_id = 222
1421 and tbl.application_id = col.application_id;
1422
1423 l_attri_value varchar2(2000);
1424 l_attri_def_prof varchar2(30);
1425 l_attri_def_next_prof varchar2(30);
1426 l_col_name varchar2(30);
1427 l_col_type varchar2(1);
1428 l_value_count number;
1429 l_max_count number;
1430 l_date_clause varchar2(30);
1431 l_order_by_clause varchar2(80);
1432 l_str varchar2(2000);
1433 l_attri_party_id number;
1434 l_derived_date date;
1435 begin
1436 x_return_status := fnd_api.g_ret_sts_success;
1437 l_attri_def_next_prof := nvl(fnd_profile.value('HZ_PROF_ATTR_DEFAULT_NEXT'), 'LATEST_UPDATE_DATE');
1438 if l_attri_def_next_prof = 'LATEST_UPDATE_DATE'
1439 then
1440 l_date_clause := 'LAST_UPDATE_DATE';
1441 l_order_by_clause := ' order by LAST_UPDATE_DATE DESC';
1442 elsif l_attri_def_next_prof = 'LATEST_CREATION_DATE'
1443 then
1444 l_date_clause := 'CREATION_DATE';
1445 l_order_by_clause := ' order by CREATION_DATE DESC, PARTY_ID DESC';
1446 -- added sec. order by due to import bug for creation_date - time truncated
1447 elsif l_attri_def_next_prof = 'EARLIEST_CREATION_DATE'
1448 then
1449 l_date_clause := 'CREATION_DATE';
1450 l_order_by_clause := ' order by CREATION_DATE, PARTY_ID';
1451 end if;
1452
1453 open get_entity_attri_csr(p_entity_name);
1454 loop
1455 -- need to use d-sql in a loop in order to get
1456 -- attribute_value(table column value) from profile table
1457
1458 fetch get_entity_attri_csr into l_col_name,l_col_type;
1459 exit when get_entity_attri_csr %NOTFOUND;
1460 if p_entity_name = 'HZ_ORGANIZATION_PROFILES'
1461 then
1462 if l_col_name <> 'BUSINESS_REPORT'
1463 then
1464 l_str := 'select party_id, decode('''||l_col_type||''',''D'',to_char(attri_value,''YYYY/MM/DD''),attri_value) from '||
1465 '(SELECT party_id, attri_value, rank() over (order by cntAttri desc) rank, '||l_date_clause || ' from '||
1469 'AND DS.DUP_SET_ID = DSP.DUP_SET_ID AND OP.PARTY_ID = PARTY.PARTY_ID '||
1466 '(SELECT OP.PARTY_ID, OP.'||l_col_name||' attri_value, count(*) over (partition by OP.'||l_col_name||') as cntAttri, '||'op.'||l_date_clause||
1467 ' from HZ_ORGANIZATION_PROFILES OP,HZ_PARTIES PARTY, HZ_DUP_SETS DS, HZ_DUP_SET_PARTIES DSP, HZ_DUP_BATCH DB '||
1468 'WHERE OP.PARTY_ID =DSP.DUP_PARTY_ID AND DB.DUP_BATCH_ID = DS.DUP_BATCH_ID '||
1470 'AND sysdate between effective_start_date and nvl(effective_end_date,sysdate) '||
1471 'AND OP.'||l_col_name||' is not null '||
1472 'AND nvl(merge_flag, ''Y'') <> ''N'' AND DSP.DUP_SET_ID= '||':p_merge_batch_id'||')' || l_order_by_clause||')'
1473 ||' where rank = 1 and rownum=1' ; --added rownum=1, in case same create/update date for rank = 1
1474
1475 -- fnd_file.put_line(fnd_file.log, l_str);
1476 end if; -- <> 'BUSINESS_REPORT'
1477 else -- PERSON CASE
1478 l_str := 'select party_id, decode('''||l_col_type||''',''D'',to_char(attri_value,''YYYY/MM/DD''),attri_value) from '||
1479 '(SELECT party_id, attri_value, rank() over (order by cntAttri desc) rank, '||l_date_clause || ' from '||
1480 '(SELECT OP.PARTY_ID, OP.'||l_col_name||' attri_value, count(*) over (partition by OP.'||l_col_name||') as cntAttri, '||'op.'||l_date_clause||
1481 ' from HZ_PERSON_PROFILES OP,HZ_PARTIES PARTY, HZ_DUP_SETS DS, HZ_DUP_SET_PARTIES DSP, HZ_DUP_BATCH DB '||
1482 'WHERE OP.PARTY_ID =DSP.DUP_PARTY_ID AND DB.DUP_BATCH_ID = DS.DUP_BATCH_ID '||
1483 'AND DS.DUP_SET_ID = DSP.DUP_SET_ID AND OP.PARTY_ID = PARTY.PARTY_ID '||
1484 'AND sysdate between effective_start_date and nvl(effective_end_date,sysdate) '||
1485 'AND OP.'||l_col_name||' is not null '||
1486 'AND nvl(merge_flag, ''Y'') <> ''N'' AND DSP.DUP_SET_ID= '||':p_merge_batch_id'||')' || l_order_by_clause||')'
1487 ||' where rank = 1 and rownum=1' ; --added rownum=1, in case same create/update date for rank = 1
1488
1489 --fnd_file.put_line(fnd_file.log, l_str);
1490 end if;
1491 BEGIN
1492 execute immediate l_str into l_attri_party_id, l_attri_value using p_merge_batch_id;
1493 -- fnd_file.put_line(fnd_file.log, l_attri_value || l_col_name);
1494 EXCEPTION
1495 WHEN NO_DATA_FOUND THEN
1496 l_attri_party_id := p_merge_to_party_id;
1497 l_attri_value := null;
1498 WHEN OTHERS THEN
1499 l_attri_party_id := p_merge_to_party_id;
1500 l_attri_value := null;
1501 END;
1502 if l_col_name <> 'BUSINESS_REPORT'
1503 then
1504 l_derived_date := get_party_last_upd_date(l_attri_party_id,p_entity_name);
1505 insert into hz_merge_entity_attributes(
1506 MERGE_BATCH_ID,
1507 MERGE_TO_PARTY_ID,
1508 ATTRIBUTE_NAME,
1509 ATTRIBUTE_VALUE,
1510 ATTRIBUTE_TYPE,
1511 ATTRIBUTE_PARTY_ID,
1512 ENTITY_NAME,
1513 DERIVED_LAST_UPDATE_DATE,
1514 OBJECT_VERSION_NUMBER,
1515 CREATED_BY ,
1516 CREATION_DATE ,
1517 LAST_UPDATED_BY,
1518 LAST_UPDATE_LOGIN,
1519 LAST_UPDATE_DATE) values
1520 (p_merge_batch_id,
1521 p_merge_to_party_id,
1522 l_col_name,
1523 l_attri_value,
1524 l_col_type,
1525 l_attri_party_id,
1526 p_entity_name,
1527 l_derived_date,
1528 1,
1529 nvl(fnd_global.user_id,-1),
1530 sysdate,
1531 nvl(fnd_global.user_id,-1),
1532 nvl(fnd_global.login_id,-1),
1533 sysdate);
1534 end if;
1535
1536 end loop;
1537
1538 EXCEPTION
1539 WHEN OTHERS THEN
1540 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
1541 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1542 FND_MSG_PUB.ADD;
1543 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1544
1545 end;
1546
1547 /* private API */
1548 PROCEDURE create_merge_attributes(
1549 p_merge_batch_id IN NUMBER,
1550 p_merge_to_party_id IN NUMBER,
1551 p_entity_name IN VARCHAR2,
1552 x_return_status OUT NOCOPY VARCHAR2,
1553 x_msg_count OUT NOCOPY NUMBER,
1554 x_msg_data OUT NOCOPY VARCHAR2
1555 ) is
1556 cursor get_entity_attri_csr(l_table_name varchar2) is
1557 select col.column_name, col.column_type
1558 from fnd_tables tbl, fnd_columns col
1559 where tbl.table_id = col.table_id
1560 and tbl.table_name = l_table_name
1561 -- adding the following clause for performance 4956759
1562 and tbl.application_id = 222
1563 and tbl.application_id = col.application_id;
1564
1565 -- get party with latest update date
1566 cursor get_party_id_lu_csr is
1567 select party_id
1568 from(
1569 select pro.party_id party_id,
1570 RANK() OVER (ORDER BY pro.last_update_date desc ) rank
1571 from hz_parties pro, hz_dup_set_parties mp
1572 where pro.party_id = mp.dup_party_id
1573 and mp.dup_set_id = p_merge_batch_id)
1574 where rank = 1 and rownum=1;
1575
1576 -- get party with latest creation date
1577 cursor get_party_id_lc_csr is
1578 select party_id
1579 from(
1580 select pro.party_id party_id,
1581 RANK() OVER (ORDER BY pro.creation_date desc, pro.party_id desc ) rank
1582 from hz_parties pro, hz_dup_set_parties mp
1583 where pro.party_id = mp.dup_party_id
1584 and mp.dup_set_id = p_merge_batch_id)
1585 where rank = 1;
1586
1587 -- note: use second order by party id for all creation date related query due to bug in import-time trunc out
1588
1589 -- get party with earlist creation date
1590 cursor get_party_id_ec_csr is
1591 select party_id
1592 from(
1593 select pro.party_id party_id,
1594 RANK() OVER (ORDER BY pro.creation_date, pro.party_id ) rank
1595 from hz_parties pro, hz_dup_set_parties mp
1599
1596 where pro.party_id = mp.dup_party_id
1597 and mp.dup_set_id = p_merge_batch_id)
1598 where rank = 1;
1600
1601 l_col_name varchar2(30);
1602 l_col_type varchar2(1);
1603 str varchar2(2000);
1604 l_party_id number;
1605 l_col_name_var varchar2(30);
1606 l_prof_value varchar2(30);
1607 l_selected_party_id number;
1608 begin
1609 savepoint create_merge_attributes_pvt;
1610 -- Initialize return status to SUCCESS
1611 x_return_status := FND_API.G_RET_STS_SUCCESS;
1612
1613 if p_entity_name not in ('HZ_ORGANIZATION_PROFILES','HZ_PERSON_PROFILES')
1614 then
1615 FND_MESSAGE.SET_NAME( 'AR', 'HZ_INVALID_ENTITY_NAME' );
1616 FND_MSG_PUB.ADD;
1617 RAISE FND_API.G_EXC_ERROR;
1618 end if;
1619
1620 l_prof_value := nvl(fnd_profile.value('HZ_PROF_ATTR_DEFAULT'), 'MASTER');
1621 --dbms_output.put_line('profile value '|| l_prof_value);
1622 if l_prof_value = 'MASTER'
1623 then l_selected_party_id := p_merge_to_party_id;
1624 elsif l_prof_value = 'LATEST_UPDATE_DATE'
1625 then
1626 open get_party_id_lu_csr;
1627 fetch get_party_id_lu_csr into l_selected_party_id;
1628 close get_party_id_lu_csr;
1629 elsif l_prof_value = 'LATEST_CREATION_DATE'
1630 then
1631 open get_party_id_lc_csr;
1632 fetch get_party_id_lc_csr into l_selected_party_id;
1633 close get_party_id_lc_csr;
1634
1635 elsif l_prof_value = 'EARLIEST_CREATION_DATE'
1636 then
1637 open get_party_id_ec_csr;
1638 fetch get_party_id_ec_csr into l_selected_party_id;
1639 close get_party_id_ec_csr;
1640 elsif l_prof_value = 'MOST_OCCURRING'
1641 then
1642 default_most_occur_attri(
1643 p_merge_batch_id => p_merge_batch_id,
1644 p_merge_to_party_id => p_merge_to_party_id,
1645 p_entity_name => p_entity_name,
1646 x_return_status => x_return_status,
1647 x_msg_count => x_msg_count,
1648 x_msg_data => x_msg_data
1649 );
1650
1651 if x_return_status = fnd_api.g_ret_sts_success
1652 then
1653 return; -- done all of the logic for this defaulting rule.
1654 elsif x_return_status = fnd_api.g_ret_sts_unexp_error
1655 then
1656 raise fnd_api.g_exc_unexpected_error;
1657 end if;
1658
1659 end if;
1660
1661
1662 -- handle business report with seperate logic
1663 open get_entity_attri_csr(p_entity_name);
1664 loop
1665 -- need to use d-sql in a loop in order to get
1666 -- attribute_value(table column value) from profile table
1667
1668 fetch get_entity_attri_csr into l_col_name,l_col_type;
1669 exit when get_entity_attri_csr %NOTFOUND;
1670 if l_col_name = 'BUSINESS_REPORT'
1671 then
1672 insert into hz_merge_entity_attributes(
1673 MERGE_BATCH_ID,
1674 MERGE_TO_PARTY_ID,
1675 ATTRIBUTE_NAME,
1676 ATTRIBUTE_VALUE,
1677 ATTRIBUTE_TYPE,
1678 ATTRIBUTE_PARTY_ID,
1679 ENTITY_NAME,
1680 DERIVED_LAST_UPDATE_DATE,
1681 OBJECT_VERSION_NUMBER,
1682 CREATED_BY ,
1683 CREATION_DATE ,
1684 LAST_UPDATED_BY,
1685 LAST_UPDATE_LOGIN,
1686 LAST_UPDATE_DATE) select
1687 p_merge_batch_id,
1688 p_merge_to_party_id,
1689 'BUSINESS_REPORT',
1690 'CLOB',
1691 l_col_type,
1692 p_merge_to_party_id,
1693 p_entity_name,
1694 last_update_date,
1695 1,
1696 nvl(fnd_global.user_id,-1),
1697 sysdate,
1698 nvl(fnd_global.user_id,-1),
1699 nvl(fnd_global.login_id,-1),
1700 sysdate
1701 from HZ_ORGANIZATION_PROFILES
1702 where party_id = l_selected_party_id
1703 and sysdate between effective_start_date and nvl(effective_end_date,sysdate);
1704
1705 default_business_report(
1706 p_merge_batch_id=>p_merge_batch_id,
1707 p_merge_to_party_id => p_merge_to_party_id);
1708 else
1709 -- need to use d-sql to get to_char(l_col_name) from entity table
1710 -- ex. select to_char(SIC_CODE) from hz_organization_profiles to get attribute_value
1711
1712 str := 'insert into hz_merge_entity_attributes( '||
1713 'MERGE_BATCH_ID,'||
1714 'MERGE_TO_PARTY_ID,'||
1715 'ATTRIBUTE_NAME,'||
1716 'ATTRIBUTE_VALUE,'||
1717 'ATTRIBUTE_TYPE,'||
1718 'ATTRIBUTE_PARTY_ID,'||
1719 'ENTITY_NAME ,'||
1720 'DERIVED_LAST_UPDATE_DATE ,'||
1721 'OBJECT_VERSION_NUMBER,'||
1722 'CREATED_BY ,'||
1723 'CREATION_DATE ,'||
1724 'LAST_UPDATED_BY,'||
1725 'LAST_UPDATE_LOGIN,'||
1726 'LAST_UPDATE_DATE) '||
1727 'select '||
1728 p_merge_batch_id ||','||
1729 p_merge_to_party_id ||','||
1730 ''''||l_col_name||''''||','||
1731 'decode('||''''||l_col_type||''''||',''D'','||'to_char('||l_col_name||','||'''YYYY/MM/DD'')'||','||'to_char('||l_col_name||')'||')'||','||
1732
1733 ''''||l_col_type||''''||','||
1734 l_selected_party_id ||','||
1735 ''''||p_entity_name||''''||','||
1736 'last_update_date'||','||
1737 '1,'||
1738 'nvl(fnd_global.user_id,-1)'||','||
1739 'sysdate'||','||
1740 'nvl(fnd_global.user_id,-1)'||','||
1741 'nvl(fnd_global.login_id,-1)'||','||
1742 'sysdate'||
1743 ' from '|| p_entity_name ||
1744 ' where party_id = '||':selected_party_id'||
1745 ' and sysdate between effective_start_date and nvl(effective_end_date,sysdate)';
1746
1747 --fnd_file.put_line(fnd_file.log, str);
1748 EXECUTE IMMEDIATE str using l_selected_party_id;
1749 end if;
1750 end loop;
1751 close get_entity_attri_csr;
1752
1753 default_attribute_values(
1754 p_merge_batch_id => p_merge_batch_id,
1755 p_merge_to_party_id => p_merge_to_party_id,
1756 p_entity_name => p_entity_name);
1757
1758 if p_entity_name = 'HZ_PERSON_PROFILES' then
1759 l_party_id :=null;
1760 l_party_id := get_healthCarePartyId(p_merge_batch_id);
1761 if l_party_id is not null then
1762 update HZ_MERGE_ENTITY_ATTRIBUTES
1763 set attribute_value = 'CTB_PERSON_REGISTRY_SERVICES',
1764 attribute_party_id = l_party_id,
1765 last_update_date = SYSDATE,
1766 last_update_login = FND_GLOBAL.LOGIN_ID,
1767 last_updated_by = FND_GLOBAL.USER_ID,
1768 object_version_number = nvl(object_version_number,1)+1
1769 where attribute_name = 'CREATED_BY_MODULE'
1770 and merge_batch_id = p_merge_batch_id
1771 and merge_to_party_id = p_merge_to_party_id;
1772
1773 end if;
1774 end if;
1775 l_party_id := get_InternalFlagPartyId(p_merge_batch_id,p_entity_name);
1776
1777 if l_party_id is not null then
1778 update HZ_MERGE_ENTITY_ATTRIBUTES
1779 set attribute_value = 'Y',
1780 attribute_party_id = l_party_id,
1781 last_update_date = SYSDATE,
1782 last_update_login = FND_GLOBAL.LOGIN_ID,
1783 last_updated_by = FND_GLOBAL.USER_ID,
1784 object_version_number = nvl(object_version_number,1)+1
1785 where attribute_name = 'INTERNAL_FLAG'
1786 and merge_batch_id = p_merge_batch_id
1787 and merge_to_party_id = p_merge_to_party_id;
1788 end if;
1789
1790
1791
1792
1793 EXCEPTION
1794
1795 WHEN FND_API.G_EXC_ERROR THEN
1796 ROLLBACK TO create_merge_attributes_pvt;
1797 x_return_status := FND_API.G_RET_STS_ERROR;
1798 FND_MSG_PUB.Count_And_Get(
1799 p_encoded => FND_API.G_FALSE,
1800 p_count => x_msg_count,
1801 p_data => x_msg_data);
1802
1803 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1804 ROLLBACK TO create_merge_attributes_pvt;
1805 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1806 FND_MSG_PUB.Count_And_Get(
1807 p_encoded => FND_API.G_FALSE,
1808 p_count => x_msg_count,
1809 p_data => x_msg_data);
1810
1811 WHEN OTHERS THEN
1812 ROLLBACK TO create_merge_attributes_pvt;
1813 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1814 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1815 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1816 FND_MSG_PUB.ADD;
1817 FND_MSG_PUB.Count_And_Get(
1818 p_encoded => FND_API.G_FALSE,
1819 p_count => x_msg_count,
1820 p_data => x_msg_data);
1821
1822 end create_merge_attributes;
1823
1824 function is_leading_attribute(p_attribute_name in varchar2, p_lookup_type in varchar2) return varchar2 is
1825 cursor get_leading_attri_csr is
1826 select 'X'
1827 from ar_lookups c,
1828 ar_lookups t
1829 where c.lookup_type = t.lookup_code
1830 and c.enabled_flag = 'Y'
1831 and t.lookup_type = p_lookup_type
1832 and substrb(c.externally_visible_flag,10,1) = 'L'
1833 and c.lookup_code = p_attribute_name;
1834
1835 l_tmp varchar2(1);
1836 begin
1837 open get_leading_attri_csr;
1838 fetch get_leading_attri_csr into l_tmp;
1839 if get_leading_attri_csr%FOUND
1840 then
1841 close get_leading_attri_csr;
1842 return 'Y';
1843 else
1844 close get_leading_attri_csr;
1845 return 'N';
1846 end if;
1847 end;
1848
1849 /* private API */
1850 PROCEDURE update_merge_attribute (
1851 p_merge_batch_id IN NUMBER,
1852 p_merge_to_party_id IN NUMBER,
1853 p_attribute_name IN VARCHAR2,
1854 p_attribute_value IN VARCHAR2,
1855 p_attribute_party_id IN NUMBER,
1856 p_entity_name IN VARCHAR2,
1857 px_object_version_number IN OUT NOCOPY NUMBER,
1858 x_return_status OUT NOCOPY VARCHAR2,
1859 x_msg_count OUT NOCOPY NUMBER,
1860 x_msg_data OUT NOCOPY VARCHAR2
1861 ) is
1862 cursor get_obj_version_number_csr is
1863 select object_version_number
1864 from HZ_MERGE_ENTITY_ATTRIBUTES
1865 where merge_batch_id = p_merge_batch_id
1866 and merge_to_party_id = p_merge_to_party_id
1867 and entity_name = p_entity_name
1868 and attribute_name = p_attribute_name;
1869
1870 cursor get_org_last_update_date_csr is
1871 select last_update_date
1872 from hz_organization_profiles
1873 where party_id = p_attribute_party_id
1874 and sysdate between effective_start_date and nvl(effective_end_date,sysdate);
1875
1876 cursor get_per_last_update_date_csr is
1877 select last_update_date
1878 from hz_person_profiles
1879 where party_id = p_attribute_party_id
1880 and sysdate between effective_start_date and nvl(effective_end_date,sysdate);
1881
1882 l_object_version_number number;
1883 l_last_update_date date;
1884 l_lookup_type varchar2(30);
1885
1886 begin
1887
1888 savepoint update_merge_attributes_pvt;
1889 -- Initialize return status to SUCCESS
1890 x_return_status := FND_API.G_RET_STS_SUCCESS;
1891
1892 if p_entity_name = 'HZ_ORGANIZATION_PROFILES'
1893 then
1894 l_lookup_type := 'ORG_PROFILE_CATEGORY';
1895 elsif p_entity_name = 'HZ_PERSON_PROFILES'
1896 then l_lookup_type := 'PERSON_PROFILE_CATEGORY';
1897 end if;
1898
1899 if is_leading_attribute(p_attribute_name,l_lookup_type) = 'N'
1900 then
1901 /* if pass in attribute name is not leading attri, no update action */
1902 return;
1903
1904 end if;
1905
1906 open get_obj_version_number_csr;
1907 fetch get_obj_version_number_csr into l_object_version_number;
1908 close get_obj_version_number_csr;
1909
1910 IF (l_object_version_number <> px_object_version_number) THEN
1911 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1912 -- row has been changed by another user.
1913 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
1914 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_MERGE_ENTITY_ATTRIBUTES');
1915 FND_MSG_PUB.ADD;
1916 RAISE FND_API.G_EXC_ERROR;
1917 END IF;
1918 END IF;
1919
1920 px_object_version_number := nvl(l_object_version_number,1)+1;
1921
1922 if p_entity_name = 'HZ_ORGANIZATION_PROFILES'
1923 then
1924 open get_org_last_update_date_csr;
1925 fetch get_org_last_update_date_csr into l_last_update_date;
1926 close get_org_last_update_date_csr;
1927 elsif p_entity_name = 'HZ_PERSON_PROFILES'
1928 then
1929 open get_per_last_update_date_csr;
1930 fetch get_per_last_update_date_csr into l_last_update_date;
1931 close get_per_last_update_date_csr;
1932 end if;
1933 if p_attribute_name in ('KNOWN_AS','KNOWN_AS2','KNOWN_AS3','KNOWN_AS4','KNOWN_AS5')
1934 then
1935 update HZ_MERGE_ENTITY_ATTRIBUTES
1936 set attribute_value = p_attribute_value,
1937 attribute_party_id = p_attribute_party_id,
1938 derived_last_update_date = decode(p_attribute_party_id,null,SYSDATE,l_last_update_date),
1939 last_update_date = SYSDATE,
1940 last_update_login = FND_GLOBAL.LOGIN_ID,
1941 last_updated_by = FND_GLOBAL.USER_ID,
1942 object_version_number = px_object_version_number
1943 where attribute_name = p_attribute_name
1944 and merge_batch_id = p_merge_batch_id
1945 and merge_to_party_id = p_merge_to_party_id
1946 and entity_name = p_entity_name;
1947 else
1948 if p_attribute_party_id is not null -- not user entered
1949 then
1950 update_group_attribute_value(p_attribute_name=>p_attribute_name,
1951 p_candidate_party_id => p_attribute_party_id,
1952 p_merge_batch_id => p_merge_batch_id,
1953 p_merge_to_party_id =>p_merge_to_party_id,
1954 p_derived_last_update_date => l_last_update_date,
1955 p_entity_name => p_entity_name,
1956 p_proc_type => 'U');
1957 update HZ_MERGE_ENTITY_ATTRIBUTES
1958 set object_version_number = px_object_version_number
1959 where attribute_name = p_attribute_name
1960 and merge_batch_id = p_merge_batch_id
1961 and merge_to_party_id = p_merge_to_party_id
1962 and entity_name = p_entity_name;
1963 end if;
1964 end if;
1965
1966 UPDATE HZ_DUP_SETS
1967 SET STATUS = 'MAPPING',
1968 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
1969 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
1970 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
1971 object_version_number = nvl(object_version_number,1)+1
1972 WHERE DUP_SET_ID = p_merge_batch_id;
1973
1974 EXCEPTION
1975
1976 WHEN FND_API.G_EXC_ERROR THEN
1977 ROLLBACK TO update_merge_attributes_pvt;
1978 x_return_status := FND_API.G_RET_STS_ERROR;
1979 FND_MSG_PUB.Count_And_Get(
1980 p_encoded => FND_API.G_FALSE,
1981 p_count => x_msg_count,
1982 p_data => x_msg_data);
1983
1984 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1985 ROLLBACK TO update_merge_attributes_pvt ;
1986 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1987 FND_MSG_PUB.Count_And_Get(
1988 p_encoded => FND_API.G_FALSE,
1989 p_count => x_msg_count,
1990 p_data => x_msg_data);
1991
1992 WHEN OTHERS THEN
1993 ROLLBACK TO update_merge_attributes_pvt;
1994 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1995 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1996 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1997 FND_MSG_PUB.ADD;
1998 FND_MSG_PUB.Count_And_Get(
1999 p_encoded => FND_API.G_FALSE,
2000 p_count => x_msg_count,
2001 p_data => x_msg_data);
2002
2003 end update_merge_attribute;
2004
2005 /* This function is called in profile attribute merge UI */
2006 function get_attri_value_meaning(p_profile_type in varchar2,p_attri_name in varchar2, p_attri_value in varchar2)
2007 return varchar2 is
2008 begin
2009
2010 if p_profile_type = 'ORGANIZATION'
2011 then
2012
2013 if p_attri_name = 'SIC_CODE_TYPE'
2014 then
2015 return HZ_UTILITY_v2PUB.Get_LookupMeaning('AR_LOOKUPS','SIC_CODE_TYPE',p_attri_value);
2019 elsif p_attri_name = 'HQ_BRANCH_IND'
2016 elsif p_attri_name = 'LEGAL_STATUS'
2017 then
2018 return HZ_UTILITY_v2PUB.Get_LookupMeaning('AR_LOOKUPS','LEGAL_STATUS',p_attri_value);
2020 then
2021 return HZ_UTILITY_v2PUB.Get_LookupMeaning('AR_LOOKUPS','HQ_BRANCH_IND',p_attri_value);
2022 elsif p_attri_name = 'FISCAL_YEAREND_MONTH'
2023 then
2024 return HZ_UTILITY_v2PUB.Get_LookupMeaning('AR_LOOKUPS','MONTH',p_attri_value);
2025 elsif p_attri_name = 'LOCAL_BUS_IDEN_TYPE'
2026 then
2027 return HZ_UTILITY_v2PUB.Get_LookupMeaning('AR_LOOKUPS','LOCAL_BUS_IDEN_TYPE',p_attri_value);
2028 elsif p_attri_name = 'REGISTRATION_TYPE'
2029 then
2030 return HZ_UTILITY_v2PUB.Get_LookupMeaning('AR_LOOKUPS','REGISTRATION_TYPE',p_attri_value);
2031 elsif p_attri_name = 'LOCAL_ACTIVITY_CODE_TYPE'
2032 then
2033 return HZ_UTILITY_v2PUB.Get_LookupMeaning('AR_LOOKUPS','LOCAL_ACTIVITY_CODE_TYPE',p_attri_value);
2034
2035 elsif p_attri_name = 'LOCAL_ACTIVITY_CODE'
2036 then
2037 return HZ_UTILITY_v2PUB.Get_LookupMeaning('AR_LOOKUPS','NACE',p_attri_value);
2038 elsif p_attri_name = 'REGISTRATION_TYPE'
2039 then
2040 return HZ_UTILITY_v2PUB.Get_LookupMeaning('AR_LOOKUPS','REGISTRATION_TYPE',p_attri_value);
2041 elsif p_attri_name = 'TOTAL_EMPLOYEES_IND'
2042 then
2043 return HZ_UTILITY_v2PUB.Get_LookupMeaning('AR_LOOKUPS','TOTAL_EMPLOYEES_INDICATOR',p_attri_value);
2044 elsif p_attri_name = 'TOTAL_EMP_EST_IND'
2045 then
2046 return HZ_UTILITY_v2PUB.Get_LookupMeaning('AR_LOOKUPS','TOTAL_EMP_EST_IND', p_attri_value);
2047 elsif p_attri_name = 'TOTAL_EMP_MIN_IND'
2048 then
2049 return HZ_UTILITY_v2PUB.Get_LookupMeaning('AR_LOOKUPS','TOTAL_EMP_MIN_IND', p_attri_value);
2050 elsif p_attri_name = 'EMP_AT_PRIMARY_ADR_EST_IND'
2051 then
2052 return HZ_UTILITY_v2PUB.Get_LookupMeaning('AR_LOOKUPS','EMP_AT_PRIMARY_ADR_EST_IND',p_attri_value);
2053 elsif p_attri_name = 'EMP_AT_PRIMARY_ADR_MIN_IND'
2054 then
2055 return HZ_UTILITY_v2PUB.Get_LookupMeaning('AR_LOOKUPS','EMP_AT_PRIMARY_ADR_MIN_IND',p_attri_value);
2056 elsif p_attri_value in ('Y','N')
2057 then
2058 return HZ_UTILITY_v2PUB.Get_LookupMeaning('AR_LOOKUPS','YES/NO',p_attri_value);
2059 else return p_attri_value;
2060 end if;
2061 elsif p_profile_type = 'PERSON'
2062 then
2063 if p_attri_name = 'PERSON_PRE_NAME_ADJUNCT'
2064 then
2065 return HZ_UTILITY_v2PUB.Get_LookupMeaning('AR_LOOKUPS','CONTACT_TITLE',p_attri_value);
2066 elsif p_attri_name = 'MARITAL_STATUS'
2067 then
2068 return HZ_UTILITY_v2PUB.Get_LookupMeaning('AR_LOOKUPS','MARITAL_STATUS',p_attri_value);
2069 elsif p_attri_value in ('Y','N')
2070 then
2071 return HZ_UTILITY_v2PUB.Get_LookupMeaning('AR_LOOKUPS','YES/NO',p_attri_value);
2072 --Start bug 7279978
2073 elsif p_attri_name = 'RENT_OWN_IND'
2074 then
2075 return HZ_UTILITY_v2PUB.Get_LookupMeaning('AR_LOOKUPS','OWN_RENT_IND',p_attri_value);
2076 --End bug 7279978
2077 else return p_attri_value;
2078 end if;
2079 end if;
2080
2081 end get_attri_value_meaning;
2082 function get_party_last_upd_date(p_attribute_party_id in number, p_entity_name in varchar2) return date is
2083
2084 cursor get_org_last_update_date_csr is
2085 select last_update_date
2086 from hz_organization_profiles
2087 where party_id = p_attribute_party_id
2088 and sysdate between effective_start_date and nvl(effective_end_date,sysdate);
2089
2090 cursor get_per_last_update_date_csr is
2091 select last_update_date
2092 from hz_person_profiles
2093 where party_id = p_attribute_party_id
2094 and sysdate between effective_start_date and nvl(effective_end_date,sysdate);
2095 l_last_update_date date;
2096 begin
2097 if p_entity_name = 'HZ_ORGANIZATION_PROFILES'
2098 then
2099 open get_org_last_update_date_csr;
2100 fetch get_org_last_update_date_csr into l_last_update_date;
2101 close get_org_last_update_date_csr;
2102 elsif p_entity_name = 'HZ_PERSON_PROFILES'
2103 then
2104 open get_per_last_update_date_csr;
2105 fetch get_per_last_update_date_csr into l_last_update_date;
2106 close get_per_last_update_date_csr;
2107 end if;
2108 return l_last_update_date;
2109 end get_party_last_upd_date;
2110
2111 function is_attri_party_data_changed(p_merge_batch_id in varchar2,p_entity_name in varchar2) return
2112 varchar2 is
2113 cursor org_party_data_changed_csr(l_attribute_party_id number) is
2114 select 'x'
2115 from hz_merge_entity_attributes
2116 where merge_batch_id = p_merge_batch_id
2117 and attribute_name <> 'BUSINESS_REPORT'
2118 and attribute_party_id = l_attribute_party_id and rownum = 1
2119 and derived_last_update_date <> (select last_update_date
2120 from hz_organization_profiles
2121 where party_Id =l_attribute_party_id
2122 and sysdate between effective_start_date and nvl(effective_end_date,sysdate));
2123
2124 cursor person_party_data_changed_csr(l_attribute_party_id number) is
2125 select 'x'
2126 from hz_merge_entity_attributes
2127 where merge_batch_id = p_merge_batch_id
2128 and attribute_party_id = l_attribute_party_id and rownum = 1
2129 and derived_last_update_date <> (select last_update_date
2130 from hz_person_profiles
2131 where party_Id = l_attribute_party_id
2132 and sysdate between effective_start_date and nvl(effective_end_date,sysdate));
2133 cursor get_attri_party_id_csr is
2134 select distinct attribute_party_id
2135 from hz_merge_entity_attributes
2136 where merge_batch_id = p_merge_batch_id;
2137
2138 l_tmp varchar2(1);
2139 l_attribute_party_id number;
2140 begin
2141 open get_attri_party_id_csr;
2142 loop
2143 fetch get_attri_party_id_csr into l_attribute_party_id;
2144 exit when get_attri_party_id_csr%NOTFOUND;
2145
2146 if p_entity_name = 'HZ_ORGANIZATION_PROFILES'
2147 then
2148 open org_party_data_changed_csr(l_attribute_party_id);
2149 fetch org_party_data_changed_csr into l_tmp;
2150 if org_party_data_changed_csr%FOUND
2151 then
2152 close org_party_data_changed_csr;
2153 return 'Y';
2154 end if;
2155 close org_party_data_changed_csr;
2156 elsif p_entity_name = 'HZ_PERSON_PROFILES'
2157 then
2158 open person_party_data_changed_csr(l_attribute_party_id);
2159 fetch person_party_data_changed_csr into l_tmp;
2160 if person_party_data_changed_csr%FOUND
2161 then
2162 close person_party_data_changed_csr;
2163 return 'Y';
2164 end if;
2165 close person_party_data_changed_csr;
2166 end if;
2167 end loop;
2168 close get_attri_party_id_csr;
2169 return 'N';
2170
2171 end is_attri_party_data_changed;
2172
2173 /* sync up attribute values in hz_merge_entity_attributes and hz_organization/person_profiles*/
2174 PROCEDURE sync_merge_attributes(
2175 p_merge_batch_id IN NUMBER,
2176 p_merge_to_party_id IN NUMBER,
2177 p_entity_name IN VARCHAR2,
2178 x_return_status OUT NOCOPY VARCHAR2,
2179 x_msg_count OUT NOCOPY NUMBER,
2180 x_msg_data OUT NOCOPY VARCHAR2
2181 ) is
2182 cursor get_attri_party_id_csr is
2183 select distinct attribute_party_id, derived_last_update_date
2184 from hz_merge_entity_attributes
2185 where merge_batch_id = p_merge_batch_id
2186 and merge_to_party_id = p_merge_to_party_id
2187 and attribute_name <> 'BUSINESS_REPORT';
2188
2189 cursor get_dirty_attri_names_csr(l_attribute_party_id number) is
2190 select attribute_name, entity_name
2191 from hz_merge_entity_attributes attri
2192 where merge_batch_id = p_merge_batch_id
2193 and merge_to_party_id = p_merge_to_party_id
2194 and attribute_party_id = l_attribute_party_id
2195 and attribute_name <> 'BUSINESS_REPORT';
2196
2197 cursor attri_party_id_count_csr is
2198 select count(distinct attribute_party_id)
2199 from hz_merge_entity_attributes
2200 where merge_batch_id = p_merge_batch_id
2201 and merge_to_party_id = p_merge_to_party_id
2202 and attribute_party_id <> p_merge_to_party_id;
2203
2204 l_count number;
2205 l_attribute_name varchar2(30);
2206 l_attri_party_id number;
2207 l_entity_name varchar2(30);
2208 l_value varchar2(2000);
2209 l_derived_last_upd_date date;
2210 l_party_last_upd_date date;
2211 begin
2212 -- Initialize return status to SUCCESS
2213 x_return_status := FND_API.G_RET_STS_SUCCESS;
2214
2215 if is_attri_party_data_changed(p_merge_batch_id, p_entity_name) = 'N'
2216 then
2217 --dbms_output.put_line('no data change');
2218
2219 return;
2220 else
2221 open attri_party_id_count_csr;
2222 fetch attri_party_id_count_csr into l_count;
2223 close attri_party_id_count_csr;
2224
2225 if l_count = 0 -- only orig default(master party) is there, we can re-default.
2226 then
2227 delete from hz_merge_entity_attributes
2228 where merge_batch_id = p_merge_batch_id;
2229
2230 create_merge_attributes(
2231 p_merge_batch_id => p_merge_batch_id,
2232 p_merge_to_party_id =>p_merge_to_party_id,
2233 p_entity_name =>p_entity_name,
2234 x_return_status =>x_return_status,
2235 x_msg_count =>x_msg_count,
2236 x_msg_data =>x_msg_data);
2237 else -- data has been updated by the user
2238
2239 open get_attri_party_id_csr;
2240 loop
2241 fetch get_attri_party_id_csr into l_attri_party_id,l_derived_last_upd_date;
2242 exit when get_attri_party_id_csr%NOTFOUND;
2243 l_party_last_upd_date := get_party_last_upd_date(l_attri_party_id,p_entity_name);
2244 if l_derived_last_upd_date <> l_party_last_upd_date
2245 then
2246 open get_dirty_attri_names_csr(l_attri_party_id);
2247 loop
2248 fetch get_dirty_attri_names_csr into l_attribute_name, l_entity_name;
2249 exit when get_dirty_attri_names_csr%NOTFOUND;
2250 l_value := get_attribute_value(l_attribute_name, l_attri_party_id,l_entity_name);
2251
2252 --dbms_output.put_line('l_value = '||l_value);
2253 --dbms_output.put_line('l_attribute_name = '||l_attribute_name);
2254
2255 update hz_merge_entity_attributes
2256 set attribute_value = l_value, -- value formatted already
2257 derived_last_update_date = l_party_last_upd_date,
2258 last_update_date = SYSDATE,
2259 last_update_login = FND_GLOBAL.LOGIN_ID,
2260 last_updated_by = FND_GLOBAL.USER_ID,
2261 object_version_number = nvl(object_version_number,1)+1
2262 where attribute_name = l_attribute_name
2263 and merge_batch_id = p_merge_batch_id
2264 and entity_name = p_entity_name
2265 and merge_to_party_id =p_merge_to_party_id
2266 and attribute_party_id = l_attri_party_id;
2267 end loop;
2268 close get_dirty_attri_names_csr;
2269 end if;
2270 end loop;
2271 close get_attri_party_id_csr;
2272 end if; -- if l_count = 0
2273 end if;
2274 EXCEPTION
2275 WHEN OTHERS THEN
2276 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2277 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2278 FND_MSG_PUB.ADD;
2279 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2280 end sync_merge_attributes;
2281
2282 END HZ_MERGE_ENTITY_ATTRI_PVT;