[Home] [Help]
PACKAGE BODY: APPS.HZ_CUST_ACCOUNT_MERGE_V2PVT
Source
1 PACKAGE BODY hz_cust_account_merge_v2pvt AS
2 /*$Header: ARHACTMB.pls 120.20 2010/12/21 06:25:44 vsegu ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'HZ_CUST_ACCOUNT_MERGE_V2PVT';
5
6 g_pst_mixnmatch_enabled VARCHAR2(1);
7 g_pst_selected_datasources VARCHAR2(255);
8 g_pst_is_datasource_selected VARCHAR2(1) := 'N';
9 g_pst_entity_attr_id NUMBER;
10
11
12 g_cpt_mixnmatch_enabled VARCHAR2(1);
13 g_cpt_selected_datasources VARCHAR2(255);
14 g_cpt_is_datasource_selected VARCHAR2(1) := 'N';
15 g_cpt_entity_attr_id NUMBER;
16
17 g_rel_mixnmatch_enabled VARCHAR2(1);
18 g_rel_selected_datasources VARCHAR2(255);
19 g_rel_is_datasource_selected VARCHAR2(1) := 'N';
20 g_rel_entity_attr_id NUMBER;
21
22 -----------------------------PARTY------------------------------------------
23
24 TYPE party_dup_rec_type IS RECORD(
25 sic_code VARCHAR2(30),
26 sic_code_type VARCHAR2(30),
27 hq_branch_ind VARCHAR2(2),
28 tax_reference VARCHAR2(50),
29 jgzz_fiscal_code VARCHAR2(20),
30 duns_number_c VARCHAR2(30),
31 pre_name_adjunct VARCHAR2(30),
32 first_name VARCHAR2(150),
33 middle_name VARCHAR2(60),
34 last_name VARCHAR2(150),
35 name_suffix VARCHAR2(30),
36 title VARCHAR2(60),
37 academic_title VARCHAR2(260),
38 previous_last_name VARCHAR2(150),
39 known_as VARCHAR2(240),
40 known_as2 VARCHAR2(240),
41 known_as3 VARCHAR2(240),
42 known_as4 VARCHAR2(240),
43 known_as5 VARCHAR2(240),
44 person_iden_type VARCHAR2(5),
45 person_identifier VARCHAR2(60),
46 country VARCHAR2(60),
47 address1 VARCHAR2(240),
48 address2 VARCHAR2(240),
49 address3 VARCHAR2(240),
50 address4 VARCHAR2(240),
51 city VARCHAR2(60),
52 postal_code VARCHAR2(60),
53 state VARCHAR2(60),
54 province VARCHAR2(60),
55 county VARCHAR2(60),
56 url VARCHAR2(2000),
57 email_address VARCHAR2(2000),
58 next_fy_potential_revenue NUMBER,
59 mission_statement VARCHAR2(2000),
60 organization_name_phonetic VARCHAR2(320),
61 person_first_name_phonetic VARCHAR2(60),
62 person_last_name_phonetic VARCHAR2(60),
63 middle_name_phonetic VARCHAR2(60),
64 language_name VARCHAR2(4),
65 analysis_fy VARCHAR2(5),
66 fiscal_yearend_month VARCHAR2(30),
67 employees_total NUMBER,
68 curr_fy_potential_revenue NUMBER,
69 year_established NUMBER,
70 gsa_indicator_flag VARCHAR2(1),
71 created_by_module VARCHAR2(150),
72 application_id NUMBER
73 );
74
75 PROCEDURE get_party_rec (
76 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
77 p_party_id IN NUMBER,
78 x_party_rec OUT NOCOPY hz_party_v2pub.party_rec_type,
79 x_return_status OUT NOCOPY VARCHAR2,
80 x_msg_count OUT NOCOPY NUMBER,
81 x_msg_data OUT NOCOPY VARCHAR2
82 ) IS
83
84 l_api_name CONSTANT VARCHAR2(30) := 'get_party_rec';
85 x_party_dup_rec party_dup_rec_type;
86 l_party_name hz_parties.party_name%TYPE;
87 l_party_type hz_parties.party_type%TYPE;
88 l_customer_key hz_parties.customer_key%TYPE;
89 l_group_type hz_parties.group_type%TYPE;
90 l_country hz_parties.country%TYPE;
91 l_address1 hz_parties.address1%TYPE;
92 l_address2 hz_parties.address2%TYPE;
93 l_address3 hz_parties.address3%TYPE;
94 l_address4 hz_parties.address4%TYPE;
95 l_city hz_parties.city%TYPE;
96 l_state hz_parties.state%TYPE;
97 l_postal_code hz_parties.postal_code%TYPE;
98 l_province hz_parties.province%TYPE;
99 l_county hz_parties.county%TYPE;
100 l_url hz_parties.url%TYPE;
101 l_email_address hz_parties.email_address%TYPE;
102 l_language_name hz_parties.language_name%TYPE;
103 l_created_by_module hz_parties.created_by_module%TYPE;
104 l_application_id NUMBER;
105
106 BEGIN
107
108 --Initialize message list if p_init_msg_list is set to TRUE.
109 IF fnd_api.to_boolean(p_init_msg_list) THEN
110 fnd_msg_pub.initialize;
111 END IF;
112
113 --Initialize API return status to success.
114 x_return_status := fnd_api.g_ret_sts_success;
115
116 --Check whether primary key has been passed in.
117 IF p_party_id IS NULL OR
118 p_party_id = fnd_api.g_miss_num THEN
119 fnd_message.set_name( 'AR', 'HZ_API_MISSING_COLUMN' );
120 fnd_message.set_token( 'COLUMN', 'party_id' );
121 fnd_msg_pub.add;
122 RAISE fnd_api.g_exc_error;
123 END IF;
124
125 x_party_rec.party_id := p_party_id;
126
127 hz_parties_pkg.select_row (
128 X_PARTY_ID => x_party_rec.party_id,
129 X_PARTY_NUMBER => x_party_rec.party_number,
130 X_PARTY_NAME => l_party_name,
131 X_PARTY_TYPE => l_party_type,
132 X_VALIDATED_FLAG => x_party_rec.validated_flag,
133 X_ATTRIBUTE_CATEGORY => x_party_rec.attribute_category,
134 X_ATTRIBUTE1 => x_party_rec.attribute1,
135 X_ATTRIBUTE2 => x_party_rec.attribute2,
136 X_ATTRIBUTE3 => x_party_rec.attribute3,
137 X_ATTRIBUTE4 => x_party_rec.attribute4,
138 X_ATTRIBUTE5 => x_party_rec.attribute5,
139 X_ATTRIBUTE6 => x_party_rec.attribute6,
140 X_ATTRIBUTE7 => x_party_rec.attribute7,
141 X_ATTRIBUTE8 => x_party_rec.attribute8,
142 X_ATTRIBUTE9 => x_party_rec.attribute9,
143 X_ATTRIBUTE10 => x_party_rec.attribute10,
144 X_ATTRIBUTE11 => x_party_rec.attribute11,
145 X_ATTRIBUTE12 => x_party_rec.attribute12,
146 X_ATTRIBUTE13 => x_party_rec.attribute13,
147 X_ATTRIBUTE14 => x_party_rec.attribute14,
148 X_ATTRIBUTE15 => x_party_rec.attribute15,
149 X_ATTRIBUTE16 => x_party_rec.attribute16,
150 X_ATTRIBUTE17 => x_party_rec.attribute17,
151 X_ATTRIBUTE18 => x_party_rec.attribute18,
152 X_ATTRIBUTE19 => x_party_rec.attribute19,
153 X_ATTRIBUTE20 => x_party_rec.attribute20,
154 X_ATTRIBUTE21 => x_party_rec.attribute21,
155 X_ATTRIBUTE22 => x_party_rec.attribute22,
156 X_ATTRIBUTE23 => x_party_rec.attribute23,
157 X_ATTRIBUTE24 => x_party_rec.attribute24,
158 X_ORIG_SYSTEM_REFERENCE => x_party_rec.orig_system_reference,
159 X_SIC_CODE => x_party_dup_rec.sic_code,
160 X_HQ_BRANCH_IND => x_party_dup_rec.hq_branch_ind,
161 X_CUSTOMER_KEY => l_customer_key,
162 X_TAX_REFERENCE => x_party_dup_rec.tax_reference,
163 X_JGZZ_FISCAL_CODE => x_party_dup_rec.jgzz_fiscal_code,
164 X_PERSON_PRE_NAME_ADJUNCT => x_party_dup_rec.pre_name_adjunct,
165 X_PERSON_FIRST_NAME => x_party_dup_rec.first_name,
166 X_PERSON_MIDDLE_NAME => x_party_dup_rec.middle_name,
167 X_PERSON_LAST_NAME => x_party_dup_rec.last_name,
168 X_PERSON_NAME_SUFFIX => x_party_dup_rec.name_suffix,
169 X_PERSON_TITLE => x_party_dup_rec.title,
170 X_PERSON_ACADEMIC_TITLE => x_party_dup_rec.academic_title,
171 X_PERSON_PREVIOUS_LAST_NAME => x_party_dup_rec.previous_last_name,
172 X_KNOWN_AS => x_party_dup_rec.known_as,
173 X_PERSON_IDEN_TYPE => x_party_dup_rec.person_iden_type,
174 X_PERSON_IDENTIFIER => x_party_dup_rec.person_identifier,
175 X_GROUP_TYPE => l_group_type,
176 X_COUNTRY => l_country,
177 X_ADDRESS1 => l_address1,
178 X_ADDRESS2 => l_address2,
179 X_ADDRESS3 => l_address3,
180 X_ADDRESS4 => l_address4,
181 X_CITY => l_city,
182 X_POSTAL_CODE => l_postal_code,
183 X_STATE => l_state,
184 X_PROVINCE => l_province,
185 X_STATUS => x_party_rec.status,
186 X_COUNTY => l_county,
187 X_SIC_CODE_TYPE => x_party_dup_rec.sic_code_type,
188 X_URL => l_url,
189 X_EMAIL_ADDRESS => l_email_address,
190 X_ANALYSIS_FY => x_party_dup_rec.analysis_fy,
191 X_FISCAL_YEAREND_MONTH => x_party_dup_rec.fiscal_yearend_month,
192 X_EMPLOYEES_TOTAL => x_party_dup_rec.employees_total,
193 X_CURR_FY_POTENTIAL_REVENUE => x_party_dup_rec.curr_fy_potential_revenue,
194 X_NEXT_FY_POTENTIAL_REVENUE => x_party_dup_rec.next_fy_potential_revenue,
195 X_YEAR_ESTABLISHED => x_party_dup_rec.year_established,
196 X_GSA_INDICATOR_FLAG => x_party_dup_rec.gsa_indicator_flag,
197 X_MISSION_STATEMENT => x_party_dup_rec.mission_statement,
198 X_ORGANIZATION_NAME_PHONETIC => x_party_dup_rec.organization_name_phonetic,
199 X_PERSON_FIRST_NAME_PHONETIC => x_party_dup_rec.person_first_name_phonetic,
200 X_PERSON_LAST_NAME_PHONETIC => x_party_dup_rec.person_last_name_phonetic,
201 X_LANGUAGE_NAME => l_language_name,
202 X_CATEGORY_CODE => x_party_rec.category_code,
203 X_SALUTATION => x_party_rec.salutation,
204 X_KNOWN_AS2 => x_party_dup_rec.known_as2,
205 X_KNOWN_AS3 => x_party_dup_rec.known_as3,
206 X_KNOWN_AS4 => x_party_dup_rec.known_as4,
207 X_KNOWN_AS5 => x_party_dup_rec.known_as5,
208 X_DUNS_NUMBER_C => x_party_dup_rec.duns_number_c,
209 X_CREATED_BY_MODULE => l_created_by_module,
210 X_APPLICATION_ID => l_application_id
211 );
212
213 --Standard call to get message count and if count is 1, get message info.
214 FND_MSG_PUB.Count_And_Get(
215 p_encoded => FND_API.G_FALSE,
216 p_count => x_msg_count,
217 p_data => x_msg_data );
218
219 EXCEPTION
220 WHEN FND_API.G_EXC_ERROR THEN
221 x_return_status := FND_API.G_RET_STS_ERROR;
222
223 FND_MSG_PUB.Count_And_Get(
224 p_encoded => FND_API.G_FALSE,
225 p_count => x_msg_count,
226 p_data => x_msg_data );
227
228 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
229 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
230
231 FND_MSG_PUB.Count_And_Get(
232 p_encoded => FND_API.G_FALSE,
233 p_count => x_msg_count,
234 p_data => x_msg_data );
235
236 WHEN OTHERS THEN
237 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
238
239 fnd_message.set_name( 'AR', 'HZ_API_OTHERS_EXCEP' );
240 fnd_message.set_token( 'ERROR' ,SQLERRM );
241 fnd_msg_pub.add;
242
243 FND_MSG_PUB.Count_And_Get(
244 p_encoded => FND_API.G_FALSE,
245 p_count => x_msg_count,
246 p_data => x_msg_data );
247
248 END get_party_rec;
249
250 ---------------------------RELATIONSHIP----------------------------------------------------
251
252 PROCEDURE do_create_rel(
253 p_relationship_rec IN OUT NOCOPY HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE,
254 p_direction_code IN VARCHAR2,
255 x_created_party OUT NOCOPY VARCHAR2,
256 x_relationship_id OUT NOCOPY NUMBER,
257 x_party_id OUT NOCOPY NUMBER,
258 x_party_number OUT NOCOPY VARCHAR2,
259 x_return_status IN OUT NOCOPY VARCHAR2
260 );
261
262 PROCEDURE do_create_party(
263 p_party_type IN VARCHAR2,
264 p_relationship_rec IN HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE,
265 x_party_id OUT NOCOPY NUMBER,
266 x_party_number OUT NOCOPY VARCHAR2,
267 x_profile_id OUT NOCOPY NUMBER,
268 x_return_status IN OUT NOCOPY VARCHAR2
269 );
270
271 PROCEDURE do_update_party_flags(
272 p_relationship_rec IN HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE,
273 p_party_id IN NUMBER
274 );
275
276
277 PROCEDURE do_unmark_primary_per_type(
278 p_party_id IN NUMBER,
279 p_party_site_id IN NUMBER,
280 p_site_use_type IN VARCHAR2
281 );
282
283 -----------------------------
284 -- body of private procedures
285 -----------------------------
286
287 PROCEDURE do_create_rel(
288 p_relationship_rec IN OUT NOCOPY HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE,
289 p_direction_code IN VARCHAR2,
290 x_created_party OUT NOCOPY VARCHAR2,
291 x_relationship_id OUT NOCOPY NUMBER,
292 x_party_id OUT NOCOPY NUMBER,
293 x_party_number OUT NOCOPY VARCHAR2,
294 x_return_status IN OUT NOCOPY VARCHAR2
295 ) IS
296
297 -- this cursor will uniquely identify a record
298 -- in hz_relationship_types package
299 CURSOR c_rel_type
300 IS
301 SELECT RELATIONSHIP_TYPE,
302 FORWARD_REL_CODE,
303 BACKWARD_REL_CODE,
304 CREATE_PARTY_FLAG,
305 ALLOW_RELATE_TO_SELF_FLAG,
306 HIERARCHICAL_FLAG,
307 ALLOW_CIRCULAR_RELATIONSHIPS,
308 DIRECTION_CODE,
309 RELATIONSHIP_TYPE_ID,
310 MULTIPLE_PARENT_ALLOWED
311 FROM HZ_RELATIONSHIP_TYPES
312 WHERE RELATIONSHIP_TYPE = p_relationship_rec.relationship_type
313 AND FORWARD_REL_CODE = p_relationship_rec.relationship_code
314 AND SUBJECT_TYPE = p_relationship_rec.subject_type
315 AND OBJECT_TYPE = p_relationship_rec.object_type
316 AND STATUS = 'A';
317
318 r_rel_type c_rel_type%ROWTYPE;
319
320 -- this cursor retrieves all parents for a given child in a particular
321 --hierarchy.
322 -- it will be used for circularity check.
323 CURSOR c_parent1 (p_parent_id NUMBER, p_parent_table_name VARCHAR2,
324 p_parent_object_type VARCHAR2)
325 IS
326 SELECT SUBJECT_ID,
327 SUBJECT_TABLE_NAME,
328 SUBJECT_TYPE
329 FROM HZ_RELATIONSHIPS
330 START WITH OBJECT_ID = p_parent_id
331 AND OBJECT_TABLE_NAME = p_parent_table_name
332 AND OBJECT_TYPE = p_parent_object_type
333 AND DIRECTION_CODE = 'P'
334 AND RELATIONSHIP_TYPE = p_relationship_rec.relationship_type
335 AND ( START_DATE BETWEEN NVL(p_relationship_rec.start_date, SYSDATE) AND
336 NVL(p_relationship_rec.end_date, TO_DATE('31-12-4712 00:00:00','DD-MM-YYYY HH24:MI:SS'))
337 OR END_DATE BETWEEN NVL(p_relationship_rec.start_date, SYSDATE)
338 AND NVL(p_relationship_rec.end_date, TO_DATE('31-12-4712 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))
339 OR
340 NVL(p_relationship_rec.start_date, SYSDATE) BETWEEN START_DATE AND END_DATE
341 OR
342 NVL(p_relationship_rec.end_date, TO_DATE('31-12-4712 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) BETWEEN START_DATE AND END_DATE)
343 CONNECT BY OBJECT_ID = PRIOR SUBJECT_ID AND OBJECT_TYPE = PRIOR SUBJECT_TYPE AND OBJECT_TABLE_NAME = PRIOR SUBJECT_TABLE_NAME
344 AND DIRECTION_CODE = 'P' AND RELATIONSHIP_TYPE = p_relationship_rec.relationship_type
345 AND (START_DATE BETWEEN NVL(p_relationship_rec.start_date, SYSDATE)
346 AND NVL(p_relationship_rec.end_date, TO_DATE('31-12-4712 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))
347 OR
348 END_DATE BETWEEN NVL(p_relationship_rec.start_date, SYSDATE)
349 AND NVL(p_relationship_rec.end_date, TO_DATE('31-12-4712 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))
350 OR
351 NVL(p_relationship_rec.start_date, SYSDATE) BETWEEN START_DATE AND END_DATE
352 OR
353 NVL(p_relationship_rec.end_date, TO_DATE('31-12-4712 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) BETWEEN START_DATE AND END_DATE);
354
355 -- this cursor retrieves all child for a given parent in a particular hierarchy
356 -- it will be used for circularity check.
357 CURSOR c_child1 (p_child_id NUMBER, p_child_table_name VARCHAR2,
358 p_child_object_type VARCHAR2)
359 IS
360 SELECT OBJECT_ID,
361 OBJECT_TABLE_NAME,
362 OBJECT_TYPE
363 FROM HZ_RELATIONSHIPS
364 START WITH SUBJECT_ID = p_child_id
365 AND SUBJECT_TABLE_NAME = p_child_table_name
366 AND SUBJECT_TYPE = p_child_object_type
367 AND DIRECTION_CODE = 'P'
368 AND RELATIONSHIP_TYPE = p_relationship_rec.relationship_type
369 AND (START_DATE BETWEEN NVL(p_relationship_rec.start_date, SYSDATE)
370 AND NVL(p_relationship_rec.end_date, TO_DATE('31-12-4712 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))
371 OR
372 END_DATE BETWEEN NVL(p_relationship_rec.start_date, SYSDATE)
373 AND NVL(p_relationship_rec.end_date, TO_DATE('31-12-4712 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))
374 OR
375 NVL(p_relationship_rec.start_date, SYSDATE) BETWEEN START_DATE AND END_DATE
376 OR
377 NVL(p_relationship_rec.end_date, TO_DATE('31-12-4712 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) BETWEEN START_DATE AND END_DATE)
378 CONNECT BY SUBJECT_ID = PRIOR OBJECT_ID AND SUBJECT_TYPE = PRIOR OBJECT_TYPE AND SUBJECT_TABLE_NAME = PRIOR OBJECT_TABLE_NAME
379 AND DIRECTION_CODE = 'P' AND RELATIONSHIP_TYPE = p_relationship_rec.relationship_type
380 AND (START_DATE BETWEEN NVL(p_relationship_rec.start_date, SYSDATE)
381 AND NVL(p_relationship_rec.end_date, TO_DATE('31-12-4712 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))
382 OR
383 END_DATE BETWEEN NVL(p_relationship_rec.start_date, SYSDATE)
384 AND NVL(p_relationship_rec.end_date, TO_DATE('31-12-4712 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))
385 OR
386 NVL(p_relationship_rec.start_date, SYSDATE) BETWEEN START_DATE AND END_DATE
387 OR
388 NVL(p_relationship_rec.end_date, TO_DATE('31-12-4712 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) BETWEEN START_DATE AND END_DATE);
389
390 r_parent1 c_parent1%rowtype;
391 r_child1 c_child1%rowtype;
392
393 l_relationship_id NUMBER := p_relationship_rec.relationship_id;
394 l_rowid ROWID := NULL;
395 l_count NUMBER;
396 l_profile_id NUMBER;
397 l_directional_flag VARCHAR2(1);
398 l_msg_count NUMBER;
399 l_msg_data VARCHAR2(2000);
400 l_end_date DATE;
401 l_party_rel_rec HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE;
402 l_dummy VARCHAR2(1) := 'Y';
403 l_debug_prefix VARCHAR2(30) := '';
404 l_back_direction VARCHAR2(30);
405
406 l_hierarchy_rec HZ_HIERARCHY_PUB.HIERARCHY_NODE_REC_TYPE;
407 l_parent_id NUMBER;
408 l_parent_object_type VARCHAR2(30);
409 l_parent_table_name VARCHAR2(30);
410 l_child_id NUMBER;
411 l_child_object_type VARCHAR2(30);
412 l_child_table_name VARCHAR2(30);
413 l_parent_flag VARCHAR2(1);
414
415 l_invalid_rel_type VARCHAR2(1) := 'N';
416 l_multiple_parent VARCHAR2(1) := 'N';
417 l_circular_flag VARCHAR2(1) := 'N';
418 l_invalid_rel_to_self VARCHAR2(1) := 'N';
419
420
421 BEGIN
422
423 ---Initialize the created_by module
424 p_relationship_rec.created_by_module := 'HZ_TCA_CUSTOMER_MERGE';
425
426 ---set the relationship_id to null
427 p_relationship_rec.relationship_id := null;
428
429
430 -- Generate primary key from sequence if not passed in. If this values already exists in
431 -- the database, keep generating till a unique value is found.
432 -- If primary key value is passed, check for uniqueness.
433
434 WHILE l_dummy = 'Y' LOOP
435 BEGIN
436 SELECT HZ_RELATIONSHIPS_S.NEXTVAL
437 INTO l_relationship_id
438 FROM DUAL;
439
440 SELECT 'Y'
441 INTO l_dummy
442 FROM HZ_RELATIONSHIPS
443 WHERE RELATIONSHIP_ID = l_relationship_id
444 AND DIRECTIONAL_FLAG = 'F';
445
446 EXCEPTION
447 WHEN NO_DATA_FOUND THEN
448 l_dummy := 'N';
449 END;
450 END LOOP;
451
452 x_relationship_id := l_relationship_id;
453
454 -- default end date to 31-DEC-4712
455 IF p_relationship_rec.end_date IS NULL
456 OR
457 p_relationship_rec.end_date = FND_API.G_MISS_DATE
458 THEN
459 l_end_date := to_date('12/31/4712','MM/DD/YYYY');
460 ELSE
461 l_end_date := p_relationship_rec.end_date;
462 END IF;
463
464 -- Open the relationship_type record and get all the info
465 OPEN c_rel_type;
466 FETCH c_rel_type INTO r_rel_type;
467 CLOSE c_rel_type;
468
469 if r_rel_type.relationship_type is null THEN
470 l_invalid_rel_type := 'Y';
471 end if;
472
473 --create as long as valid
474 if l_invalid_rel_type <> 'Y' then
475
476 -- decide who is parent and who is child in this relationship.
477 -- if relationship type record is 'P' type, then subject is parent, else object
478 IF r_rel_type.direction_code = 'P' THEN
479 l_parent_id := p_relationship_rec.subject_id;
480 l_parent_table_name := p_relationship_rec.subject_table_name;
481 l_parent_object_type := p_relationship_rec.subject_type;
482 l_child_id := p_relationship_rec.object_id;
483 l_child_table_name := p_relationship_rec.object_table_name;
484 l_child_object_type := p_relationship_rec.object_type;
485 ELSIF r_rel_type.direction_code = 'C' THEN
486 l_parent_id := p_relationship_rec.object_id;
487 l_parent_table_name := p_relationship_rec.object_table_name;
488 l_parent_object_type := p_relationship_rec.object_type;
489 l_child_id := p_relationship_rec.subject_id;
490 l_child_table_name := p_relationship_rec.subject_table_name;
491 l_child_object_type := p_relationship_rec.subject_type;
492 END IF;
493
494 -- if the relationship type is hierarchical, then we have to check
495 -- whether there is already a parent present for the child in the same
496 -- hierarchy/relationship type. if so, then we would not allow creation.
497 IF r_rel_type.hierarchical_flag = 'Y' THEN
498 -- it needs to be done if multiple_parent_allowed is 'N'
499 IF r_rel_type.multiple_parent_allowed = 'N' THEN
500 BEGIN
501 SELECT 1 INTO l_count
502 FROM HZ_RELATIONSHIPS
503 WHERE OBJECT_ID = l_child_id
504 AND OBJECT_TABLE_NAME = l_child_table_name
505 AND OBJECT_TYPE = l_child_object_type
506 AND RELATIONSHIP_TYPE = p_relationship_rec.relationship_type
507 AND DIRECTION_CODE = 'P'
508 AND (START_DATE BETWEEN NVL(p_relationship_rec.start_date, SYSDATE)
509 AND NVL(p_relationship_rec.end_date, TO_DATE('31-12-4712 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))
510 OR
511 END_DATE BETWEEN NVL(p_relationship_rec.start_date, SYSDATE)
512 AND NVL(p_relationship_rec.end_date, TO_DATE('31-12-4712 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))
513 OR
514 NVL(p_relationship_rec.start_date, SYSDATE) BETWEEN START_DATE AND END_DATE
515 OR
516 NVL(p_relationship_rec.end_date, TO_DATE('31-12-4712 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) BETWEEN START_DATE AND END_DATE
517 );
518
519 l_multiple_parent := 'Y';
520
521 EXCEPTION
522 WHEN NO_DATA_FOUND THEN
523 -- no parent found, proceed
524 NULL;
525 END;
526 END IF;
527 END IF;
528
529 -- if the relationship type does not allow circular, then we
530 -- have to check whether creation of the current relationship
531 -- will introduce circularity in the tree. if so, we would
532 -- prevent that by erroring out.
533 IF r_rel_type.hierarchical_flag = 'Y' OR
534 r_rel_type.allow_circular_relationships = 'N'
535 THEN
536 l_parent_flag := 'Y';
537 OPEN c_parent1 (l_parent_id, l_parent_table_name, l_parent_object_type);
538 FETCH c_parent1 INTO r_parent1;
539 WHILE c_parent1%FOUND LOOP
540 IF r_parent1.subject_id = l_child_id THEN
541 l_circular_flag := 'Y';
542 CLOSE c_parent1;
543 END IF;
544 FETCH c_parent1 INTO r_parent1;
545 END LOOP;
546 CLOSE c_parent1;
547 END IF;
548
549 IF r_rel_type.hierarchical_flag = 'Y' OR
550 r_rel_type.allow_circular_relationships = 'N'
551 THEN
552 l_parent_flag := 'Y';
553 OPEN c_child1 (l_child_id, l_child_table_name, l_child_object_type);
554 FETCH c_child1 INTO r_child1;
555 WHILE c_child1%FOUND LOOP
556 IF r_child1.object_id = l_child_id THEN
557 l_circular_flag := 'Y';
558 CLOSE c_child1;
559 END IF;
560 FETCH c_child1 INTO r_child1;
561 END LOOP;
562 CLOSE c_child1;
563 END IF;
564
565 -- subject_id and object_id must not have the same value,
566 -- unless relationship type allows
567 IF r_rel_type.allow_relate_to_self_flag = 'N'
568 AND
569 p_relationship_rec.subject_id = p_relationship_rec.object_id
570 THEN
571 l_invalid_rel_to_self := 'Y';
572 END IF;
573
574 END IF ; ---for valid rel_type
575
576 IF ( l_invalid_rel_type <> 'Y'AND l_multiple_parent <> 'Y'
577 AND l_circular_flag <> 'Y' AND l_invalid_rel_to_self <> 'Y' ) THEN
578
579 -- build the record for creation of relationship party record
580 l_party_rel_rec.party_rec := p_relationship_rec.party_rec;
581 l_party_rel_rec.subject_id := p_relationship_rec.subject_id;
582 l_party_rel_rec.object_id := p_relationship_rec.object_id;
583 l_party_rel_rec.created_by_module := p_relationship_rec.created_by_module;
584 l_party_rel_rec.application_id := p_relationship_rec.application_id;
585
586
587 -- the PARTY_RELATIONSHIP type party will be created if
588 -- the relationship type has create_party_flag = 'Y' and
589 -- both the subject_table_name and object_table_name are
590 -- 'HZ_PARTIES'
591 IF r_rel_type.create_party_flag = 'Y'
592 AND
593 p_relationship_rec.subject_table_name = 'HZ_PARTIES'
594 AND
595 p_relationship_rec.object_table_name = 'HZ_PARTIES'
596 THEN
597 x_created_party := 'Y';
598 do_create_party(
599 p_party_type => 'PARTY_RELATIONSHIP',
600 p_relationship_rec => l_party_rel_rec,
601 x_party_id => x_party_id,
602 x_party_number => x_party_number,
603 x_profile_id => l_profile_id,
604 x_return_status => x_return_status
605 );
606 p_relationship_rec.party_rec.party_id := x_party_id;
607 p_relationship_rec.party_rec.party_number := x_party_number;
608
609 ELSE
610 x_created_party := 'N';
611 END IF;
612
613 -- Denormalize flags to HZ_PARTIES:
614 -- COMPETITOR_FLAG
615 -- REFERENCE_USE_FLAG
616 -- THIRD_PARTY_FLAG
617 -- Denormalization will be done only if content_source_type
618 -- is 'USER_ENTERED' and both subject_table_name and
619 -- object_table_name are 'HZ_PARTIES'
620
621 -- Bug 2197181: added for mix-n-match project. Denormalize
622 -- the three flags when the data source is visible (i.e.
623 -- selected).
624
625 -- SSM SST Integration and Extension
626 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
627 -- There is no need to check if the data-source is selected.
628
629 IF p_relationship_rec.relationship_code IN
630 ('COMPETITOR_OF', 'REFERENCE_FOR', 'PARTNER_OF')
631 --AND g_rel_is_datasource_selected = 'Y'
632 AND p_relationship_rec.subject_table_name = 'HZ_PARTIES'
633 AND p_relationship_rec.object_table_name = 'HZ_PARTIES' THEN
634
635 do_update_party_flags(
636 p_relationship_rec,
637 p_relationship_rec.subject_id);
638 END IF;
639
640
641
642 p_relationship_rec.relationship_id := l_relationship_id;
643
644 -- Call table-handler to create the forward record.
645 HZ_RELATIONSHIPS_PKG.Insert_Row (
646 X_RELATIONSHIP_ID => p_relationship_rec.relationship_id,
647 X_SUBJECT_ID => p_relationship_rec.subject_id,
648 X_SUBJECT_TYPE => p_relationship_rec.subject_type,
649 X_SUBJECT_TABLE_NAME => p_relationship_rec.subject_table_name,
650 X_OBJECT_ID => p_relationship_rec.object_id,
651 X_OBJECT_TYPE => p_relationship_rec.object_type,
652 X_OBJECT_TABLE_NAME => p_relationship_rec.object_table_name,
653 X_PARTY_ID => x_party_id,
654 X_RELATIONSHIP_CODE => p_relationship_rec.relationship_code,
655 X_DIRECTIONAL_FLAG => 'F',
656 X_COMMENTS => p_relationship_rec.comments,
657 X_START_DATE => p_relationship_rec.start_date,
658 X_END_DATE => l_end_date,
659 X_STATUS => p_relationship_rec.status,
660 X_ATTRIBUTE_CATEGORY => p_relationship_rec.attribute_category,
661 X_ATTRIBUTE1 => p_relationship_rec.attribute1,
662 X_ATTRIBUTE2 => p_relationship_rec.attribute2,
663 X_ATTRIBUTE3 => p_relationship_rec.attribute3,
664 X_ATTRIBUTE4 => p_relationship_rec.attribute4,
665 X_ATTRIBUTE5 => p_relationship_rec.attribute5,
666 X_ATTRIBUTE6 => p_relationship_rec.attribute6,
667 X_ATTRIBUTE7 => p_relationship_rec.attribute7,
668 X_ATTRIBUTE8 => p_relationship_rec.attribute8,
669 X_ATTRIBUTE9 => p_relationship_rec.attribute9,
670 X_ATTRIBUTE10 => p_relationship_rec.attribute10,
671 X_ATTRIBUTE11 => p_relationship_rec.attribute11,
672 X_ATTRIBUTE12 => p_relationship_rec.attribute12,
673 X_ATTRIBUTE13 => p_relationship_rec.attribute13,
674 X_ATTRIBUTE14 => p_relationship_rec.attribute14,
675 X_ATTRIBUTE15 => p_relationship_rec.attribute15,
676 X_ATTRIBUTE16 => p_relationship_rec.attribute16,
677 X_ATTRIBUTE17 => p_relationship_rec.attribute17,
678 X_ATTRIBUTE18 => p_relationship_rec.attribute18,
679 X_ATTRIBUTE19 => p_relationship_rec.attribute19,
680 X_ATTRIBUTE20 => p_relationship_rec.attribute20,
681 X_CONTENT_SOURCE_TYPE => p_relationship_rec.content_source_type,
682 X_RELATIONSHIP_TYPE => p_relationship_rec.relationship_type,
683 X_OBJECT_VERSION_NUMBER => 1,
684 X_CREATED_BY_MODULE => p_relationship_rec.created_by_module,
685 X_APPLICATION_ID => p_relationship_rec.application_id,
686 X_ADDITIONAL_INFORMATION1 => p_relationship_rec.additional_information1,
687 X_ADDITIONAL_INFORMATION2 => p_relationship_rec.additional_information2,
688 X_ADDITIONAL_INFORMATION3 => p_relationship_rec.additional_information3,
689 X_ADDITIONAL_INFORMATION4 => p_relationship_rec.additional_information4,
690 X_ADDITIONAL_INFORMATION5 => p_relationship_rec.additional_information5,
691 X_ADDITIONAL_INFORMATION6 => p_relationship_rec.additional_information6,
692 X_ADDITIONAL_INFORMATION7 => p_relationship_rec.additional_information7,
693 X_ADDITIONAL_INFORMATION8 => p_relationship_rec.additional_information8,
694 X_ADDITIONAL_INFORMATION9 => p_relationship_rec.additional_information9,
695 X_ADDITIONAL_INFORMATION10 => p_relationship_rec.additional_information10,
696 X_ADDITIONAL_INFORMATION11 => p_relationship_rec.additional_information11,
697 X_ADDITIONAL_INFORMATION12 => p_relationship_rec.additional_information12,
698 X_ADDITIONAL_INFORMATION13 => p_relationship_rec.additional_information13,
699 X_ADDITIONAL_INFORMATION14 => p_relationship_rec.additional_information14,
700 X_ADDITIONAL_INFORMATION15 => p_relationship_rec.additional_information15,
701 X_ADDITIONAL_INFORMATION16 => p_relationship_rec.additional_information16,
702 X_ADDITIONAL_INFORMATION17 => p_relationship_rec.additional_information17,
703 X_ADDITIONAL_INFORMATION18 => p_relationship_rec.additional_information18,
704 X_ADDITIONAL_INFORMATION19 => p_relationship_rec.additional_information19,
705 X_ADDITIONAL_INFORMATION20 => p_relationship_rec.additional_information20,
706 X_ADDITIONAL_INFORMATION21 => p_relationship_rec.additional_information21,
707 X_ADDITIONAL_INFORMATION22 => p_relationship_rec.additional_information22,
708 X_ADDITIONAL_INFORMATION23 => p_relationship_rec.additional_information23,
709 X_ADDITIONAL_INFORMATION24 => p_relationship_rec.additional_information24,
710 X_ADDITIONAL_INFORMATION25 => p_relationship_rec.additional_information25,
711 X_ADDITIONAL_INFORMATION26 => p_relationship_rec.additional_information26,
712 X_ADDITIONAL_INFORMATION27 => p_relationship_rec.additional_information27,
713 X_ADDITIONAL_INFORMATION28 => p_relationship_rec.additional_information28,
714 X_ADDITIONAL_INFORMATION29 => p_relationship_rec.additional_information29,
715 X_ADDITIONAL_INFORMATION30 => p_relationship_rec.additional_information30,
716 X_DIRECTION_CODE => r_rel_type.direction_code,
717 X_PERCENTAGE_OWNERSHIP => p_relationship_rec.percentage_ownership,
718 X_ACTUAL_CONTENT_SOURCE => p_relationship_rec.ACTUAL_CONTENT_SOURCE
719 );
720
721 -- Call table-handler again to create the backward record.
722 -- This is done because for every relationship we want to
723 -- create both forward and backward relationship.
724
725 --If there was no backward record originally do not create it
726
727 -- determine the direction_code for the backward record
728 IF r_rel_type.direction_code = 'P' THEN
729 l_back_direction := 'C';
730 ELSIF r_rel_type.direction_code = 'C' THEN
731 l_back_direction := 'P';
732 ELSE
733 l_back_direction := 'N';
734 END IF;
735
736 IF r_rel_type.BACKWARD_REL_CODE is not null THEN
737
738 HZ_RELATIONSHIPS_PKG.Insert_Row (
739 X_RELATIONSHIP_ID => p_relationship_rec.relationship_id,
740 X_SUBJECT_ID => p_relationship_rec.object_id,
741 X_SUBJECT_TYPE => p_relationship_rec.object_type,
742 X_SUBJECT_TABLE_NAME => p_relationship_rec.object_table_name,
743 X_OBJECT_ID => p_relationship_rec.subject_id,
744 X_OBJECT_TYPE => p_relationship_rec.subject_type,
745 X_OBJECT_TABLE_NAME => p_relationship_rec.subject_table_name,
746 X_PARTY_ID => x_party_id,
747 X_RELATIONSHIP_CODE => r_rel_type.backward_rel_code,
748 X_DIRECTIONAL_FLAG => 'B',
749 X_COMMENTS => p_relationship_rec.comments,
750 X_START_DATE => p_relationship_rec.start_date,
751 X_END_DATE => l_end_date,
752 X_STATUS => p_relationship_rec.status,
753 X_ATTRIBUTE_CATEGORY => p_relationship_rec.attribute_category,
754 X_ATTRIBUTE1 => p_relationship_rec.attribute1,
755 X_ATTRIBUTE2 => p_relationship_rec.attribute2,
756 X_ATTRIBUTE3 => p_relationship_rec.attribute3,
757 X_ATTRIBUTE4 => p_relationship_rec.attribute4,
758 X_ATTRIBUTE5 => p_relationship_rec.attribute5,
759 X_ATTRIBUTE6 => p_relationship_rec.attribute6,
760 X_ATTRIBUTE7 => p_relationship_rec.attribute7,
761 X_ATTRIBUTE8 => p_relationship_rec.attribute8,
762 X_ATTRIBUTE9 => p_relationship_rec.attribute9,
763 X_ATTRIBUTE10 => p_relationship_rec.attribute10,
764 X_ATTRIBUTE11 => p_relationship_rec.attribute11,
765 X_ATTRIBUTE12 => p_relationship_rec.attribute12,
766 X_ATTRIBUTE13 => p_relationship_rec.attribute13,
767 X_ATTRIBUTE14 => p_relationship_rec.attribute14,
768 X_ATTRIBUTE15 => p_relationship_rec.attribute15,
769 X_ATTRIBUTE16 => p_relationship_rec.attribute16,
770 X_ATTRIBUTE17 => p_relationship_rec.attribute17,
771 X_ATTRIBUTE18 => p_relationship_rec.attribute18,
772 X_ATTRIBUTE19 => p_relationship_rec.attribute19,
773 X_ATTRIBUTE20 => p_relationship_rec.attribute20,
774 X_CONTENT_SOURCE_TYPE => p_relationship_rec.content_source_type,
775 X_RELATIONSHIP_TYPE => r_rel_type.relationship_type,
776 X_OBJECT_VERSION_NUMBER => 1,
777 X_CREATED_BY_MODULE => p_relationship_rec.created_by_module,
778 X_APPLICATION_ID => p_relationship_rec.application_id,
779 X_ADDITIONAL_INFORMATION1 => p_relationship_rec.additional_information1,
780 X_ADDITIONAL_INFORMATION2 => p_relationship_rec.additional_information2,
781 X_ADDITIONAL_INFORMATION3 => p_relationship_rec.additional_information3,
782 X_ADDITIONAL_INFORMATION4 => p_relationship_rec.additional_information4,
783 X_ADDITIONAL_INFORMATION5 => p_relationship_rec.additional_information5,
784 X_ADDITIONAL_INFORMATION6 => p_relationship_rec.additional_information6,
785 X_ADDITIONAL_INFORMATION7 => p_relationship_rec.additional_information7,
786 X_ADDITIONAL_INFORMATION8 => p_relationship_rec.additional_information8,
787 X_ADDITIONAL_INFORMATION9 => p_relationship_rec.additional_information9,
788 X_ADDITIONAL_INFORMATION10 => p_relationship_rec.additional_information10,
789 X_ADDITIONAL_INFORMATION11 => p_relationship_rec.additional_information11,
790 X_ADDITIONAL_INFORMATION12 => p_relationship_rec.additional_information12,
791 X_ADDITIONAL_INFORMATION13 => p_relationship_rec.additional_information13,
792 X_ADDITIONAL_INFORMATION14 => p_relationship_rec.additional_information14,
793 X_ADDITIONAL_INFORMATION15 => p_relationship_rec.additional_information15,
794 X_ADDITIONAL_INFORMATION16 => p_relationship_rec.additional_information16,
795 X_ADDITIONAL_INFORMATION17 => p_relationship_rec.additional_information17,
796 X_ADDITIONAL_INFORMATION18 => p_relationship_rec.additional_information18,
797 X_ADDITIONAL_INFORMATION19 => p_relationship_rec.additional_information19,
798 X_ADDITIONAL_INFORMATION20 => p_relationship_rec.additional_information20,
799 X_ADDITIONAL_INFORMATION21 => p_relationship_rec.additional_information21,
800 X_ADDITIONAL_INFORMATION22 => p_relationship_rec.additional_information22,
801 X_ADDITIONAL_INFORMATION23 => p_relationship_rec.additional_information23,
802 X_ADDITIONAL_INFORMATION24 => p_relationship_rec.additional_information24,
803 X_ADDITIONAL_INFORMATION25 => p_relationship_rec.additional_information25,
804 X_ADDITIONAL_INFORMATION26 => p_relationship_rec.additional_information26,
805 X_ADDITIONAL_INFORMATION27 => p_relationship_rec.additional_information27,
806 X_ADDITIONAL_INFORMATION28 => p_relationship_rec.additional_information28,
807 X_ADDITIONAL_INFORMATION29 => p_relationship_rec.additional_information29,
808 X_ADDITIONAL_INFORMATION30 => p_relationship_rec.additional_information30,
809 X_DIRECTION_CODE => l_back_direction,
810 X_PERCENTAGE_OWNERSHIP => p_relationship_rec.percentage_ownership,
811 X_ACTUAL_CONTENT_SOURCE => p_relationship_rec.ACTUAL_CONTENT_SOURCE
812 );
813
814 END IF;
815
816 -- maintain hierarchy of relationships
817 -- check if the relationship type is hierarchical
818 IF r_rel_type.hierarchical_flag = 'Y' THEN
819 -- check if relationship type is parent one
820 IF r_rel_type.direction_code = 'P' THEN
821 -- assign the subject to parent for hierarchy
822 l_hierarchy_rec.hierarchy_type := r_rel_type.relationship_type;
823 l_hierarchy_rec.parent_id := p_relationship_rec.subject_id;
824 l_hierarchy_rec.parent_table_name := p_relationship_rec.subject_table_name;
825 l_hierarchy_rec.parent_object_type := p_relationship_rec.subject_type;
826 l_hierarchy_rec.child_id := p_relationship_rec.object_id;
827 l_hierarchy_rec.child_table_name := p_relationship_rec.object_table_name;
828 l_hierarchy_rec.child_object_type := p_relationship_rec.object_type;
829 l_hierarchy_rec.effective_start_date := p_relationship_rec.start_date;
830 l_hierarchy_rec.effective_end_date := l_end_date;
831 l_hierarchy_rec.relationship_id := p_relationship_rec.relationship_id;
832 l_hierarchy_rec.status := NVL(p_relationship_rec.status, 'A');
833 ELSIF r_rel_type.direction_code = 'C' THEN
834 -- assign the object to parent
835 l_hierarchy_rec.hierarchy_type := r_rel_type.relationship_type;
836 l_hierarchy_rec.parent_id := p_relationship_rec.object_id;
837 l_hierarchy_rec.parent_table_name := p_relationship_rec.object_table_name;
838 l_hierarchy_rec.parent_object_type := p_relationship_rec.object_type;
839 l_hierarchy_rec.child_id := p_relationship_rec.subject_id;
840 l_hierarchy_rec.child_table_name := p_relationship_rec.subject_table_name;
841 l_hierarchy_rec.child_object_type := p_relationship_rec.subject_type;
842 l_hierarchy_rec.effective_start_date := p_relationship_rec.start_date;
843 l_hierarchy_rec.effective_end_date := l_end_date;
844 l_hierarchy_rec.relationship_id := p_relationship_rec.relationship_id;
845 l_hierarchy_rec.status := NVL(p_relationship_rec.status, 'A');
846 END IF;
847
848 HZ_HIERARCHY_PUB.create_link(
849 p_init_msg_list => FND_API.G_FALSE,
850 p_hierarchy_node_rec => l_hierarchy_rec,
851 x_return_status => x_return_status,
852 x_msg_count => l_msg_count,
853 x_msg_data => l_msg_data
854 );
855 END IF; --r_rel_type.hierarchical_flag 'Y'
856
857 ELSE --l_invalid_rel_type <> 'Y'AND l_multiple_parent <> 'Y'
858 X_RELATIONSHIP_ID := NULL; ---no rel was created
859 END IF; --l_invalid_rel_type <> 'Y'AND l_multiple_parent <> 'Y'
860
861 END do_create_rel;
862
863
864 PROCEDURE do_create_party(
865 p_party_type IN VARCHAR2,
866 p_relationship_rec IN HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE,
867 x_party_id OUT NOCOPY NUMBER,
868 x_party_number OUT NOCOPY VARCHAR2,
869 x_profile_id OUT NOCOPY NUMBER,
870 x_return_status IN OUT NOCOPY VARCHAR2
871 ) IS
872
873 l_party_id NUMBER;
874 l_party_number VARCHAR2(30);
875 l_generate_party_number VARCHAR2(1);
876 l_rowid ROWID := NULL;
877 l_count NUMBER;
878 l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE := p_relationship_rec.party_rec;
879 l_party_name hz_parties.PARTY_NAME%TYPE;
880 l_subject_name hz_parties.PARTY_NAME%TYPE;
881 l_object_name hz_parties.PARTY_NAME%TYPE;
882 l_customer_key hz_parties.CUSTOMER_KEY%TYPE;
883 l_code_assignment_id NUMBER;
884 l_msg_count NUMBER;
885 l_msg_data VARCHAR2(2000);
886 l_dummy VARCHAR2(1);
887 l_debug_prefix VARCHAR2(30) := '';
888 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
889
890 BEGIN
891
892 l_party_rec.orig_system_reference := null;
893 l_party_rec.party_id := null;
894
895 -- build the party_name for relationship party
896 SELECT PARTY_NAME
897 INTO l_subject_name
898 FROM hz_parties
899 WHERE PARTY_ID = p_relationship_rec.subject_id;
900
901 SELECT PARTY_NAME
902 INTO l_object_name
903 FROM hz_parties
904 WHERE PARTY_ID = p_relationship_rec.object_id;
905
906 l_party_name := SUBSTRB(l_subject_name || '-' ||
907 l_object_name || '-' ||
908 l_party_number, 1, 360);
909
910
911
912 hz_parties_PKG.Insert_Row (
913 X_PARTY_ID => l_party_rec.party_id,
914 X_PARTY_NUMBER => l_party_rec.party_number,
915 X_PARTY_NAME => l_party_name,
916 X_PARTY_TYPE => p_party_type,
917 X_VALIDATED_FLAG => l_party_rec.validated_flag,
918 X_ATTRIBUTE_CATEGORY => l_party_rec.attribute_category,
919 X_ATTRIBUTE1 => l_party_rec.attribute1,
920 X_ATTRIBUTE2 => l_party_rec.attribute2,
921 X_ATTRIBUTE3 => l_party_rec.attribute3,
922 X_ATTRIBUTE4 => l_party_rec.attribute4,
923 X_ATTRIBUTE5 => l_party_rec.attribute5,
924 X_ATTRIBUTE6 => l_party_rec.attribute6,
925 X_ATTRIBUTE7 => l_party_rec.attribute7,
926 X_ATTRIBUTE8 => l_party_rec.attribute8,
927 X_ATTRIBUTE9 => l_party_rec.attribute9,
928 X_ATTRIBUTE10 => l_party_rec.attribute10,
929 X_ATTRIBUTE11 => l_party_rec.attribute11,
930 X_ATTRIBUTE12 => l_party_rec.attribute12,
931 X_ATTRIBUTE13 => l_party_rec.attribute13,
932 X_ATTRIBUTE14 => l_party_rec.attribute14,
933 X_ATTRIBUTE15 => l_party_rec.attribute15,
934 X_ATTRIBUTE16 => l_party_rec.attribute16,
935 X_ATTRIBUTE17 => l_party_rec.attribute17,
936 X_ATTRIBUTE18 => l_party_rec.attribute18,
937 X_ATTRIBUTE19 => l_party_rec.attribute19,
938 X_ATTRIBUTE20 => l_party_rec.attribute20,
939 X_ATTRIBUTE21 => l_party_rec.attribute21,
940 X_ATTRIBUTE22 => l_party_rec.attribute22,
941 X_ATTRIBUTE23 => l_party_rec.attribute23,
942 X_ATTRIBUTE24 => l_party_rec.attribute24,
943 X_ORIG_SYSTEM_REFERENCE => l_party_rec.orig_system_reference,
944 X_SIC_CODE => null,
945 X_HQ_BRANCH_IND => null,
946 X_CUSTOMER_KEY => null,
947 X_TAX_REFERENCE => null,
948 X_JGZZ_FISCAL_CODE => null,
949 X_PERSON_PRE_NAME_ADJUNCT => null,
950 X_PERSON_FIRST_NAME => null,
951 X_PERSON_MIDDLE_NAME => null,
952 X_PERSON_LAST_NAME => null,
953 X_PERSON_NAME_SUFFIX => null,
954 X_PERSON_TITLE => null,
955 X_PERSON_ACADEMIC_TITLE => null,
956 X_PERSON_PREVIOUS_LAST_NAME => null,
957 X_KNOWN_AS => null,
958 X_PERSON_IDEN_TYPE => null,
959 X_PERSON_IDENTIFIER => null,
960 X_GROUP_TYPE => null,
961 X_COUNTRY => NULL,
962 X_ADDRESS1 => NULL,
963 X_ADDRESS2 => NULL,
964 X_ADDRESS3 => NULL,
965 X_ADDRESS4 => NULL,
966 X_CITY => NULL,
967 X_POSTAL_CODE => NULL,
968 X_STATE => NULL,
969 X_PROVINCE => NULL,
970 X_STATUS => l_party_rec.status,
971 X_COUNTY => NULL,
972 X_SIC_CODE_TYPE => null,
973 X_URL => NULL,
974 X_EMAIL_ADDRESS => NULL,
975 X_ANALYSIS_FY => null,
976 X_FISCAL_YEAREND_MONTH => null,
977 X_EMPLOYEES_TOTAL => null,
978 X_CURR_FY_POTENTIAL_REVENUE => null,
979 X_NEXT_FY_POTENTIAL_REVENUE => null,
980 X_YEAR_ESTABLISHED => null,
981 X_GSA_INDICATOR_FLAG => null,
982 X_MISSION_STATEMENT => null,
983 X_ORGANIZATION_NAME_PHONETIC => null,
984 X_PERSON_FIRST_NAME_PHONETIC => null,
985 X_PERSON_LAST_NAME_PHONETIC => null,
986 X_LANGUAGE_NAME => NULL,
987 X_CATEGORY_CODE => l_party_rec.category_code,
988 X_SALUTATION => l_party_rec.salutation,
989 X_KNOWN_AS2 => null,
990 X_KNOWN_AS3 => null,
991 X_KNOWN_AS4 => null,
992 X_KNOWN_AS5 => null,
993 X_OBJECT_VERSION_NUMBER => 1,
994 X_DUNS_NUMBER_C => null,
995 X_CREATED_BY_MODULE => p_relationship_rec.created_by_module,
996 X_APPLICATION_ID => p_relationship_rec.application_id
997 );
998
999 /*
1000 per HLD,mosr record should not be created for copy case, since old osr is still active
1001 hz_orig_system_ref_pvt.create_mosr_for_merge(
1002 FND_API.G_FALSE,
1003 'HZ_PARTIES',
1004 l_party_rec.party_id,
1005 x_return_status,
1006 l_msg_count,
1007 l_msg_data);
1008
1009 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1010 RAISE FND_API.G_EXC_ERROR;
1011 END IF;
1012 */
1013 x_party_id := l_party_rec.party_id;
1014 x_party_number := l_party_rec.party_number;
1015
1016 -- update the party_name
1017 l_party_name := SUBSTRB(l_subject_name || '-' ||
1018 l_object_name || '-' ||
1019 x_party_number, 1, 360);
1020
1021 UPDATE hz_parties SET PARTY_NAME = l_party_name WHERE PARTY_ID = x_party_id;
1022
1023 END do_create_party;
1024
1025 PROCEDURE do_update_party_flags(
1026 p_relationship_rec IN HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE,
1027 p_party_id IN NUMBER
1028 ) IS
1029
1030 l_party_id NUMBER;
1031 l_reference_use_flag VARCHAR2(1) := 'N';
1032 l_third_party_flag VARCHAR2(1) := 'N';
1033 l_competitor_flag VARCHAR2(1) := 'N';
1034 l_end_date DATE := p_relationship_rec.end_date;
1035 l_status VARCHAR2(1) := p_relationship_rec.status;
1036 l_record_locked VARCHAR2(1) := 'N';
1037
1038 BEGIN
1039
1040 --check if party record is locked by any one else.
1041 BEGIN
1042 SELECT party_id INTO l_party_id
1043 FROM hz_parties
1044 WHERE party_id = p_party_id
1045 FOR UPDATE NOWAIT;
1046 EXCEPTION WHEN OTHERS THEN
1047 l_record_locked := 'Y';
1048 END;
1049
1050 IF l_end_date IS NULL
1051 OR l_end_date = FND_API.G_MISS_DATE
1052 THEN
1053 l_end_date := to_date('31-12-4712', 'DD-MM-YYYY');
1054 ELSIF l_end_date = sysdate THEN
1055 l_end_date := sysdate-1;
1056 END IF;
1057
1058 IF l_status IS NULL
1059 OR l_status = FND_API.G_MISS_CHAR
1060 THEN
1061 l_status := 'A';
1062 END IF;
1063
1064 IF p_relationship_rec.relationship_code = 'COMPETITOR_OF' THEN
1065 IF l_status = 'A'
1066 AND
1067 (SYSDATE BETWEEN p_relationship_rec.start_date AND l_end_date)
1068 THEN
1069 l_competitor_flag := 'Y';
1070 END IF;
1071
1072 UPDATE HZ_PARTIES
1073 SET COMPETITOR_FLAG = l_competitor_flag
1074 WHERE PARTY_ID = p_party_id;
1075
1076 ELSIF p_relationship_rec.relationship_code = 'REFERENCE_FOR' THEN
1077 IF l_status = 'A'
1078 AND
1079 (SYSDATE BETWEEN p_relationship_rec.start_date AND l_end_date)
1080 THEN
1081 l_reference_use_flag := 'Y';
1082 END IF;
1083
1084 UPDATE HZ_PARTIES
1085 SET REFERENCE_USE_FLAG = l_reference_use_flag
1086 WHERE PARTY_ID = p_party_id;
1087
1088 ELSIF p_relationship_rec.relationship_code = 'PARTNER_OF' THEN
1089 IF l_status = 'A'
1090 AND (SYSDATE BETWEEN p_relationship_rec.start_date AND l_end_date)
1091 THEN
1092 l_third_party_flag := 'Y';
1093 END IF;
1094
1095 UPDATE HZ_PARTIES
1096 SET THIRD_PARTY_FLAG = l_third_party_flag
1097 WHERE PARTY_ID = p_party_id;
1098
1099 END IF;
1100
1101 END do_update_party_flags;
1102
1103
1104
1105
1106 PROCEDURE create_relationship (
1107 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
1108 p_relationship_rec IN HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE,
1109 p_direction_code IN VARCHAR2,
1110 x_relationship_id OUT NOCOPY NUMBER,
1111 x_party_id OUT NOCOPY NUMBER,
1112 x_party_number OUT NOCOPY VARCHAR2,
1113 x_return_status OUT NOCOPY VARCHAR2,
1114 x_msg_count OUT NOCOPY NUMBER,
1115 x_msg_data OUT NOCOPY VARCHAR2
1116 ) IS
1117
1118 l_rel_rec HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE := p_relationship_rec;
1119 l_created_party VARCHAR2(1);
1120
1121 BEGIN
1122
1123 -- Standard start of API savepoint
1124 SAVEPOINT create_relationship;
1125
1126 -- Initialize message list if p_init_msg_list is set to TRUE.
1127 IF FND_API.to_Boolean(p_init_msg_list) THEN
1128 FND_MSG_PUB.initialize;
1129 END IF;
1130
1131 -- Initialize API return status to success.
1132 x_return_status := FND_API.G_RET_STS_SUCCESS;
1133
1134 /* SSM SST Integration and Extension
1135 * For non-profile entities, the concept of select/de-select data-sources is obsoleted.
1136
1137 IF g_rel_mixnmatch_enabled IS NULL THEN
1138 HZ_MIXNM_UTILITY.LoadDataSources(
1139 p_entity_name => 'HZ_RELATIONSHIPS',
1140 p_entity_attr_id => g_rel_entity_attr_id,
1141 p_mixnmatch_enabled => g_rel_mixnmatch_enabled,
1142 p_selected_datasources => g_rel_selected_datasources );
1143 END IF;
1144 */
1145 HZ_MIXNM_UTILITY.AssignDataSourceDuringCreation (
1146 p_entity_name => 'HZ_RELATIONSHIPS',
1147 p_entity_attr_id => g_rel_entity_attr_id,
1148 p_mixnmatch_enabled => g_rel_mixnmatch_enabled,
1149 p_selected_datasources => g_rel_selected_datasources,
1150 p_content_source_type => l_rel_rec.content_source_type,
1151 p_actual_content_source => l_rel_rec.actual_content_source,
1152 x_is_datasource_selected => g_rel_is_datasource_selected,
1153 x_return_status => x_return_status );
1154
1155
1156 -- Call to business logic.
1157 do_create_rel(
1158 l_rel_rec,
1159 p_direction_code,
1160 l_created_party,
1161 x_relationship_id,
1162 x_party_id,
1163 x_party_number,
1164 x_return_status);
1165
1166 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1167 -- Invoke business event system.
1168 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN --10168091
1169 HZ_BUSINESS_EVENT_V2PVT.create_relationship_event (
1170 l_rel_rec,
1171 l_created_party );
1172 END IF;
1173 END IF;
1174
1175 -- Standard call to get message count and if count is 1, get message info.
1176 FND_MSG_PUB.Count_And_Get(
1177 p_encoded => FND_API.G_FALSE,
1178 p_count => x_msg_count,
1179 p_data => x_msg_data);
1180
1181 EXCEPTION
1182 WHEN FND_API.G_EXC_ERROR THEN
1183 ROLLBACK TO create_relationship;
1184 x_return_status := FND_API.G_RET_STS_ERROR;
1185 FND_MSG_PUB.Count_And_Get(
1186 p_encoded => FND_API.G_FALSE,
1187 p_count => x_msg_count,
1188 p_data => x_msg_data);
1189
1190
1191 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1192 ROLLBACK TO create_relationship;
1193 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1194 FND_MSG_PUB.Count_And_Get(
1195 p_encoded => FND_API.G_FALSE,
1196 p_count => x_msg_count,
1197 p_data => x_msg_data);
1198
1199 WHEN OTHERS THEN
1200 ROLLBACK TO create_relationship;
1201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1202 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1203 fnd_message.set_token('ERROR' ,SQLERRM);
1204 fnd_msg_pub.add;
1205 FND_MSG_PUB.Count_And_Get(
1206 p_encoded => FND_API.G_FALSE,
1207 p_count => x_msg_count,
1208 p_data => x_msg_data);
1209
1210 END create_relationship;
1211
1212 PROCEDURE get_relationship_rec (
1213 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1214 p_relationship_id IN NUMBER,
1215 p_directional_flag IN VARCHAR2 := 'F',
1216 x_rel_rec OUT NOCOPY HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE,
1217 x_direction_code OUT NOCOPY VARCHAR2,
1218 x_return_status OUT NOCOPY VARCHAR2,
1219 x_msg_count OUT NOCOPY NUMBER,
1220 x_msg_data OUT NOCOPY VARCHAR2
1221 ) IS
1222
1223 l_party_id NUMBER;
1224 l_directional_flag VARCHAR2(1);
1225 l_direction_code VARCHAR2(255);
1226
1227 BEGIN
1228
1229 --Initialize message list if p_init_msg_list is set to TRUE.
1230 IF FND_API.to_Boolean(p_init_msg_list) THEN
1231 FND_MSG_PUB.initialize;
1232 END IF;
1233
1234 --Initialize API return status to success.
1235 x_return_status := FND_API.G_RET_STS_SUCCESS;
1236
1237 --Check whether primary key has been passed in.
1238 IF p_relationship_id IS NULL OR
1239 p_relationship_id = FND_API.G_MISS_NUM THEN
1240 fnd_message.set_name( 'AR', 'HZ_API_MISSING_COLUMN' );
1241 fnd_message.set_token( 'COLUMN', 'relationship_id' );
1242 fnd_msg_pub.add;
1243 RAISE FND_API.G_EXC_ERROR;
1244 END IF;
1245
1246 x_rel_rec.relationship_id := p_relationship_id;
1247 IF p_directional_flag <> 'F'
1248 AND
1249 p_directional_flag <> 'B'
1250 THEN
1251 l_directional_flag := 'F';
1252 ELSE
1253 l_directional_flag := NVL(p_directional_flag, 'F');
1254 END IF;
1255
1256 HZ_RELATIONSHIPS_PKG.Select_Row (
1257 X_RELATIONSHIP_ID => x_rel_rec.relationship_id,
1258 X_DIRECTIONAL_FLAG => l_directional_flag,
1259 X_SUBJECT_ID => x_rel_rec.subject_id,
1260 X_SUBJECT_TYPE => x_rel_rec.subject_type,
1261 X_SUBJECT_TABLE_NAME => x_rel_rec.subject_table_name,
1262 X_OBJECT_ID => x_rel_rec.object_id,
1263 X_OBJECT_TYPE => x_rel_rec.object_type,
1264 X_OBJECT_TABLE_NAME => x_rel_rec.object_table_name,
1265 X_PARTY_ID => l_party_id,
1266 X_RELATIONSHIP_CODE => x_rel_rec.relationship_code,
1267 X_COMMENTS => x_rel_rec.comments,
1268 X_START_DATE => x_rel_rec.start_date,
1269 X_END_DATE => x_rel_rec.end_date,
1270 X_STATUS => x_rel_rec.status,
1271 X_ATTRIBUTE_CATEGORY => x_rel_rec.attribute_category,
1272 X_ATTRIBUTE1 => x_rel_rec.attribute1,
1273 X_ATTRIBUTE2 => x_rel_rec.attribute2,
1274 X_ATTRIBUTE3 => x_rel_rec.attribute3,
1275 X_ATTRIBUTE4 => x_rel_rec.attribute4,
1276 X_ATTRIBUTE5 => x_rel_rec.attribute5,
1277 X_ATTRIBUTE6 => x_rel_rec.attribute6,
1278 X_ATTRIBUTE7 => x_rel_rec.attribute7,
1279 X_ATTRIBUTE8 => x_rel_rec.attribute8,
1280 X_ATTRIBUTE9 => x_rel_rec.attribute9,
1281 X_ATTRIBUTE10 => x_rel_rec.attribute10,
1282 X_ATTRIBUTE11 => x_rel_rec.attribute11,
1283 X_ATTRIBUTE12 => x_rel_rec.attribute12,
1284 X_ATTRIBUTE13 => x_rel_rec.attribute13,
1285 X_ATTRIBUTE14 => x_rel_rec.attribute14,
1286 X_ATTRIBUTE15 => x_rel_rec.attribute15,
1287 X_ATTRIBUTE16 => x_rel_rec.attribute16,
1288 X_ATTRIBUTE17 => x_rel_rec.attribute17,
1289 X_ATTRIBUTE18 => x_rel_rec.attribute18,
1290 X_ATTRIBUTE19 => x_rel_rec.attribute19,
1291 X_ATTRIBUTE20 => x_rel_rec.attribute20,
1292 X_CONTENT_SOURCE_TYPE => x_rel_rec.content_source_type,
1293 X_RELATIONSHIP_TYPE => x_rel_rec.relationship_type,
1294 X_CREATED_BY_MODULE => x_rel_rec.created_by_module,
1295 X_APPLICATION_ID => x_rel_rec.application_id,
1296 X_ADDITIONAL_INFORMATION1 => x_rel_rec.additional_information1,
1297 X_ADDITIONAL_INFORMATION2 => x_rel_rec.additional_information2,
1298 X_ADDITIONAL_INFORMATION3 => x_rel_rec.additional_information3,
1299 X_ADDITIONAL_INFORMATION4 => x_rel_rec.additional_information4,
1300 X_ADDITIONAL_INFORMATION5 => x_rel_rec.additional_information5,
1301 X_ADDITIONAL_INFORMATION6 => x_rel_rec.additional_information6,
1302 X_ADDITIONAL_INFORMATION7 => x_rel_rec.additional_information7,
1303 X_ADDITIONAL_INFORMATION8 => x_rel_rec.additional_information8,
1304 X_ADDITIONAL_INFORMATION9 => x_rel_rec.additional_information9,
1305 X_ADDITIONAL_INFORMATION10 => x_rel_rec.additional_information10,
1306 X_ADDITIONAL_INFORMATION11 => x_rel_rec.additional_information11,
1307 X_ADDITIONAL_INFORMATION12 => x_rel_rec.additional_information12,
1308 X_ADDITIONAL_INFORMATION13 => x_rel_rec.additional_information13,
1309 X_ADDITIONAL_INFORMATION14 => x_rel_rec.additional_information14,
1310 X_ADDITIONAL_INFORMATION15 => x_rel_rec.additional_information15,
1311 X_ADDITIONAL_INFORMATION16 => x_rel_rec.additional_information16,
1312 X_ADDITIONAL_INFORMATION17 => x_rel_rec.additional_information17,
1313 X_ADDITIONAL_INFORMATION18 => x_rel_rec.additional_information18,
1314 X_ADDITIONAL_INFORMATION19 => x_rel_rec.additional_information19,
1315 X_ADDITIONAL_INFORMATION20 => x_rel_rec.additional_information20,
1316 X_ADDITIONAL_INFORMATION21 => x_rel_rec.additional_information21,
1317 X_ADDITIONAL_INFORMATION22 => x_rel_rec.additional_information22,
1318 X_ADDITIONAL_INFORMATION23 => x_rel_rec.additional_information23,
1319 x_ADDITIONAL_INFORMATION24 => x_rel_rec.additional_information24,
1320 X_ADDITIONAL_INFORMATION25 => x_rel_rec.additional_information25,
1321 X_ADDITIONAL_INFORMATION26 => x_rel_rec.additional_information26,
1322 X_ADDITIONAL_INFORMATION27 => x_rel_rec.additional_information27,
1323 X_ADDITIONAL_INFORMATION28 => x_rel_rec.additional_information28,
1324 X_ADDITIONAL_INFORMATION29 => x_rel_rec.additional_information29,
1325 X_ADDITIONAL_INFORMATION30 => x_rel_rec.additional_information30,
1326 X_DIRECTION_CODE => x_direction_code,
1327 X_PERCENTAGE_OWNERSHIP => x_rel_rec.percentage_ownership,
1328 X_ACTUAL_CONTENT_SOURCE => x_rel_rec.ACTUAL_CONTENT_SOURCE
1329
1330 );
1331
1332 IF l_party_id IS NOT NULL
1333 AND
1334 l_party_id <> FND_API.G_MISS_NUM
1335 THEN
1336 get_party_rec (
1337 p_party_id => l_party_id,
1338 x_party_rec => x_rel_rec.party_rec,
1339 x_return_status => x_return_status,
1340 x_msg_count => x_msg_count,
1341 x_msg_data => x_msg_data
1342 );
1343
1344 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1345 RAISE FND_API.G_EXC_ERROR;
1346 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1347 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1348 END IF;
1349 END IF;
1350
1351 --Standard call to get message count and if count is 1, get message info.
1352 FND_MSG_PUB.Count_And_Get(
1353 p_encoded => FND_API.G_FALSE,
1354 p_count => x_msg_count,
1355 p_data => x_msg_data );
1356
1357 EXCEPTION
1358 WHEN FND_API.G_EXC_ERROR THEN
1359 x_return_status := FND_API.G_RET_STS_ERROR;
1360
1361 FND_MSG_PUB.Count_And_Get(
1362 p_encoded => FND_API.G_FALSE,
1363 p_count => x_msg_count,
1364 p_data => x_msg_data );
1365
1366 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1367 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1368
1369 FND_MSG_PUB.Count_And_Get(
1370 p_encoded => FND_API.G_FALSE,
1371 p_count => x_msg_count,
1372 p_data => x_msg_data );
1373
1374 WHEN OTHERS THEN
1375 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1376
1377 fnd_message.set_name( 'AR', 'HZ_API_OTHERS_EXCEP' );
1378 fnd_message.set_token( 'ERROR' ,SQLERRM );
1379 fnd_msg_pub.add;
1380
1381 FND_MSG_PUB.Count_And_Get(
1382 p_encoded => FND_API.G_FALSE,
1383 p_count => x_msg_count,
1384 p_data => x_msg_data );
1385
1386
1387 END get_relationship_rec;
1388
1389 --------------------PARTY_CONTACT--------------------------------------------
1390
1391 ------------------------------------
1392 -- declaration of private procedures
1393 ------------------------------------
1394
1395
1396 PROCEDURE do_create_org_contact(
1397 p_org_contact_rec IN OUT NOCOPY HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE,
1398 p_direction_code IN VARCHAR2,
1399 x_return_status IN OUT NOCOPY VARCHAR2,
1400 x_org_contact_id OUT NOCOPY NUMBER,
1401 x_party_rel_id OUT NOCOPY NUMBER,
1402 x_party_id OUT NOCOPY NUMBER,
1403 x_party_number OUT NOCOPY VARCHAR2
1404 ) IS
1405
1406 l_org_contact_id NUMBER := p_org_contact_rec.org_contact_id;
1407 l_rowid ROWID := NULL;
1408 l_count NUMBER;
1409 l_gen_contact_number VARCHAR2(1);
1410 l_contact_number VARCHAR2(30) := p_org_contact_rec.contact_number;
1411 l_msg_count NUMBER;
1412 l_msg_data VARCHAR2(2000);
1413 l_dummy VARCHAR2(1);
1414 l_debug_prefix VARCHAR2(30);
1415 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
1416 BEGIN
1417
1418 --Initialize the created by module
1419 p_org_contact_rec.created_by_module := 'HZ_TCA_CUSTOMER_MERGE';
1420 p_org_contact_rec.orig_system_reference := null;
1421
1422 ---Set the contact_number to null so that it is generated
1423 p_org_contact_rec.contact_number := null;
1424 p_org_contact_rec.org_contact_id := null;
1425
1426 --- Retain the application ID of the org contact rec in party reln rec and party rec
1427 p_org_contact_rec.party_rel_rec.application_id := p_org_contact_rec.application_id;
1428
1429 p_org_contact_rec.party_rel_rec.party_rec.party_number := null;
1430
1431 --
1432 -- create party relationship.
1433 --
1434 create_relationship (
1435 p_relationship_rec => p_org_contact_rec.party_rel_rec,
1436 p_direction_code => p_direction_code,
1437 x_relationship_id => x_party_rel_id,
1438 x_party_id => x_party_id,
1439 x_party_number => x_party_number,
1440 x_return_status => x_return_status,
1441 x_msg_count => l_msg_count,
1442 x_msg_data => l_msg_data
1443 );
1444
1445
1446 p_org_contact_rec.party_rel_rec.party_rec.party_id := x_party_id;
1447 p_org_contact_rec.party_rel_rec.party_rec.party_number := x_party_number;
1448 p_org_contact_rec.party_rel_rec.relationship_id := x_party_rel_id;
1449
1450 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1451 RAISE FND_API.G_EXC_ERROR;
1452 END IF;
1453
1454 -- call table-handler.
1455 HZ_ORG_CONTACTS_PKG.Insert_Row (
1456 X_ORG_CONTACT_ID => p_org_contact_rec.org_contact_id,
1457 X_PARTY_RELATIONSHIP_ID => x_party_rel_id,
1458 X_COMMENTS => p_org_contact_rec.comments,
1459 X_CONTACT_NUMBER => l_contact_number,
1460 X_DEPARTMENT_CODE => p_org_contact_rec.department_code,
1461 X_DEPARTMENT => p_org_contact_rec.department,
1462 X_TITLE => p_org_contact_rec.title,
1463 X_JOB_TITLE => p_org_contact_rec.job_title,
1464 X_DECISION_MAKER_FLAG => p_org_contact_rec.decision_maker_flag,
1465 X_JOB_TITLE_CODE => p_org_contact_rec.job_title_code,
1466 X_REFERENCE_USE_FLAG => p_org_contact_rec.reference_use_flag,
1467 X_RANK => p_org_contact_rec.rank,
1468 X_ORIG_SYSTEM_REFERENCE => p_org_contact_rec.orig_system_reference,
1469 X_ATTRIBUTE_CATEGORY => p_org_contact_rec.attribute_category,
1470 X_ATTRIBUTE1 => p_org_contact_rec.attribute1,
1471 X_ATTRIBUTE2 => p_org_contact_rec.attribute2,
1472 X_ATTRIBUTE3 => p_org_contact_rec.attribute3,
1473 X_ATTRIBUTE4 => p_org_contact_rec.attribute4,
1474 X_ATTRIBUTE5 => p_org_contact_rec.attribute5,
1475 X_ATTRIBUTE6 => p_org_contact_rec.attribute6,
1476 X_ATTRIBUTE7 => p_org_contact_rec.attribute7,
1477 X_ATTRIBUTE8 => p_org_contact_rec.attribute8,
1478 X_ATTRIBUTE9 => p_org_contact_rec.attribute9,
1479 X_ATTRIBUTE10 => p_org_contact_rec.attribute10,
1480 X_ATTRIBUTE11 => p_org_contact_rec.attribute11,
1481 X_ATTRIBUTE12 => p_org_contact_rec.attribute12,
1482 X_ATTRIBUTE13 => p_org_contact_rec.attribute13,
1483 X_ATTRIBUTE14 => p_org_contact_rec.attribute14,
1484 X_ATTRIBUTE15 => p_org_contact_rec.attribute15,
1485 X_ATTRIBUTE16 => p_org_contact_rec.attribute16,
1486 X_ATTRIBUTE17 => p_org_contact_rec.attribute17,
1487 X_ATTRIBUTE18 => p_org_contact_rec.attribute18,
1488 X_ATTRIBUTE19 => p_org_contact_rec.attribute19,
1489 X_ATTRIBUTE20 => p_org_contact_rec.attribute20,
1490 X_ATTRIBUTE21 => p_org_contact_rec.attribute21,
1491 X_ATTRIBUTE22 => p_org_contact_rec.attribute22,
1492 X_ATTRIBUTE23 => p_org_contact_rec.attribute23,
1493 X_ATTRIBUTE24 => p_org_contact_rec.attribute24,
1494 X_PARTY_SITE_ID => p_org_contact_rec.party_site_id,
1495 X_OBJECT_VERSION_NUMBER => 1,
1496 X_CREATED_BY_MODULE => p_org_contact_rec.created_by_module,
1497 X_APPLICATION_ID => p_org_contact_rec.application_id,
1498 X_STATUS => p_org_contact_rec.party_rel_rec.status
1499 );
1500 /*
1501 per HLD,mosr record should not be created for copy case, since old osr is still active
1502 hz_orig_system_ref_pvt.create_mosr_for_merge(
1503 FND_API.G_FALSE,
1504 'HZ_ORG_CONTACTS',
1505 p_org_contact_rec.org_contact_id,
1506 x_return_status,
1507 l_msg_count,
1508 l_msg_data);
1509
1510 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1511 RAISE FND_API.G_EXC_ERROR;
1512 END IF;
1513 */
1514 x_org_contact_id := p_org_contact_rec.org_contact_id;
1515 END do_create_org_contact;
1516
1517 PROCEDURE create_org_contact (
1518 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
1519 p_org_contact_rec IN HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE,
1520 p_direction_code IN VARCHAR2,
1521 x_org_contact_id OUT NOCOPY NUMBER,
1522 x_party_rel_id OUT NOCOPY NUMBER,
1523 x_party_id OUT NOCOPY NUMBER,
1524 x_party_number OUT NOCOPY VARCHAR2,
1525 x_return_status OUT NOCOPY VARCHAR2,
1526 x_msg_count OUT NOCOPY NUMBER,
1527 x_msg_data OUT NOCOPY VARCHAR2
1528 ) IS
1529
1530 l_api_name CONSTANT VARCHAR2(30) := 'create_org_contact';
1531 l_api_version CONSTANT NUMBER := 1.0;
1532 l_org_contact_rec HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE := p_org_contact_rec;
1533
1534 BEGIN
1535
1536 -- standard start of API savepoint
1537 SAVEPOINT create_org_contact;
1538
1539 -- initialize API return status to success.
1540 x_return_status := FND_API.G_RET_STS_SUCCESS;
1541
1542 -- call to business logic.
1543 do_create_org_contact(
1544 l_org_contact_rec,
1545 p_direction_code,
1546 x_return_status,
1547 x_org_contact_id,
1548 x_party_rel_id,
1549 x_party_id,
1550 x_party_number
1551 );
1552
1553 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1554 -- Invoke business event system.
1555 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN --10168091
1556 HZ_BUSINESS_EVENT_V2PVT.create_org_contact_event (
1557 l_org_contact_rec );
1558 END IF;
1559 END IF;
1560
1561 -- Call to indicate Org Contact creation to DQM
1562 HZ_DQM_SYNC.sync_contact(l_org_contact_rec.org_contact_id, 'C');
1563
1564 -- standard call to get message count and if count is 1, get message info.
1565 FND_MSG_PUB.Count_And_Get(
1566 p_encoded => FND_API.G_FALSE,
1567 p_count => x_msg_count,
1568 p_data => x_msg_data);
1569
1570
1571 EXCEPTION
1572 WHEN FND_API.G_EXC_ERROR THEN
1573 ROLLBACK TO create_org_contact;
1574 x_return_status := FND_API.G_RET_STS_ERROR;
1575 FND_MSG_PUB.Count_And_Get(
1576 p_encoded => FND_API.G_FALSE,
1577 p_count => x_msg_count,
1578 p_data => x_msg_data);
1579
1580
1581 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1582 ROLLBACK TO create_org_contact;
1583 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1584 FND_MSG_PUB.Count_And_Get(
1585 p_encoded => FND_API.G_FALSE,
1586 p_count => x_msg_count,
1587 p_data => x_msg_data);
1588
1589
1590 WHEN OTHERS THEN
1591 ROLLBACK TO create_org_contact;
1592 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1593 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1594 fnd_message.set_token('ERROR' ,SQLERRM);
1595 fnd_msg_pub.add;
1596 FND_MSG_PUB.Count_And_Get(
1597 p_encoded => FND_API.G_FALSE,
1598 p_count => x_msg_count,
1599 p_data => x_msg_data);
1600
1601 END create_org_contact;
1602
1603
1604 PROCEDURE get_org_contact_rec (
1605 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1606 p_org_contact_id IN NUMBER,
1607 x_org_contact_rec OUT NOCOPY HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE,
1608 x_direction_code OUT NOCOPY VARCHAR2,
1609 x_return_status OUT NOCOPY VARCHAR2,
1610 x_msg_count OUT NOCOPY NUMBER,
1611 x_msg_data OUT NOCOPY VARCHAR2
1612 ) IS
1613
1614 l_api_name CONSTANT VARCHAR2(30) := 'get_org_contact_rec';
1615 l_api_version CONSTANT NUMBER := 1.0;
1616 l_party_relationship_id NUMBER;
1617
1618 BEGIN
1619
1620 --Initialize message list if p_init_msg_list is set to TRUE.
1621 IF FND_API.to_Boolean(p_init_msg_list) THEN
1622 FND_MSG_PUB.initialize;
1623 END IF;
1624
1625 --Initialize API return status to success.
1626 x_return_status := FND_API.G_RET_STS_SUCCESS;
1627 --Check whether primary key has been passed in.
1628 IF p_org_contact_id IS NULL OR
1629 p_org_contact_id = FND_API.G_MISS_NUM THEN
1630 fnd_message.set_name( 'AR', 'HZ_API_MISSING_COLUMN' );
1631 fnd_message.set_token( 'COLUMN', 'org_contact_id' );
1632 fnd_msg_pub.add;
1633 RAISE FND_API.G_EXC_ERROR;
1634 END IF;
1635
1636 x_org_contact_rec.org_contact_id := p_org_contact_id;
1637
1638 HZ_ORG_CONTACTS_PKG.Select_Row (
1639 X_ORG_CONTACT_ID => x_org_contact_rec.org_contact_id,
1640 X_PARTY_RELATIONSHIP_ID => l_party_relationship_id,
1641 X_COMMENTS => x_org_contact_rec.comments,
1642 X_CONTACT_NUMBER => x_org_contact_rec.contact_number,
1643 X_DEPARTMENT_CODE => x_org_contact_rec.department_code,
1644 X_DEPARTMENT => x_org_contact_rec.department,
1645 X_TITLE => x_org_contact_rec.title,
1646 X_JOB_TITLE => x_org_contact_rec.job_title,
1647 X_DECISION_MAKER_FLAG => x_org_contact_rec.decision_maker_flag,
1648 X_JOB_TITLE_CODE => x_org_contact_rec.job_title_code,
1649 X_REFERENCE_USE_FLAG => x_org_contact_rec.reference_use_flag,
1650 X_RANK => x_org_contact_rec.rank,
1651 X_ORIG_SYSTEM_REFERENCE => x_org_contact_rec.orig_system_reference,
1652 X_ATTRIBUTE_CATEGORY => x_org_contact_rec.attribute_category,
1653 X_ATTRIBUTE1 => x_org_contact_rec.attribute1,
1654 X_ATTRIBUTE2 => x_org_contact_rec.attribute2,
1655 X_ATTRIBUTE3 => x_org_contact_rec.attribute3,
1656 X_ATTRIBUTE4 => x_org_contact_rec.attribute4,
1657 X_ATTRIBUTE5 => x_org_contact_rec.attribute5,
1658 X_ATTRIBUTE6 => x_org_contact_rec.attribute6,
1659 X_ATTRIBUTE7 => x_org_contact_rec.attribute7,
1660 X_ATTRIBUTE8 => x_org_contact_rec.attribute8,
1661 X_ATTRIBUTE9 => x_org_contact_rec.attribute9,
1662 X_ATTRIBUTE10 => x_org_contact_rec.attribute10,
1663 X_ATTRIBUTE11 => x_org_contact_rec.attribute11,
1664 X_ATTRIBUTE12 => x_org_contact_rec.attribute12,
1665 X_ATTRIBUTE13 => x_org_contact_rec.attribute13,
1666 X_ATTRIBUTE14 => x_org_contact_rec.attribute14,
1667 X_ATTRIBUTE15 => x_org_contact_rec.attribute15,
1668 X_ATTRIBUTE16 => x_org_contact_rec.attribute16,
1669 X_ATTRIBUTE17 => x_org_contact_rec.attribute17,
1670 X_ATTRIBUTE18 => x_org_contact_rec.attribute18,
1671 X_ATTRIBUTE19 => x_org_contact_rec.attribute19,
1672 X_ATTRIBUTE20 => x_org_contact_rec.attribute20,
1673 X_ATTRIBUTE21 => x_org_contact_rec.attribute21,
1674 X_ATTRIBUTE22 => x_org_contact_rec.attribute22,
1675 X_ATTRIBUTE23 => x_org_contact_rec.attribute23,
1676 X_ATTRIBUTE24 => x_org_contact_rec.attribute24,
1677 X_PARTY_SITE_ID => x_org_contact_rec.party_site_id,
1678 X_CREATED_BY_MODULE => x_org_contact_rec.created_by_module,
1679 X_APPLICATION_ID => x_org_contact_rec.application_id
1680 );
1681
1682
1683 IF l_party_relationship_id IS NOT NULL
1684 AND
1685 l_party_relationship_id <> FND_API.G_MISS_NUM
1686 THEN
1687 get_relationship_rec (
1688 p_relationship_id => l_party_relationship_id,
1689 p_directional_flag => 'F',
1690 x_rel_rec => x_org_contact_rec.party_rel_rec,
1691 x_direction_code => x_direction_code,
1692 x_return_status => x_return_status,
1693 x_msg_count => x_msg_count,
1694 x_msg_data => x_msg_data
1695 );
1696
1697 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1698 RAISE FND_API.G_EXC_ERROR;
1699 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1700 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1701 END IF;
1702 END IF;
1703
1704 --Standard call to get message count and if count is 1, get message info.
1705 FND_MSG_PUB.Count_And_Get(
1706 p_encoded => FND_API.G_FALSE,
1707 p_count => x_msg_count,
1708 p_data => x_msg_data );
1709
1710 EXCEPTION
1711 WHEN FND_API.G_EXC_ERROR THEN
1712 x_return_status := FND_API.G_RET_STS_ERROR;
1713
1714 FND_MSG_PUB.Count_And_Get(
1715 p_encoded => FND_API.G_FALSE,
1716 p_count => x_msg_count,
1717 p_data => x_msg_data );
1718
1719 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1720 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1721
1722 FND_MSG_PUB.Count_And_Get(
1723 p_encoded => FND_API.G_FALSE,
1724 p_count => x_msg_count,
1725 p_data => x_msg_data );
1726
1727 WHEN OTHERS THEN
1728 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1729 fnd_message.set_name( 'AR', 'HZ_API_OTHERS_EXCEP' );
1730 fnd_message.set_token( 'ERROR' ,SQLERRM );
1731 fnd_msg_pub.add;
1732 FND_MSG_PUB.Count_And_Get(
1733 p_encoded => FND_API.G_FALSE,
1734 p_count => x_msg_count,
1735 p_data => x_msg_data );
1736
1737 END get_org_contact_rec;
1738
1739 --------------------PARTY_SITE--------------------------------------------------
1740
1741 --------------------------------------------------
1742 -- declaration of private procedures and functions
1743 --------------------------------------------------
1744
1745
1746 PROCEDURE do_create_party_site (
1747 p_party_site_rec IN OUT NOCOPY HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE,
1748 p_actual_cont_source IN VARCHAR2,
1749 x_party_site_id OUT NOCOPY NUMBER,
1750 x_party_site_number OUT NOCOPY VARCHAR2,
1751 x_return_status IN OUT NOCOPY VARCHAR2
1752 );
1753
1754 PROCEDURE do_update_address(
1755 p_party_id IN NUMBER,
1756 p_location_id IN NUMBER
1757 );
1758
1759 PROCEDURE do_unmark_address_flag(
1760 p_party_id IN NUMBER,
1761 p_party_site_id IN NUMBER := NULL
1762 );
1763
1764
1765 -----------------------------
1766 -- body of private procedures
1767 -----------------------------
1768
1769
1770 PROCEDURE do_create_party_site(
1771 p_party_site_rec IN OUT NOCOPY HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE,
1772 p_actual_cont_source IN VARCHAR2,
1773 x_party_site_id OUT NOCOPY NUMBER,
1774 x_party_site_number OUT NOCOPY VARCHAR2,
1775 x_return_status IN OUT NOCOPY VARCHAR2
1776 ) IS
1777
1778 l_party_site_id NUMBER := p_party_site_rec.party_site_id;
1779 l_party_site_number VARCHAR2(30) := p_party_site_rec.party_site_number;
1780 l_gen_party_site_number VARCHAR2(1);
1781 l_rowid ROWID := NULL;
1782 l_count NUMBER;
1783 l_exist VARCHAR2(1) := 'N';
1784 l_msg_count NUMBER;
1785 l_msg_data VARCHAR2(2000);
1786 l_dummy VARCHAR2(1);
1787 l_debug_prefix VARCHAR2(30) := '';
1788
1789 -- Bug 2197181
1790 l_loc_actual_content_source hz_locations.actual_content_source%TYPE;
1791 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
1792 BEGIN
1793
1794 --Initialize the created by module
1795 p_party_site_rec.created_by_module := 'HZ_TCA_CUSTOMER_MERGE';
1796
1797 p_party_site_rec.orig_system_reference := null;
1798 p_party_site_rec.party_site_number := null;
1799 p_party_site_rec.party_site_id := null;
1800
1801 -- Bug 2197181
1802 select actual_content_source
1803 into l_loc_actual_content_source
1804 from hz_locations
1805 where location_id = p_party_site_rec.location_id;
1806
1807 /* SSM SST Integration and Extension
1808 * For non-profile entities, the concept of select/de-select data-sources is obsoleted.
1809 * There is no need to check if the data-source is selected.
1810
1811 g_pst_is_datasource_selected :=
1812 HZ_MIXNM_UTILITY.isDataSourceSelected (
1813 p_selected_datasources => g_pst_selected_datasources,
1814 p_actual_content_source => l_loc_actual_content_source );
1815 */
1816 -- if this is the first active, visible party site,
1817 -- we need to mark it with identifying flag = 'Y'.
1818
1819 BEGIN
1820 -- Bug 2197181: Added the checking if the party site is visible
1821 -- or not. The identifying address should be visible.
1822
1823 -- SSM SST Integration and Extension
1824 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
1825 -- There is no need to check if the data-source is selected.
1826
1827 SELECT 'Y' INTO l_dummy
1828 FROM HZ_PARTY_SITES
1829 WHERE PARTY_ID = p_party_site_rec.party_id
1830 AND STATUS = 'A'
1831 /* AND HZ_MIXNM_UTILITY.isDataSourceSelected (
1832 g_pst_selected_datasources, actual_content_source ) = 'Y'*/
1833 AND ROWNUM = 1;
1834
1835 -- no exception raise, means 'a primary party site exist'
1836 -- if the current party site is to be identifying, then unmark
1837 -- the previous party sites with identifying flag = 'Y'.
1838
1839 -- Bug 2197181: added for mix-n-match project: the identifying_flag
1840 -- can be set to 'Y' only if the party site will be visible. If it
1841 -- is not visible, the flag must be reset to 'N'.
1842
1843 -- SSM SST Integration and Extension
1844 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
1845 -- There is no need to check if the data-source is selected.
1846
1847 IF p_party_site_rec.identifying_address_flag = 'Y' /*AND
1848 g_pst_is_datasource_selected = 'Y'*/
1849 THEN
1850 do_unmark_address_flag(p_party_site_rec.party_id);
1851 ELSE
1852 p_party_site_rec.identifying_address_flag := 'N';
1853 END IF;
1854
1855 EXCEPTION
1856 WHEN NO_DATA_FOUND THEN
1857 -- this is the first visible, active address, so this will be
1858 -- set as identifying address.
1859
1860 -- Bug 2197181: added for mix-n-match project: the identifying_flag
1861 -- can be set to 'Y' only if the party site will be visible. If it i
1862 -- not visible, the flag must be reset to 'N'.
1863
1864 -- SSM SST Integration and Extension
1865 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
1866 -- There is no need to check if the data-source is selected.
1867
1868 IF (NVL(p_party_site_rec.status, 'A') = 'A' OR
1869 p_party_site_rec.status = FND_API.G_MISS_CHAR)/* AND
1870 g_pst_is_datasource_selected = 'Y'*/
1871 THEN
1872 p_party_site_rec.identifying_address_flag := 'Y';
1873 ELSE
1874 p_party_site_rec.identifying_address_flag := 'N';
1875 END IF;
1876 END;
1877
1878 --denormalize primary address
1879 IF p_party_site_rec.identifying_address_flag = 'Y' THEN
1880 IF p_party_site_rec.party_id <> -1 THEN
1881 do_update_address(
1882 p_party_site_rec.party_id,
1883 p_party_site_rec.location_id);
1884 END IF;
1885
1886 END IF;
1887
1888
1889 p_party_site_rec.party_site_id := l_party_site_id;
1890 p_party_site_rec.party_site_number := l_party_site_number;
1891
1892 -- this is for orig_system_defaulting
1893 IF p_party_site_rec.party_site_id = FND_API.G_MISS_NUM THEN
1894 p_party_site_rec.party_site_id := NULL;
1895 END IF;
1896
1897
1898 -- call table-handler.
1899 HZ_PARTY_SITES_PKG.Insert_Row (
1900 X_PARTY_SITE_ID => p_party_site_rec.party_site_id,
1901 X_PARTY_ID => p_party_site_rec.party_id,
1902 X_LOCATION_ID => p_party_site_rec.location_id,
1903 X_PARTY_SITE_NUMBER => p_party_site_rec.party_site_number,
1904 X_ATTRIBUTE_CATEGORY => p_party_site_rec.attribute_category,
1905 X_ATTRIBUTE1 => p_party_site_rec.attribute1,
1906 X_ATTRIBUTE2 => p_party_site_rec.attribute2,
1907 X_ATTRIBUTE3 => p_party_site_rec.attribute3,
1908 X_ATTRIBUTE4 => p_party_site_rec.attribute4,
1909 X_ATTRIBUTE5 => p_party_site_rec.attribute5,
1910 X_ATTRIBUTE6 => p_party_site_rec.attribute6,
1911 X_ATTRIBUTE7 => p_party_site_rec.attribute7,
1912 X_ATTRIBUTE8 => p_party_site_rec.attribute8,
1913 X_ATTRIBUTE9 => p_party_site_rec.attribute9,
1914 X_ATTRIBUTE10 => p_party_site_rec.attribute10,
1915 X_ATTRIBUTE11 => p_party_site_rec.attribute11,
1916 X_ATTRIBUTE12 => p_party_site_rec.attribute12,
1917 X_ATTRIBUTE13 => p_party_site_rec.attribute13,
1918 X_ATTRIBUTE14 => p_party_site_rec.attribute14,
1919 X_ATTRIBUTE15 => p_party_site_rec.attribute15,
1920 X_ATTRIBUTE16 => p_party_site_rec.attribute16,
1921 X_ATTRIBUTE17 => p_party_site_rec.attribute17,
1922 X_ATTRIBUTE18 => p_party_site_rec.attribute18,
1923 X_ATTRIBUTE19 => p_party_site_rec.attribute19,
1924 X_ATTRIBUTE20 => p_party_site_rec.attribute20,
1925 X_ORIG_SYSTEM_REFERENCE => p_party_site_rec.orig_system_reference,
1926 X_LANGUAGE => p_party_site_rec.language,
1927 X_MAILSTOP => p_party_site_rec.mailstop,
1928 X_IDENTIFYING_ADDRESS_FLAG => p_party_site_rec.identifying_address_flag,
1929 X_STATUS => p_party_site_rec.status,
1930 X_PARTY_SITE_NAME => p_party_site_rec.party_site_name,
1931 X_ADDRESSEE => p_party_site_rec.addressee,
1932 X_OBJECT_VERSION_NUMBER => 1,
1933 X_CREATED_BY_MODULE => p_party_site_rec.created_by_module,
1934 X_APPLICATION_ID => p_party_site_rec.application_id,
1935 X_ACTUAL_CONTENT_SOURCE => p_actual_cont_source,
1936 X_GLOBAL_LOCATION_NUMBER => p_party_site_rec.global_location_number,
1937 X_DUNS_NUMBER_C => p_party_site_rec.duns_number_c
1938 );
1939 /*
1940 per HLD,mosr record should not be created for copy case, since old osr is still active
1941 hz_orig_system_ref_pvt.create_mosr_for_merge(
1942 FND_API.G_FALSE,
1943 'HZ_PARTY_SITES',
1944 p_party_site_rec.party_site_id,
1945 x_return_status,
1946 l_msg_count,
1947 l_msg_data);
1948
1949 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1950 RAISE FND_API.G_EXC_ERROR;
1951 END IF;
1952 */
1953 x_party_site_id := p_party_site_rec.party_site_id;
1954 x_party_site_number := p_party_site_rec.party_site_number;
1955
1956
1957 END do_create_party_site;
1958
1959 procedure do_update_address(
1960 p_party_id IN NUMBER,
1961 p_location_id IN NUMBER
1962 ) IS
1963
1964 CURSOR c_loc IS
1965 SELECT * FROM hz_locations
1966 WHERE location_id = p_location_id;
1967
1968 CURSOR c_party IS
1969 SELECT 'Y'
1970 FROM hz_parties
1971 WHERE party_id = p_party_id
1972 FOR UPDATE NOWAIT;
1973
1974 l_location_rec c_loc%ROWTYPE;
1975 l_exists VARCHAR2(1);
1976 l_do_not_normalize VARCHAR2(1):= 'N';
1977
1978 BEGIN
1979
1980 --check if party record is locked by any one else.
1981 BEGIN
1982 OPEN c_party;
1983 FETCH c_party INTO l_exists;
1984 CLOSE c_party;
1985 EXCEPTION
1986 WHEN OTHERS THEN
1987 l_do_not_normalize := 'Y';
1988 END;
1989
1990
1991 -- if location_id is null, we will null out the location
1992 -- components in hz_parties.
1993
1994 IF p_location_id IS NULL THEN
1995 l_location_rec.country := NULL;
1996 l_location_rec.address1 := NULL;
1997 l_location_rec.address2 := NULL;
1998 l_location_rec.address3 := NULL;
1999 l_location_rec.address4 := NULL;
2000 l_location_rec.city := NULL;
2001 l_location_rec.postal_code := NULL;
2002 l_location_rec.state := NULL;
2003 l_location_rec.province := NULL;
2004 l_location_rec.county := NULL;
2005 ELSE
2006 --Open the cursor and fetch location components and
2007 --content_source_type.
2008
2009 OPEN c_loc;
2010 FETCH c_loc INTO l_location_rec;
2011 CLOSE c_loc;
2012 END IF;
2013
2014 if l_do_not_normalize <> 'Y' then
2015
2016 UPDATE hz_parties
2017 SET country = l_location_rec.country,
2018 address1 = l_location_rec.address1,
2019 address2 = l_location_rec.address2,
2020 address3 = l_location_rec.address3,
2021 address4 = l_location_rec.address4,
2022 city = l_location_rec.city,
2023 postal_code = l_location_rec.postal_code,
2024 state = l_location_rec.state,
2025 province = l_location_rec.province,
2026 county = l_location_rec.county
2027 WHERE party_id = p_party_id;
2028
2029 end if;
2030
2031
2032 END do_update_address;
2033
2034 PROCEDURE do_unmark_address_flag(
2035 p_party_id IN NUMBER,
2036 p_party_site_id IN NUMBER := NULL
2037 ) IS
2038
2039 CURSOR c_party_sites IS
2040 SELECT rowid
2041 FROM hz_party_sites
2042 WHERE party_id = p_party_id
2043 AND party_site_id <> nvl(p_party_site_id,-999)
2044 AND identifying_address_flag = 'Y'
2045 AND rownum = 1
2046 FOR UPDATE NOWAIT;
2047
2048 l_rowid VARCHAR2(100);
2049 l_record_locked VARCHAR2(1) := 'N';
2050
2051 BEGIN
2052
2053 --check if party record is locked by any one else.
2054 BEGIN
2055 OPEN c_party_sites;
2056 FETCH c_party_sites INTO l_rowid;
2057 CLOSE c_party_sites;
2058 EXCEPTION
2059 WHEN OTHERS THEN
2060 l_record_locked := 'Y';
2061 END;
2062
2063 IF l_rowid IS NOT NULL AND l_record_locked <> 'Y' THEN
2064 UPDATE hz_party_sites
2065 SET identifying_address_flag = 'N'
2066 WHERE rowid = l_rowid;
2067 END IF;
2068
2069 END do_unmark_address_flag;
2070
2071
2072
2073 ----------------------------
2074 -- body of public procedures
2075 ----------------------------
2076
2077 PROCEDURE create_party_site (
2078 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2079 p_party_site_rec IN HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE,
2080 p_actual_cont_source IN VARCHAR2,
2081 x_party_site_id OUT NOCOPY NUMBER,
2082 x_party_site_number OUT NOCOPY VARCHAR2,
2083 x_return_status OUT NOCOPY VARCHAR2,
2084 x_msg_count OUT NOCOPY NUMBER,
2085 x_msg_data OUT NOCOPY VARCHAR2
2086 ) IS
2087
2088 l_api_name CONSTANT VARCHAR2(30) := 'create_party_site';
2089 l_api_version CONSTANT NUMBER := 1.0;
2090 l_party_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE := p_party_site_rec;
2091
2092 BEGIN
2093 -- standard start of API savepoint
2094 SAVEPOINT create_party_site;
2095
2096 -- initialize message list if p_init_msg_list is set to TRUE.
2097 IF FND_API.to_Boolean(p_init_msg_list) THEN
2098 FND_MSG_PUB.initialize;
2099 END IF;
2100
2101 -- initialize API return status to success.
2102 x_return_status := FND_API.G_RET_STS_SUCCESS;
2103
2104 -- Bug 2197181: added for mix-n-match project. first load data
2105 -- sources for this entity.
2106 /*
2107 IF g_pst_mixnmatch_enabled IS NULL THEN
2108 HZ_MIXNM_UTILITY.LoadDataSources(
2109 p_entity_name => 'HZ_LOCATIONS',
2110 p_entity_attr_id => g_pst_entity_attr_id,
2111 p_mixnmatch_enabled => g_pst_mixnmatch_enabled,
2112 p_selected_datasources => g_pst_selected_datasources );
2113 END IF;
2114 */
2115 -- call to business logic.
2116 do_create_party_site(
2117 l_party_site_rec,
2118 p_actual_cont_source,
2119 x_party_site_id,
2120 x_party_site_number,
2121 x_return_status
2122 );
2123
2124 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2125 -- Invoke business event system.
2126 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN --10168091
2127 HZ_BUSINESS_EVENT_V2PVT.create_party_site_event (
2128 l_party_site_rec );
2129 END IF;
2130 END IF;
2131
2132 -- Call to indicate Party Site creation to DQM
2133 HZ_DQM_SYNC.sync_party_site(l_party_site_rec.party_site_id,'C');
2134
2135 -- standard call to get message count and if count is 1, get message info.
2136 FND_MSG_PUB.Count_And_Get(
2137 p_encoded => FND_API.G_FALSE,
2138 p_count => x_msg_count,
2139 p_data => x_msg_data);
2140
2141 EXCEPTION
2142 WHEN FND_API.G_EXC_ERROR THEN
2143 ROLLBACK TO create_party_site;
2144 x_return_status := FND_API.G_RET_STS_ERROR;
2145 FND_MSG_PUB.Count_And_Get(
2146 p_encoded => FND_API.G_FALSE,
2147 p_count => x_msg_count,
2148 p_data => x_msg_data);
2149
2150 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2151 ROLLBACK TO create_party_site;
2152 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2153 FND_MSG_PUB.Count_And_Get(
2154 p_encoded => FND_API.G_FALSE,
2155 p_count => x_msg_count,
2156 p_data => x_msg_data);
2157 WHEN OTHERS THEN
2158 ROLLBACK TO create_party_site;
2159 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2160 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
2161 fnd_message.set_token('ERROR' ,SQLERRM);
2162 fnd_msg_pub.add;
2163 FND_MSG_PUB.Count_And_Get(
2164 p_encoded => FND_API.G_FALSE,
2165 p_count => x_msg_count,
2166 p_data => x_msg_data);
2167
2168 END create_party_site;
2169
2170
2171 PROCEDURE get_party_site_rec (
2172 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2173 p_party_site_id IN NUMBER,
2174 x_party_site_rec OUT NOCOPY HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE,
2175 x_actual_cont_source OUT NOCOPY VARCHAR2,
2176 x_return_status OUT NOCOPY VARCHAR2,
2177 x_msg_count OUT NOCOPY NUMBER,
2178 x_msg_data OUT NOCOPY VARCHAR2
2179
2180 ) IS
2181
2182 l_api_name CONSTANT VARCHAR2(30) := 'get_party_site_rec';
2183 l_api_version CONSTANT NUMBER := 1.0;
2184
2185 BEGIN
2186
2187 --Initialize message list if p_init_msg_list is set to TRUE.
2188 IF FND_API.to_Boolean(p_init_msg_list) THEN
2189 FND_MSG_PUB.initialize;
2190 END IF;
2191
2192 --Initialize API return status to success.
2193 x_return_status := FND_API.G_RET_STS_SUCCESS;
2194
2195 --Check whether primary key has been passed in.
2196 IF p_party_site_id IS NULL OR
2197 p_party_site_id = FND_API.G_MISS_NUM THEN
2198 fnd_message.set_name( 'AR', 'HZ_API_MISSING_COLUMN' );
2199 fnd_message.set_token( 'COLUMN', 'party_site_id' );
2200 fnd_msg_pub.add;
2201 RAISE FND_API.G_EXC_ERROR;
2202 END IF;
2203
2204 x_party_site_rec.party_site_id := p_party_site_id;
2205
2206 HZ_PARTY_SITES_PKG.Select_Row (
2207 X_PARTY_SITE_ID => x_party_site_rec.party_site_id,
2208 X_PARTY_ID => x_party_site_rec.party_id,
2209 X_LOCATION_ID => x_party_site_rec.location_id,
2210 X_PARTY_SITE_NUMBER => x_party_site_rec.party_site_number,
2211 X_ATTRIBUTE_CATEGORY => x_party_site_rec.attribute_category,
2212 X_ATTRIBUTE1 => x_party_site_rec.attribute1,
2213 X_ATTRIBUTE2 => x_party_site_rec.attribute2,
2214 X_ATTRIBUTE3 => x_party_site_rec.attribute3,
2215 X_ATTRIBUTE4 => x_party_site_rec.attribute4,
2216 X_ATTRIBUTE5 => x_party_site_rec.attribute5,
2217 X_ATTRIBUTE6 => x_party_site_rec.attribute6,
2218 X_ATTRIBUTE7 => x_party_site_rec.attribute7,
2219 X_ATTRIBUTE8 => x_party_site_rec.attribute8,
2220 X_ATTRIBUTE9 => x_party_site_rec.attribute9,
2221 X_ATTRIBUTE10 => x_party_site_rec.attribute10,
2222 X_ATTRIBUTE11 => x_party_site_rec.attribute11,
2223 X_ATTRIBUTE12 => x_party_site_rec.attribute12,
2224 X_ATTRIBUTE13 => x_party_site_rec.attribute13,
2225 X_ATTRIBUTE14 => x_party_site_rec.attribute14,
2226 X_ATTRIBUTE15 => x_party_site_rec.attribute15,
2227 X_ATTRIBUTE16 => x_party_site_rec.attribute16,
2228 X_ATTRIBUTE17 => x_party_site_rec.attribute17,
2229 X_ATTRIBUTE18 => x_party_site_rec.attribute18,
2230 X_ATTRIBUTE19 => x_party_site_rec.attribute19,
2231 X_ATTRIBUTE20 => x_party_site_rec.attribute20,
2232 X_ORIG_SYSTEM_REFERENCE => x_party_site_rec.orig_system_reference,
2233 X_LANGUAGE => x_party_site_rec.language,
2234 X_MAILSTOP => x_party_site_rec.mailstop,
2235 X_IDENTIFYING_ADDRESS_FLAG => x_party_site_rec.identifying_address_flag,
2236 X_STATUS => x_party_site_rec.status,
2237 X_PARTY_SITE_NAME => x_party_site_rec.party_site_name,
2238 X_ADDRESSEE => x_party_site_rec.addressee,
2239 X_CREATED_BY_MODULE => x_party_site_rec.created_by_module,
2240 X_APPLICATION_ID => x_party_site_rec.application_id,
2241 X_ACTUAL_CONTENT_SOURCE => x_actual_cont_source,
2242 X_GLOBAL_LOCATION_NUMBER => x_party_site_rec.global_location_number,
2243 X_DUNS_NUMBER_C => x_party_site_rec.duns_number_c
2244 );
2245
2246 --Standard call to get message count and if count is 1, get message info.
2247 FND_MSG_PUB.Count_And_Get(
2248 p_encoded => FND_API.G_FALSE,
2249 p_count => x_msg_count,
2250 p_data => x_msg_data );
2251
2252 EXCEPTION
2253 WHEN FND_API.G_EXC_ERROR THEN
2254 x_return_status := FND_API.G_RET_STS_ERROR;
2255
2256 FND_MSG_PUB.Count_And_Get(
2257 p_encoded => FND_API.G_FALSE,
2258 p_count => x_msg_count,
2259 p_data => x_msg_data );
2260
2261 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2263
2264 FND_MSG_PUB.Count_And_Get(
2265 p_encoded => FND_API.G_FALSE,
2266 p_count => x_msg_count,
2267 p_data => x_msg_data );
2268
2269 WHEN OTHERS THEN
2270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2271 fnd_message.set_name( 'AR', 'HZ_API_OTHERS_EXCEP' );
2272 fnd_message.set_token( 'ERROR' ,SQLERRM );
2273 fnd_msg_pub.add;
2274 FND_MSG_PUB.Count_And_Get(
2275 p_encoded => FND_API.G_FALSE,
2276 p_count => x_msg_count,
2277 p_data => x_msg_data );
2278
2279 END get_party_site_rec;
2280
2281 ------------------ACCOUNT_SITE_-----------------------------------------------
2282
2283 PROCEDURE do_create_cust_acct_site (
2284 p_cust_acct_site_rec IN OUT NOCOPY
2285 HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE,
2286 p_org_id IN NUMBER DEFAULT null,
2287 x_cust_acct_site_id OUT NOCOPY NUMBER,
2288 x_return_status IN OUT NOCOPY VARCHAR2
2289 );
2290
2291 PROCEDURE do_create_cust_site_use (
2292 p_cust_site_use_rec IN OUT NOCOPY HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE,
2293 p_customer_profile_rec IN OUT NOCOPY HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE,
2294 p_create_profile IN VARCHAR2 := FND_API.G_TRUE,
2295 p_create_profile_amt IN VARCHAR2 := FND_API.G_TRUE,
2296 p_org_id IN NUMBER DEFAULT null,
2297 x_site_use_id OUT NOCOPY NUMBER,
2298 x_return_status IN OUT NOCOPY VARCHAR2
2299 );
2300
2301 PROCEDURE denormalize_site_use_flag (
2302 p_cust_acct_site_id IN NUMBER,
2303 p_site_use_code IN VARCHAR2,
2304 p_flag IN VARCHAR2
2305 );
2306
2307 --------------------------------------
2308 -- private procedures and functions
2309 --------------------------------------
2310
2311
2312 PROCEDURE do_create_cust_acct_site (
2313 p_cust_acct_site_rec IN OUT NOCOPY
2314 HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE,
2315 p_org_id IN NUMBER DEFAULT null,
2316 x_cust_acct_site_id OUT NOCOPY NUMBER,
2317 x_return_status IN OUT NOCOPY VARCHAR2
2318 ) IS
2319
2320 l_msg_count NUMBER;
2321 l_msg_data VARCHAR2(2000);
2322
2323 l_location_id NUMBER;
2324 l_loc_id NUMBER;
2325 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
2326
2327 BEGIN
2328
2329 --Initialize the created by module
2330 p_cust_acct_site_rec.created_by_module := 'HZ_TCA_CUSTOMER_MERGE';
2331
2332 --Bug 10119069 p_cust_acct_site_rec.orig_system_reference := null;
2333 p_cust_acct_site_rec.cust_acct_site_id := null;
2334
2335
2336 -- Call table-handler.
2337 HZ_CUST_ACCT_SITES_PKG.Insert_Row (
2338 X_CUST_ACCT_SITE_ID => p_cust_acct_site_rec.cust_acct_site_id,
2339 X_CUST_ACCOUNT_ID => p_cust_acct_site_rec.cust_account_id,
2340 X_PARTY_SITE_ID => p_cust_acct_site_rec.party_site_id,
2341 X_ATTRIBUTE_CATEGORY => p_cust_acct_site_rec.attribute_category,
2342 X_ATTRIBUTE1 => p_cust_acct_site_rec.attribute1,
2343 X_ATTRIBUTE2 => p_cust_acct_site_rec.attribute2,
2344 X_ATTRIBUTE3 => p_cust_acct_site_rec.attribute3,
2345 X_ATTRIBUTE4 => p_cust_acct_site_rec.attribute4,
2346 X_ATTRIBUTE5 => p_cust_acct_site_rec.attribute5,
2347 X_ATTRIBUTE6 => p_cust_acct_site_rec.attribute6,
2348 X_ATTRIBUTE7 => p_cust_acct_site_rec.attribute7,
2349 X_ATTRIBUTE8 => p_cust_acct_site_rec.attribute8,
2350 X_ATTRIBUTE9 => p_cust_acct_site_rec.attribute9,
2351 X_ATTRIBUTE10 => p_cust_acct_site_rec.attribute10,
2352 X_ATTRIBUTE11 => p_cust_acct_site_rec.attribute11,
2353 X_ATTRIBUTE12 => p_cust_acct_site_rec.attribute12,
2354 X_ATTRIBUTE13 => p_cust_acct_site_rec.attribute13,
2355 X_ATTRIBUTE14 => p_cust_acct_site_rec.attribute14,
2356 X_ATTRIBUTE15 => p_cust_acct_site_rec.attribute15,
2357 X_ATTRIBUTE16 => p_cust_acct_site_rec.attribute16,
2358 X_ATTRIBUTE17 => p_cust_acct_site_rec.attribute17,
2359 X_ATTRIBUTE18 => p_cust_acct_site_rec.attribute18,
2360 X_ATTRIBUTE19 => p_cust_acct_site_rec.attribute19,
2361 X_ATTRIBUTE20 => p_cust_acct_site_rec.attribute20,
2362 X_GLOBAL_ATTRIBUTE_CATEGORY=>p_cust_acct_site_rec.global_attribute_category,
2363 X_GLOBAL_ATTRIBUTE1 => p_cust_acct_site_rec.global_attribute1,
2364 X_GLOBAL_ATTRIBUTE2 => p_cust_acct_site_rec.global_attribute2,
2365 X_GLOBAL_ATTRIBUTE3 => p_cust_acct_site_rec.global_attribute3,
2366 X_GLOBAL_ATTRIBUTE4 => p_cust_acct_site_rec.global_attribute4,
2367 X_GLOBAL_ATTRIBUTE5 => p_cust_acct_site_rec.global_attribute5,
2368 X_GLOBAL_ATTRIBUTE6 => p_cust_acct_site_rec.global_attribute6,
2369 X_GLOBAL_ATTRIBUTE7 => p_cust_acct_site_rec.global_attribute7,
2370 X_GLOBAL_ATTRIBUTE8 => p_cust_acct_site_rec.global_attribute8,
2371 X_GLOBAL_ATTRIBUTE9 => p_cust_acct_site_rec.global_attribute9,
2372 X_GLOBAL_ATTRIBUTE10 => p_cust_acct_site_rec.global_attribute10,
2373 X_GLOBAL_ATTRIBUTE11 => p_cust_acct_site_rec.global_attribute11,
2374 X_GLOBAL_ATTRIBUTE12 => p_cust_acct_site_rec.global_attribute12,
2375 X_GLOBAL_ATTRIBUTE13 => p_cust_acct_site_rec.global_attribute13,
2376 X_GLOBAL_ATTRIBUTE14 => p_cust_acct_site_rec.global_attribute14,
2377 X_GLOBAL_ATTRIBUTE15 => p_cust_acct_site_rec.global_attribute15,
2378 X_GLOBAL_ATTRIBUTE16 => p_cust_acct_site_rec.global_attribute16,
2379 X_GLOBAL_ATTRIBUTE17 => p_cust_acct_site_rec.global_attribute17,
2380 X_GLOBAL_ATTRIBUTE18 => p_cust_acct_site_rec.global_attribute18,
2381 X_GLOBAL_ATTRIBUTE19 => p_cust_acct_site_rec.global_attribute19,
2382 X_GLOBAL_ATTRIBUTE20 => p_cust_acct_site_rec.global_attribute20,
2383 X_ORIG_SYSTEM_REFERENCE => p_cust_acct_site_rec.orig_system_reference,
2384 X_STATUS => p_cust_acct_site_rec.status,
2385 X_CUSTOMER_CATEGORY_CODE => p_cust_acct_site_rec.customer_category_code,
2386 X_LANGUAGE => p_cust_acct_site_rec.language,
2387 X_KEY_ACCOUNT_FLAG => p_cust_acct_site_rec.key_account_flag,
2388 X_TP_HEADER_ID => p_cust_acct_site_rec.tp_header_id,
2389 X_ECE_TP_LOCATION_CODE => p_cust_acct_site_rec.ece_tp_location_code,
2390 X_PRIMARY_SPECIALIST_ID=> p_cust_acct_site_rec.primary_specialist_id,
2391 X_SECONDARY_SPECIALIST_ID => p_cust_acct_site_rec.secondary_specialist_id,
2392 X_TERRITORY_ID => p_cust_acct_site_rec.territory_id,
2393 X_TERRITORY => p_cust_acct_site_rec.territory,
2394 X_TRANSLATED_CUSTOMER_NAME =>p_cust_acct_site_rec.translated_customer_name,
2395 X_OBJECT_VERSION_NUMBER => 1,
2396 X_CREATED_BY_MODULE => p_cust_acct_site_rec.created_by_module,
2397 X_APPLICATION_ID => p_cust_acct_site_rec.application_id,
2398 X_ORG_ID => p_org_id
2399 );
2400 /*
2401 per HLD,mosr record should not be created for copy case, since old osr is still active
2402 hz_orig_system_ref_pvt.create_mosr_for_merge(
2403 FND_API.G_FALSE,
2404 'HZ_CUST_ACCT_SITES_ALL',
2405 p_cust_acct_site_rec.cust_acct_site_id,
2406 x_return_status,
2407 l_msg_count,
2408 l_msg_data);
2409 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2410 RAISE FND_API.G_EXC_ERROR;
2411 END IF;
2412 */
2413 x_cust_acct_site_id := p_cust_acct_site_rec.cust_acct_site_id;
2414
2415 END do_create_cust_acct_site;
2416
2417
2418 PROCEDURE do_create_cust_site_use (
2419 p_cust_site_use_rec IN OUT NOCOPY HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE,
2420 p_customer_profile_rec IN OUT NOCOPY HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE,
2421 p_create_profile IN VARCHAR2 := FND_API.G_TRUE,
2422 p_create_profile_amt IN VARCHAR2 := FND_API.G_TRUE,
2423 p_org_id IN NUMBER DEFAULT null,
2424 x_site_use_id OUT NOCOPY NUMBER,
2425 x_return_status IN OUT NOCOPY VARCHAR2
2426 ) IS
2427
2428
2429 l_dummy VARCHAR2(1);
2430 l_message_count NUMBER;
2431 l_msg_count NUMBER;
2432 l_msg_data VARCHAR2(2000);
2433 l_flag VARCHAR2(1);
2434
2435 l_party_site_use_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_REC_TYPE;
2436 l_party_site_id NUMBER;
2437 l_party_site_use_id NUMBER;
2438 l_cust_account_profile_id NUMBER;
2439 l_bill_to_flag HZ_CUST_ACCT_SITES_ALL.bill_to_flag%TYPE;
2440 l_ship_to_flag HZ_CUST_ACCT_SITES_ALL.ship_to_flag%TYPE;
2441 l_market_flag HZ_CUST_ACCT_SITES_ALL.market_flag%TYPE;
2442
2443 BEGIN
2444
2445 p_cust_site_use_rec.created_by_module := 'HZ_TCA_CUSTOMER_MERGE';
2446 p_cust_site_use_rec.site_use_id := null;
2447 p_cust_site_use_rec.orig_system_reference := null;
2448
2449
2450 -- Call table-handler.
2451
2452 HZ_CUST_SITE_USES_PKG.Insert_Row (
2453 X_SITE_USE_ID => p_cust_site_use_rec.site_use_id,
2454 X_CUST_ACCT_SITE_ID => p_cust_site_use_rec.cust_acct_site_id,
2455 X_SITE_USE_CODE => p_cust_site_use_rec.site_use_code,
2456 X_PRIMARY_FLAG => p_cust_site_use_rec.primary_flag,
2457 X_STATUS => p_cust_site_use_rec.status,
2458 X_LOCATION => p_cust_site_use_rec.location,
2459 X_CONTACT_ID => p_cust_site_use_rec.contact_id,
2460 X_BILL_TO_SITE_USE_ID => p_cust_site_use_rec.bill_to_site_use_id,
2461 X_ORIG_SYSTEM_REFERENCE => p_cust_site_use_rec.orig_system_reference,
2462 X_SIC_CODE => p_cust_site_use_rec.sic_code,
2463 X_PAYMENT_TERM_ID => p_cust_site_use_rec.payment_term_id,
2464 X_GSA_INDICATOR => p_cust_site_use_rec.gsa_indicator,
2465 X_SHIP_PARTIAL => p_cust_site_use_rec.ship_partial,
2466 X_SHIP_VIA => p_cust_site_use_rec.ship_via,
2467 X_FOB_POINT => p_cust_site_use_rec.fob_point,
2468 X_ORDER_TYPE_ID => p_cust_site_use_rec.order_type_id,
2469 X_PRICE_LIST_ID => p_cust_site_use_rec.price_list_id,
2470 X_FREIGHT_TERM => p_cust_site_use_rec.freight_term,
2471 X_WAREHOUSE_ID => p_cust_site_use_rec.warehouse_id,
2472 X_TERRITORY_ID => p_cust_site_use_rec.territory_id,
2473 X_ATTRIBUTE_CATEGORY => p_cust_site_use_rec.attribute_category,
2474 X_ATTRIBUTE1 => p_cust_site_use_rec.attribute1,
2475 X_ATTRIBUTE2 => p_cust_site_use_rec.attribute2,
2476 X_ATTRIBUTE3 => p_cust_site_use_rec.attribute3,
2477 X_ATTRIBUTE4 => p_cust_site_use_rec.attribute4,
2478 X_ATTRIBUTE5 => p_cust_site_use_rec.attribute5,
2479 X_ATTRIBUTE6 => p_cust_site_use_rec.attribute6,
2480 X_ATTRIBUTE7 => p_cust_site_use_rec.attribute7,
2481 X_ATTRIBUTE8 => p_cust_site_use_rec.attribute8,
2482 X_ATTRIBUTE9 => p_cust_site_use_rec.attribute9,
2483 X_ATTRIBUTE10 => p_cust_site_use_rec.attribute10,
2484 X_TAX_REFERENCE => p_cust_site_use_rec.tax_reference,
2485 X_SORT_PRIORITY => p_cust_site_use_rec.sort_priority,
2486 X_TAX_CODE => p_cust_site_use_rec.tax_code,
2487 X_ATTRIBUTE11 => p_cust_site_use_rec.attribute11,
2488 X_ATTRIBUTE12 => p_cust_site_use_rec.attribute12,
2489 X_ATTRIBUTE13 => p_cust_site_use_rec.attribute13,
2490 X_ATTRIBUTE14 => p_cust_site_use_rec.attribute14,
2491 X_ATTRIBUTE15 => p_cust_site_use_rec.attribute15,
2492 X_ATTRIBUTE16 => p_cust_site_use_rec.attribute16,
2493 X_ATTRIBUTE17 => p_cust_site_use_rec.attribute17,
2494 X_ATTRIBUTE18 => p_cust_site_use_rec.attribute18,
2495 X_ATTRIBUTE19 => p_cust_site_use_rec.attribute19,
2496 X_ATTRIBUTE20 => p_cust_site_use_rec.attribute20,
2497 X_ATTRIBUTE21 => p_cust_site_use_rec.attribute21,
2498 X_ATTRIBUTE22 => p_cust_site_use_rec.attribute22,
2499 X_ATTRIBUTE23 => p_cust_site_use_rec.attribute23,
2500 X_ATTRIBUTE24 => p_cust_site_use_rec.attribute24,
2501 X_ATTRIBUTE25 => p_cust_site_use_rec.attribute25,
2502 X_DEMAND_CLASS_CODE => p_cust_site_use_rec.demand_class_code,
2503 X_TAX_HEADER_LEVEL_FLAG => p_cust_site_use_rec.tax_header_level_flag,
2504 X_TAX_ROUNDING_RULE => p_cust_site_use_rec.tax_rounding_rule,
2505 X_GLOBAL_ATTRIBUTE1 => p_cust_site_use_rec.global_attribute1,
2506 X_GLOBAL_ATTRIBUTE2 => p_cust_site_use_rec.global_attribute2,
2507 X_GLOBAL_ATTRIBUTE3 => p_cust_site_use_rec.global_attribute3,
2508 X_GLOBAL_ATTRIBUTE4 => p_cust_site_use_rec.global_attribute4,
2509 X_GLOBAL_ATTRIBUTE5 => p_cust_site_use_rec.global_attribute5,
2510 X_GLOBAL_ATTRIBUTE6 => p_cust_site_use_rec.global_attribute6,
2511 X_GLOBAL_ATTRIBUTE7 => p_cust_site_use_rec.global_attribute7,
2512 X_GLOBAL_ATTRIBUTE8 => p_cust_site_use_rec.global_attribute8,
2513 X_GLOBAL_ATTRIBUTE9 => p_cust_site_use_rec.global_attribute9,
2514 X_GLOBAL_ATTRIBUTE10 =>p_cust_site_use_rec.global_attribute10,
2515 X_GLOBAL_ATTRIBUTE11 => p_cust_site_use_rec.global_attribute11,
2516 X_GLOBAL_ATTRIBUTE12 => p_cust_site_use_rec.global_attribute12,
2517 X_GLOBAL_ATTRIBUTE13 => p_cust_site_use_rec.global_attribute13,
2518 X_GLOBAL_ATTRIBUTE14 => p_cust_site_use_rec.global_attribute14,
2519 X_GLOBAL_ATTRIBUTE15 => p_cust_site_use_rec.global_attribute15,
2520 X_GLOBAL_ATTRIBUTE16 => p_cust_site_use_rec.global_attribute16,
2521 X_GLOBAL_ATTRIBUTE17 => p_cust_site_use_rec.global_attribute17,
2522 X_GLOBAL_ATTRIBUTE18 => p_cust_site_use_rec.global_attribute18,
2523 X_GLOBAL_ATTRIBUTE19 => p_cust_site_use_rec.global_attribute19,
2524 X_GLOBAL_ATTRIBUTE20 => p_cust_site_use_rec.global_attribute20,
2525 X_GLOBAL_ATTRIBUTE_CATEGORY=>p_cust_site_use_rec.global_attribute_category,
2526 X_PRIMARY_SALESREP_ID => p_cust_site_use_rec.primary_salesrep_id,
2527 X_FINCHRG_RECEIVABLES_TRX_ID=>p_cust_site_use_rec.finchrg_receivables_trx_id,
2528 X_DATES_NEGATIVE_TOLERANCE=> p_cust_site_use_rec.dates_negative_tolerance,
2529 X_DATES_POSITIVE_TOLERANCE=> p_cust_site_use_rec.dates_positive_tolerance,
2530 X_DATE_TYPE_PREFERENCE => p_cust_site_use_rec.date_type_preference,
2531 X_OVER_SHIPMENT_TOLERANCE => p_cust_site_use_rec.over_shipment_tolerance,
2532 X_UNDER_SHIPMENT_TOLERANCE=> p_cust_site_use_rec.under_shipment_tolerance,
2533 X_ITEM_CROSS_REF_PREF => p_cust_site_use_rec.item_cross_ref_pref,
2534 X_OVER_RETURN_TOLERANCE => p_cust_site_use_rec.over_return_tolerance,
2535 X_UNDER_RETURN_TOLERANCE => p_cust_site_use_rec.under_return_tolerance,
2536 X_SHIP_SETS_INCLUDE_LINES_FLAG=>p_cust_site_use_rec.ship_sets_include_lines_flag,
2537 X_ARRIVALSETS_INCLUDE_LINES_FG=> p_cust_site_use_rec.arrivalsets_include_lines_flag,
2538 X_SCHED_DATE_PUSH_FLAG => p_cust_site_use_rec.sched_date_push_flag,
2539 X_INVOICE_QUANTITY_RULE => p_cust_site_use_rec.invoice_quantity_rule,
2540 X_PRICING_EVENT => p_cust_site_use_rec.pricing_event,
2541 X_GL_ID_REC => p_cust_site_use_rec.gl_id_rec,
2542 X_GL_ID_REV => p_cust_site_use_rec.gl_id_rev,
2543 X_GL_ID_TAX => p_cust_site_use_rec.gl_id_tax,
2544 X_GL_ID_FREIGHT => p_cust_site_use_rec.gl_id_freight,
2545 X_GL_ID_CLEARING => p_cust_site_use_rec.gl_id_clearing,
2546 X_GL_ID_UNBILLED => p_cust_site_use_rec.gl_id_unbilled,
2547 X_GL_ID_UNEARNED => p_cust_site_use_rec.gl_id_unearned,
2548 X_GL_ID_UNPAID_REC => p_cust_site_use_rec.gl_id_unpaid_rec,
2549 X_GL_ID_REMITTANCE => p_cust_site_use_rec.gl_id_remittance,
2550 X_GL_ID_FACTOR => p_cust_site_use_rec.gl_id_factor,
2551 X_TAX_CLASSIFICATION => p_cust_site_use_rec.tax_classification,
2552 X_OBJECT_VERSION_NUMBER => 1,
2553 X_CREATED_BY_MODULE => p_cust_site_use_rec.created_by_module,
2554 X_APPLICATION_ID => p_cust_site_use_rec.application_id,
2555 X_ORG_ID => p_org_id
2556 );
2557
2558 -- If this is a active bill_to or ship_to or market,
2559 -- set the appropriate denormalized flag in hz_cust_acct_sites_all.
2560
2561 IF p_cust_site_use_rec.site_use_code IN ('BILL_TO', 'SHIP_TO', 'MARKET' ) THEN
2562 ----Bug No.5211233
2563 IF p_cust_site_use_rec.primary_flag = 'Y' THEN
2564 l_flag := 'P';
2565 ----Bug No. 5211233
2566
2567 ELSIF p_cust_site_use_rec.status = 'A' OR
2568 p_cust_site_use_rec.status IS NULL OR
2569 p_cust_site_use_rec.status = FND_API.G_MISS_CHAR
2570 THEN
2571 l_flag := 'Y';
2572 ELSE
2573 l_flag := NULL;
2574 END IF;
2575
2576 denormalize_site_use_flag (
2577 p_cust_site_use_rec.cust_acct_site_id,
2578 p_cust_site_use_rec.site_use_code,
2579 l_flag );
2580
2581 END IF;
2582
2583 IF p_create_profile = FND_API.G_TRUE THEN
2584
2585 -- Create the profile for the site use
2586
2587 p_customer_profile_rec.site_use_id := p_cust_site_use_rec.site_use_id;
2588 p_customer_profile_rec.created_by_module := p_cust_site_use_rec.created_by_module;
2589 p_customer_profile_rec.application_id := p_cust_site_use_rec.application_id;
2590
2591 SELECT CUST_ACCOUNT_ID INTO p_customer_profile_rec.cust_account_id
2592 FROM HZ_CUST_ACCT_SITES_ALL
2593 WHERE CUST_ACCT_SITE_ID = p_cust_site_use_rec.cust_acct_site_id;
2594
2595 create_customer_profile (
2596 p_customer_profile_rec => p_customer_profile_rec,
2597 p_create_profile_amt => p_create_profile_amt,
2598 x_return_status => x_return_status,
2599 x_msg_count => l_msg_count,
2600 x_msg_data => l_msg_data,
2601 x_cust_account_profile_id => l_cust_account_profile_id );
2602
2603 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2604 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2605 RAISE FND_API.G_EXC_ERROR;
2606 ELSE
2607 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2608 END IF;
2609 END IF;
2610
2611 END IF;
2612 /*
2613 per HLD,mosr record should not be created for copy case, since old osr is still active
2614 hz_orig_system_ref_pvt.create_mosr_for_merge(
2615 FND_API.G_FALSE,
2616 'HZ_CUST_SITE_USES_ALL',
2617 p_cust_site_use_rec.site_use_id,
2618 x_return_status,
2619 l_msg_count,
2620 l_msg_data);
2621 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2622 RAISE FND_API.G_EXC_ERROR;
2623 END IF;
2624 */
2625 x_site_use_id := p_cust_site_use_rec.site_use_id;
2626
2627 END do_create_cust_site_use;
2628
2629
2630 PROCEDURE denormalize_site_use_flag (
2631 p_cust_acct_site_id IN NUMBER,
2632 p_site_use_code IN VARCHAR2,
2633 p_flag IN VARCHAR2
2634 ) IS
2635
2636 l_debug_prefix VARCHAR2(30) := ''; --'denormalize_site_use_flag'
2637
2638 BEGIN
2639
2640
2641 IF p_site_use_code = 'BILL_TO' THEN
2642 UPDATE HZ_CUST_ACCT_SITES_ALL
2643 SET BILL_TO_FLAG = p_flag
2644 WHERE CUST_ACCT_SITE_ID = p_cust_acct_site_id;
2645 ELSIF p_site_use_code = 'SHIP_TO' THEN
2646 UPDATE HZ_CUST_ACCT_SITES_ALL
2647 SET SHIP_TO_FLAG = p_flag
2648 WHERE CUST_ACCT_SITE_ID = p_cust_acct_site_id;
2649 ELSIF p_site_use_code = 'MARKET' THEN
2650 UPDATE HZ_CUST_ACCT_SITES_ALL
2651 SET MARKET_FLAG = p_flag
2652 WHERE CUST_ACCT_SITE_ID = p_cust_acct_site_id;
2653 END IF;
2654
2655
2656 END denormalize_site_use_flag;
2657
2658 --------------------------------------
2659 -- public procedures and functions
2660 --------------------------------------
2661
2662 PROCEDURE create_cust_acct_site (
2663 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2664 p_cust_acct_site_rec IN HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE,
2665 p_org_id IN NUMBER ,
2666 x_cust_acct_site_id OUT NOCOPY NUMBER,
2667 x_return_status OUT NOCOPY VARCHAR2,
2668 x_msg_count OUT NOCOPY NUMBER,
2669 x_msg_data OUT NOCOPY VARCHAR2
2670 ) IS
2671
2672 l_cust_acct_site_rec HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE :=
2673 p_cust_acct_site_rec;
2674
2675 BEGIN
2676
2677 -- Standard start of API savepoint
2678 SAVEPOINT create_cust_acct_site;
2679
2680
2681 -- Initialize message list if p_init_msg_list is set to TRUE.
2682 IF FND_API.to_Boolean(p_init_msg_list) THEN
2683 FND_MSG_PUB.initialize;
2684 END IF;
2685
2686 -- Initialize API return status to success.
2687 x_return_status := FND_API.G_RET_STS_SUCCESS;
2688
2689 -- Call to business logic.
2690 do_create_cust_acct_site (
2691 l_cust_acct_site_rec,
2692 p_org_id,
2693 x_cust_acct_site_id,
2694 x_return_status );
2695
2696 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2697 -- Invoke business event system.
2698 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN --10168091
2699 HZ_BUSINESS_EVENT_V2PVT.create_cust_acct_site_event (
2700 l_cust_acct_site_rec );
2701 END IF;
2702 END IF;
2703
2704 -- Standard call to get message count and if count is 1, get message info.
2705 FND_MSG_PUB.Count_And_Get(
2706 p_encoded => FND_API.G_FALSE,
2707 p_count => x_msg_count,
2708 p_data => x_msg_data );
2709
2710
2711 EXCEPTION
2712 WHEN FND_API.G_EXC_ERROR THEN
2713 ROLLBACK TO create_cust_acct_site;
2714 x_return_status := FND_API.G_RET_STS_ERROR;
2715
2716 FND_MSG_PUB.Count_And_Get(
2717 p_encoded => FND_API.G_FALSE,
2718 p_count => x_msg_count,
2719 p_data => x_msg_data );
2720
2721
2722 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2723 ROLLBACK TO create_cust_acct_site;
2724 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2725
2726 FND_MSG_PUB.Count_And_Get(
2727 p_encoded => FND_API.G_FALSE,
2728 p_count => x_msg_count,
2729 p_data => x_msg_data );
2730
2731 WHEN OTHERS THEN
2732 ROLLBACK TO create_cust_acct_site;
2733 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2734
2735 fnd_message.set_name( 'AR', 'HZ_API_OTHERS_EXCEP' );
2736 fnd_message.set_token( 'ERROR' ,SQLERRM );
2737 fnd_msg_pub.add;
2738
2739 FND_MSG_PUB.Count_And_Get(
2740 p_encoded => FND_API.G_FALSE,
2741 p_count => x_msg_count,
2742 p_data => x_msg_data );
2743
2744
2745 END create_cust_acct_site;
2746
2747 PROCEDURE create_cust_site_use (
2748 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2749 p_cust_site_use_rec IN HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE,
2750 p_customer_profile_rec IN HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE,
2751 p_create_profile IN VARCHAR2 := FND_API.G_TRUE,
2752 p_create_profile_amt IN VARCHAR2 := FND_API.G_TRUE,
2753 p_org_id IN NUMBER ,
2754 x_site_use_id OUT NOCOPY NUMBER,
2755 x_return_status OUT NOCOPY VARCHAR2,
2756 x_msg_count OUT NOCOPY NUMBER,
2757 x_msg_data OUT NOCOPY VARCHAR2
2758 ) IS
2759
2760 l_cust_site_use_rec HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE
2761 := p_cust_site_use_rec;
2762 l_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE
2763 := p_customer_profile_rec;
2764
2765 BEGIN
2766
2767 -- Standard start of API savepoint
2768 SAVEPOINT create_cust_site_use;
2769
2770 -- Initialize message list if p_init_msg_list is set to TRUE.
2771 IF FND_API.to_Boolean(p_init_msg_list) THEN
2772 FND_MSG_PUB.initialize;
2773 END IF;
2774
2775 -- Initialize API return status to success.
2776 x_return_status := FND_API.G_RET_STS_SUCCESS;
2777
2778 -- Call to business logic.
2779 do_create_cust_site_use (
2780 l_cust_site_use_rec,
2781 l_customer_profile_rec,
2782 p_create_profile,
2783 p_create_profile_amt,
2784 p_org_id,
2785 x_site_use_id,
2786 x_return_status );
2787
2788 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2789 -- Invoke business event system.
2790 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN --10168091
2791 HZ_BUSINESS_EVENT_V2PVT.create_cust_site_use_event (
2792 l_cust_site_use_rec,
2793 l_customer_profile_rec,
2794 p_create_profile,
2795 p_create_profile_amt );
2796 END IF;
2797 END IF;
2798
2799 -- Standard call to get message count and if count is 1, get message info.
2800 FND_MSG_PUB.Count_And_Get(
2801 p_encoded => FND_API.G_FALSE,
2802 p_count => x_msg_count,
2803 p_data => x_msg_data );
2804
2805 EXCEPTION
2806 WHEN FND_API.G_EXC_ERROR THEN
2807 ROLLBACK TO create_cust_site_use;
2808 x_return_status := FND_API.G_RET_STS_ERROR;
2809
2810 FND_MSG_PUB.Count_And_Get(
2811 p_encoded => FND_API.G_FALSE,
2812 p_count => x_msg_count,
2813 p_data => x_msg_data );
2814
2815 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2816 ROLLBACK TO create_cust_site_use;
2817 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2818
2819 FND_MSG_PUB.Count_And_Get(
2820 p_encoded => FND_API.G_FALSE,
2821 p_count => x_msg_count,
2822 p_data => x_msg_data );
2823
2824
2825 WHEN OTHERS THEN
2826 ROLLBACK TO create_cust_site_use;
2827 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2828
2829 fnd_message.set_name( 'AR', 'HZ_API_OTHERS_EXCEP' );
2830 fnd_message.set_token( 'ERROR' ,SQLERRM );
2831 fnd_msg_pub.add;
2832
2833 FND_MSG_PUB.Count_And_Get(
2834 p_encoded => FND_API.G_FALSE,
2835 p_count => x_msg_count,
2836 p_data => x_msg_data );
2837
2838 END create_cust_site_use;
2839
2840 ----------------CUST_PROFILE-----------------------------------------------------
2841
2842 ----Private procedures
2843 PROCEDURE do_create_customer_profile (
2844 p_customer_profile_rec IN OUT NOCOPY HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE,
2845 p_create_profile_amt IN VARCHAR2,
2846 x_cust_account_profile_id OUT NOCOPY NUMBER,
2847 x_return_status IN OUT NOCOPY VARCHAR2
2848 );
2849
2850
2851 PROCEDURE do_create_cust_profile_amt (
2852 p_check_foreign_key IN VARCHAR2,
2853 p_cust_profile_amt_rec IN OUT NOCOPY HZ_CUSTOMER_PROFILE_V2PUB.CUST_PROFILE_AMT_REC_TYPE,
2854 x_cust_acct_profile_amt_id OUT NOCOPY NUMBER,
2855 x_return_status IN OUT NOCOPY VARCHAR2
2856 );
2857
2858 ---Definition
2859
2860 PROCEDURE do_create_customer_profile (
2861 p_customer_profile_rec IN OUT NOCOPY HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE,
2862 p_create_profile_amt IN VARCHAR2,
2863 x_cust_account_profile_id OUT NOCOPY NUMBER,
2864 x_return_status IN OUT NOCOPY VARCHAR2
2865 ) IS
2866
2867 l_is_first BOOLEAN := TRUE;
2868 l_msg_count NUMBER;
2869 l_msg_data VARCHAR2(2000);
2870
2871 l_status HZ_CUST_PROFILE_CLASSES.status%TYPE;
2872 l_profile_class_name HZ_CUST_PROFILE_CLASSES.name%TYPE;
2873 l_profile_class_id NUMBER;
2874
2875
2876 BEGIN
2877
2878 p_customer_profile_rec.cust_account_profile_id := null;
2879 p_customer_profile_rec.created_by_module := 'HZ_TCA_CUSTOMER_MERGE';
2880
2881 -- Call table-handler.
2882 -- Table_handler is taking care of default customer profile to profile class.
2883 -- HYU modification of Table Handler add column review_cycle, last_credit_review_date, party_id
2884
2885 HZ_CUSTOMER_PROFILES_PKG.Insert_Row (
2886 X_CUST_ACCOUNT_PROFILE_ID=> p_customer_profile_rec.cust_account_profile_id,
2887 X_CUST_ACCOUNT_ID => p_customer_profile_rec.cust_account_id,
2888 X_STATUS => p_customer_profile_rec.status,
2889 X_COLLECTOR_ID => p_customer_profile_rec.collector_id,
2890 X_CREDIT_ANALYST_ID => p_customer_profile_rec.credit_analyst_id,
2891 X_CREDIT_CHECKING => p_customer_profile_rec.credit_checking,
2892 X_NEXT_CREDIT_REVIEW_DATE=> p_customer_profile_rec.next_credit_review_date,
2893 X_TOLERANCE => p_customer_profile_rec.tolerance,
2894 X_DISCOUNT_TERMS => p_customer_profile_rec.discount_terms,
2895 X_DUNNING_LETTERS => p_customer_profile_rec.dunning_letters,
2896 X_INTEREST_CHARGES => p_customer_profile_rec.interest_charges,
2897 X_SEND_STATEMENTS => p_customer_profile_rec.send_statements,
2898 X_CREDIT_BALANCE_STATEMENTS=> p_customer_profile_rec.credit_balance_statements,
2899 X_CREDIT_HOLD => p_customer_profile_rec.credit_hold,
2900 X_PROFILE_CLASS_ID => p_customer_profile_rec.profile_class_id,
2901 X_SITE_USE_ID => p_customer_profile_rec.site_use_id,
2902 X_CREDIT_RATING => p_customer_profile_rec.credit_rating,
2903 X_RISK_CODE => p_customer_profile_rec.risk_code,
2904 X_STANDARD_TERMS => p_customer_profile_rec.standard_terms,
2905 X_OVERRIDE_TERMS => p_customer_profile_rec.override_terms,
2906 X_DUNNING_LETTER_SET_ID => p_customer_profile_rec.dunning_letter_set_id,
2907 X_INTEREST_PERIOD_DAYS => p_customer_profile_rec.interest_period_days,
2908 X_PAYMENT_GRACE_DAYS => p_customer_profile_rec.payment_grace_days,
2909 X_DISCOUNT_GRACE_DAYS => p_customer_profile_rec.discount_grace_days,
2910 X_STATEMENT_CYCLE_ID => p_customer_profile_rec.statement_cycle_id,
2911 X_ACCOUNT_STATUS => p_customer_profile_rec.account_status,
2912 X_PERCENT_COLLECTABLE => p_customer_profile_rec.percent_collectable,
2913 X_AUTOCASH_HIERARCHY_ID => p_customer_profile_rec.autocash_hierarchy_id,
2914 X_ATTRIBUTE_CATEGORY => p_customer_profile_rec.attribute_category,
2915 X_ATTRIBUTE1 => p_customer_profile_rec.attribute1,
2916 X_ATTRIBUTE2 => p_customer_profile_rec.attribute2,
2917 X_ATTRIBUTE3 => p_customer_profile_rec.attribute3,
2918 X_ATTRIBUTE4 => p_customer_profile_rec.attribute4,
2919 X_ATTRIBUTE5 => p_customer_profile_rec.attribute5,
2920 X_ATTRIBUTE6 => p_customer_profile_rec.attribute6,
2921 X_ATTRIBUTE7 => p_customer_profile_rec.attribute7,
2922 X_ATTRIBUTE8 => p_customer_profile_rec.attribute8,
2923 X_ATTRIBUTE9 => p_customer_profile_rec.attribute9,
2924 X_ATTRIBUTE10 => p_customer_profile_rec.attribute10,
2925 X_ATTRIBUTE11 => p_customer_profile_rec.attribute11,
2926 X_ATTRIBUTE12 => p_customer_profile_rec.attribute12,
2927 X_ATTRIBUTE13 => p_customer_profile_rec.attribute13,
2928 X_ATTRIBUTE14 => p_customer_profile_rec.attribute14,
2929 X_ATTRIBUTE15 => p_customer_profile_rec.attribute15,
2930 X_AUTO_REC_INCL_DISPUTED_FLAG => p_customer_profile_rec.auto_rec_incl_disputed_flag,
2931 X_TAX_PRINTING_OPTION => p_customer_profile_rec.tax_printing_option,
2932 X_CHARGE_ON_FINANCE_CHARGE_FG => p_customer_profile_rec.charge_on_finance_charge_flag,
2933 X_GROUPING_RULE_ID => p_customer_profile_rec.grouping_rule_id,
2934 X_CLEARING_DAYS => p_customer_profile_rec.clearing_days,
2935 X_JGZZ_ATTRIBUTE_CATEGORY=> p_customer_profile_rec.jgzz_attribute_category,
2936 X_JGZZ_ATTRIBUTE1 => p_customer_profile_rec.jgzz_attribute1,
2937 X_JGZZ_ATTRIBUTE2 => p_customer_profile_rec.jgzz_attribute2,
2938 X_JGZZ_ATTRIBUTE3 => p_customer_profile_rec.jgzz_attribute3,
2939 X_JGZZ_ATTRIBUTE4 => p_customer_profile_rec.jgzz_attribute4,
2940 X_JGZZ_ATTRIBUTE5 => p_customer_profile_rec.jgzz_attribute5,
2941 X_JGZZ_ATTRIBUTE6 => p_customer_profile_rec.jgzz_attribute6,
2942 X_JGZZ_ATTRIBUTE7 => p_customer_profile_rec.jgzz_attribute7,
2943 X_JGZZ_ATTRIBUTE8 => p_customer_profile_rec.jgzz_attribute8,
2944 X_JGZZ_ATTRIBUTE9 => p_customer_profile_rec.jgzz_attribute9,
2945 X_JGZZ_ATTRIBUTE10 => p_customer_profile_rec.jgzz_attribute10,
2946 X_JGZZ_ATTRIBUTE11 => p_customer_profile_rec.jgzz_attribute11,
2947 X_JGZZ_ATTRIBUTE12 => p_customer_profile_rec.jgzz_attribute12,
2948 X_JGZZ_ATTRIBUTE13 => p_customer_profile_rec.jgzz_attribute13,
2949 X_JGZZ_ATTRIBUTE14 => p_customer_profile_rec.jgzz_attribute14,
2950 X_JGZZ_ATTRIBUTE15 => p_customer_profile_rec.jgzz_attribute15,
2951 X_GLOBAL_ATTRIBUTE1 => p_customer_profile_rec.global_attribute1,
2952 X_GLOBAL_ATTRIBUTE2 => p_customer_profile_rec.global_attribute2,
2953 X_GLOBAL_ATTRIBUTE3 => p_customer_profile_rec.global_attribute3,
2954 X_GLOBAL_ATTRIBUTE4 => p_customer_profile_rec.global_attribute4,
2955 X_GLOBAL_ATTRIBUTE5 => p_customer_profile_rec.global_attribute5,
2956 X_GLOBAL_ATTRIBUTE6 => p_customer_profile_rec.global_attribute6,
2957 X_GLOBAL_ATTRIBUTE7 => p_customer_profile_rec.global_attribute7,
2958 X_GLOBAL_ATTRIBUTE8 => p_customer_profile_rec.global_attribute8,
2959 X_GLOBAL_ATTRIBUTE9 => p_customer_profile_rec.global_attribute9,
2960 X_GLOBAL_ATTRIBUTE10 => p_customer_profile_rec.global_attribute10,
2961 X_GLOBAL_ATTRIBUTE11 => p_customer_profile_rec.global_attribute11,
2962 X_GLOBAL_ATTRIBUTE12 => p_customer_profile_rec.global_attribute12,
2963 X_GLOBAL_ATTRIBUTE13 => p_customer_profile_rec.global_attribute13,
2964 X_GLOBAL_ATTRIBUTE14 => p_customer_profile_rec.global_attribute14,
2965 X_GLOBAL_ATTRIBUTE15 => p_customer_profile_rec.global_attribute15,
2966 X_GLOBAL_ATTRIBUTE16 => p_customer_profile_rec.global_attribute16,
2967 X_GLOBAL_ATTRIBUTE17 => p_customer_profile_rec.global_attribute17,
2968 X_GLOBAL_ATTRIBUTE18 => p_customer_profile_rec.global_attribute18,
2969 X_GLOBAL_ATTRIBUTE19 => p_customer_profile_rec.global_attribute19,
2970 X_GLOBAL_ATTRIBUTE20 => p_customer_profile_rec.global_attribute20,
2971 X_GLOBAL_ATTRIBUTE_CATEGORY=> p_customer_profile_rec.global_attribute_category,
2972 X_CONS_INV_FLAG => p_customer_profile_rec.cons_inv_flag,
2973 X_CONS_INV_TYPE => p_customer_profile_rec.cons_inv_type,
2974 X_AUTOCASH_HIERARCHY_ID_ADR => p_customer_profile_rec.autocash_hierarchy_id_for_adr,
2975 X_LOCKBOX_MATCHING_OPTION=> p_customer_profile_rec.lockbox_matching_option,
2976 X_OBJECT_VERSION_NUMBER => 1,
2977 X_CREATED_BY_MODULE => p_customer_profile_rec.created_by_module,
2978 X_APPLICATION_ID => p_customer_profile_rec.application_id,
2979 X_REVIEW_CYCLE => p_customer_profile_rec.review_cycle,
2980 X_LAST_CREDIT_REVIEW_DATE=> p_customer_profile_rec.last_credit_review_date,
2981 X_PARTY_ID => p_customer_profile_rec.party_id,
2982 X_CREDIT_CLASSIFICATION => p_customer_profile_rec.credit_classification,
2983 X_CONS_BILL_LEVEL => p_customer_profile_rec.cons_bill_level,
2984 X_LATE_CHARGE_CALCULATION_TRX => p_customer_profile_rec.late_charge_calculation_trx,
2985 X_CREDIT_ITEMS_FLAG => p_customer_profile_rec.credit_items_flag,
2986 X_DISPUTED_TRANSACTIONS_FLAG => p_customer_profile_rec.disputed_transactions_flag,
2987 X_LATE_CHARGE_TYPE => p_customer_profile_rec.late_charge_type,
2988 X_LATE_CHARGE_TERM_ID => p_customer_profile_rec.late_charge_term_id,
2989 X_INTEREST_CALCULATION_PERIOD => p_customer_profile_rec.interest_calculation_period,
2990 X_HOLD_CHARGED_INVOICES_FLAG => p_customer_profile_rec.hold_charged_invoices_flag,
2991 X_MESSAGE_TEXT_ID => p_customer_profile_rec.message_text_id,
2992 X_MULTIPLE_INTEREST_RATES_FLAG => p_customer_profile_rec.multiple_interest_rates_flag,
2993 X_CHARGE_BEGIN_DATE => p_customer_profile_rec.charge_begin_date,
2994 X_AUTOMATCH_SET_ID => p_customer_profile_rec.automatch_set_id
2995 );
2996
2997 x_cust_account_profile_id := p_customer_profile_rec.cust_account_profile_id;
2998
2999 -- No need to create profile amt as this logic is present in
3000 -- p_create_profile_amt is TRUE. Otherwise, simply return.
3001
3002 END do_create_customer_profile;
3003
3004
3005 PROCEDURE do_create_cust_profile_amt (
3006 p_check_foreign_key IN VARCHAR2,
3007 p_cust_profile_amt_rec IN OUT NOCOPY HZ_CUSTOMER_PROFILE_V2PUB.CUST_PROFILE_AMT_REC_TYPE,
3008 x_cust_acct_profile_amt_id OUT NOCOPY NUMBER,
3009 x_return_status IN OUT NOCOPY VARCHAR2
3010 ) IS
3011
3012
3013 BEGIN
3014
3015 p_cust_profile_amt_rec.created_by_module := 'HZ_TCA_CUSTOMER_MERGE';
3016 p_cust_profile_amt_rec.cust_acct_profile_amt_id := null;
3017
3018
3019 -- Call table-handler.
3020 HZ_CUST_PROFILE_AMTS_PKG.Insert_Row (
3021 X_CUST_ACCT_PROFILE_AMT_ID=>p_cust_profile_amt_rec.cust_acct_profile_amt_id,
3022 X_CUST_ACCOUNT_PROFILE_ID => p_cust_profile_amt_rec.cust_account_profile_id,
3023 X_CURRENCY_CODE => p_cust_profile_amt_rec.currency_code,
3024 X_TRX_CREDIT_LIMIT => p_cust_profile_amt_rec.trx_credit_limit,
3025 X_OVERALL_CREDIT_LIMIT => p_cust_profile_amt_rec.overall_credit_limit,
3026 X_MIN_DUNNING_AMOUNT => p_cust_profile_amt_rec.min_dunning_amount,
3027 X_MIN_DUNNING_INVOICE_AMOUNT=> p_cust_profile_amt_rec.min_dunning_invoice_amount,
3028 X_MAX_INTEREST_CHARGE => p_cust_profile_amt_rec.max_interest_charge,
3029 X_MIN_STATEMENT_AMOUNT => p_cust_profile_amt_rec.min_statement_amount,
3030 X_AUTO_REC_MIN_RECEIPT_AMOUNT => p_cust_profile_amt_rec.auto_rec_min_receipt_amount,
3031 X_INTEREST_RATE => p_cust_profile_amt_rec.interest_rate,
3032 X_ATTRIBUTE_CATEGORY => p_cust_profile_amt_rec.attribute_category,
3033 X_ATTRIBUTE1 => p_cust_profile_amt_rec.attribute1,
3034 X_ATTRIBUTE2 => p_cust_profile_amt_rec.attribute2,
3035 X_ATTRIBUTE3 => p_cust_profile_amt_rec.attribute3,
3036 X_ATTRIBUTE4 => p_cust_profile_amt_rec.attribute4,
3037 X_ATTRIBUTE5 => p_cust_profile_amt_rec.attribute5,
3038 X_ATTRIBUTE6 => p_cust_profile_amt_rec.attribute6,
3039 X_ATTRIBUTE7 => p_cust_profile_amt_rec.attribute7,
3040 X_ATTRIBUTE8 => p_cust_profile_amt_rec.attribute8,
3041 X_ATTRIBUTE9 => p_cust_profile_amt_rec.attribute9,
3042 X_ATTRIBUTE10 => p_cust_profile_amt_rec.attribute10,
3043 X_ATTRIBUTE11 => p_cust_profile_amt_rec.attribute11,
3044 X_ATTRIBUTE12 => p_cust_profile_amt_rec.attribute12,
3045 X_ATTRIBUTE13 => p_cust_profile_amt_rec.attribute13,
3046 X_ATTRIBUTE14 => p_cust_profile_amt_rec.attribute14,
3047 X_ATTRIBUTE15 => p_cust_profile_amt_rec.attribute15,
3048 X_MIN_FC_BALANCE_AMOUNT => p_cust_profile_amt_rec.min_fc_balance_amount,
3049 X_MIN_FC_INVOICE_AMOUNT => p_cust_profile_amt_rec.min_fc_invoice_amount,
3050 X_CUST_ACCOUNT_ID => p_cust_profile_amt_rec.cust_account_id,
3051 X_SITE_USE_ID => p_cust_profile_amt_rec.site_use_id,
3052 X_EXPIRATION_DATE => p_cust_profile_amt_rec.expiration_date,
3053 X_JGZZ_ATTRIBUTE_CATEGORY=> p_cust_profile_amt_rec.jgzz_attribute_category,
3054 X_JGZZ_ATTRIBUTE1 => p_cust_profile_amt_rec.jgzz_attribute1,
3055 X_JGZZ_ATTRIBUTE2 => p_cust_profile_amt_rec.jgzz_attribute2,
3056 X_JGZZ_ATTRIBUTE3 => p_cust_profile_amt_rec.jgzz_attribute3,
3057 X_JGZZ_ATTRIBUTE4 => p_cust_profile_amt_rec.jgzz_attribute4,
3058 X_JGZZ_ATTRIBUTE5 => p_cust_profile_amt_rec.jgzz_attribute5,
3059 X_JGZZ_ATTRIBUTE6 => p_cust_profile_amt_rec.jgzz_attribute6,
3060 X_JGZZ_ATTRIBUTE7 => p_cust_profile_amt_rec.jgzz_attribute7,
3061 X_JGZZ_ATTRIBUTE8 => p_cust_profile_amt_rec.jgzz_attribute8,
3062 X_JGZZ_ATTRIBUTE9 => p_cust_profile_amt_rec.jgzz_attribute9,
3063 X_JGZZ_ATTRIBUTE10 => p_cust_profile_amt_rec.jgzz_attribute10,
3064 X_JGZZ_ATTRIBUTE11 => p_cust_profile_amt_rec.jgzz_attribute11,
3065 X_JGZZ_ATTRIBUTE12 => p_cust_profile_amt_rec.jgzz_attribute12,
3066 X_JGZZ_ATTRIBUTE13 => p_cust_profile_amt_rec.jgzz_attribute13,
3067 X_JGZZ_ATTRIBUTE14 => p_cust_profile_amt_rec.jgzz_attribute14,
3068 X_JGZZ_ATTRIBUTE15 => p_cust_profile_amt_rec.jgzz_attribute15,
3069 X_GLOBAL_ATTRIBUTE1 => p_cust_profile_amt_rec.global_attribute1,
3070 X_GLOBAL_ATTRIBUTE2 => p_cust_profile_amt_rec.global_attribute2,
3071 X_GLOBAL_ATTRIBUTE3 => p_cust_profile_amt_rec.global_attribute3,
3072 X_GLOBAL_ATTRIBUTE4 => p_cust_profile_amt_rec.global_attribute4,
3073 X_GLOBAL_ATTRIBUTE5 => p_cust_profile_amt_rec.global_attribute5,
3074 X_GLOBAL_ATTRIBUTE6 => p_cust_profile_amt_rec.global_attribute6,
3075 X_GLOBAL_ATTRIBUTE7 => p_cust_profile_amt_rec.global_attribute7,
3076 X_GLOBAL_ATTRIBUTE8 => p_cust_profile_amt_rec.global_attribute8,
3077 X_GLOBAL_ATTRIBUTE9 => p_cust_profile_amt_rec.global_attribute9,
3078 X_GLOBAL_ATTRIBUTE10 => p_cust_profile_amt_rec.global_attribute10,
3079 X_GLOBAL_ATTRIBUTE11 => p_cust_profile_amt_rec.global_attribute11,
3080 X_GLOBAL_ATTRIBUTE12 => p_cust_profile_amt_rec.global_attribute12,
3081 X_GLOBAL_ATTRIBUTE13 => p_cust_profile_amt_rec.global_attribute13,
3082 X_GLOBAL_ATTRIBUTE14 => p_cust_profile_amt_rec.global_attribute14,
3083 X_GLOBAL_ATTRIBUTE15 => p_cust_profile_amt_rec.global_attribute15,
3084 X_GLOBAL_ATTRIBUTE16 => p_cust_profile_amt_rec.global_attribute16,
3085 X_GLOBAL_ATTRIBUTE17 => p_cust_profile_amt_rec.global_attribute17,
3086 X_GLOBAL_ATTRIBUTE18 => p_cust_profile_amt_rec.global_attribute18,
3087 X_GLOBAL_ATTRIBUTE19 => p_cust_profile_amt_rec.global_attribute19,
3088 X_GLOBAL_ATTRIBUTE20 => p_cust_profile_amt_rec.global_attribute20,
3089 X_GLOBAL_ATTRIBUTE_CATEGORY=> p_cust_profile_amt_rec.global_attribute_category,
3090 X_OBJECT_VERSION_NUMBER => 1,
3091 X_CREATED_BY_MODULE => p_cust_profile_amt_rec.created_by_module,
3092 X_APPLICATION_ID => p_cust_profile_amt_rec.application_id,
3093 X_EXCHANGE_RATE_TYPE => p_cust_profile_amt_rec.exchange_rate_type,
3094 X_MIN_FC_INVOICE_OVERDUE_TYPE => p_cust_profile_amt_rec.min_fc_invoice_overdue_type,
3095 X_MIN_FC_INVOICE_PERCENT => p_cust_profile_amt_rec.min_fc_invoice_percent,
3096 X_MIN_FC_BALANCE_OVERDUE_TYPE => p_cust_profile_amt_rec.min_fc_balance_overdue_type,
3097 X_MIN_FC_BALANCE_PERCENT => p_cust_profile_amt_rec.min_fc_balance_percent,
3098 X_INTEREST_TYPE => p_cust_profile_amt_rec.interest_type,
3099 X_INTEREST_FIXED_AMOUNT => p_cust_profile_amt_rec.interest_fixed_amount,
3100 X_INTEREST_SCHEDULE_ID => p_cust_profile_amt_rec.interest_schedule_id,
3101 X_PENALTY_TYPE => p_cust_profile_amt_rec.penalty_type,
3102 X_PENALTY_RATE => p_cust_profile_amt_rec.penalty_rate,
3103 X_MIN_INTEREST_CHARGE => p_cust_profile_amt_rec.min_interest_charge,
3104 X_PENALTY_FIXED_AMOUNT => p_cust_profile_amt_rec.penalty_fixed_amount,
3105 X_PENALTY_SCHEDULE_ID => p_cust_profile_amt_rec.penalty_schedule_id
3106 );
3107
3108 x_cust_acct_profile_amt_id := p_cust_profile_amt_rec.cust_acct_profile_amt_id;
3109
3110
3111 END do_create_cust_profile_amt;
3112
3113
3114
3115 --------------------------------------
3116 -- public procedures and functions
3117 --------------------------------------
3118 PROCEDURE create_customer_profile (
3119 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3120 p_customer_profile_rec IN HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE,
3121 p_create_profile_amt IN VARCHAR2 := FND_API.G_TRUE,
3122 x_cust_account_profile_id OUT NOCOPY NUMBER,
3123 x_return_status OUT NOCOPY VARCHAR2,
3124 x_msg_count OUT NOCOPY NUMBER,
3125 x_msg_data OUT NOCOPY VARCHAR2
3126 ) IS
3127
3128 l_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE
3129 := p_customer_profile_rec;
3130
3131 BEGIN
3132
3133 -- Standard start of API savepoint
3134 SAVEPOINT create_customer_profile;
3135
3136
3137 -- Initialize message list if p_init_msg_list is set to TRUE.
3138 IF FND_API.to_Boolean(p_init_msg_list) THEN
3139 FND_MSG_PUB.initialize;
3140 END IF;
3141
3142 -- Initialize API return status to success.
3143 x_return_status := FND_API.G_RET_STS_SUCCESS;
3144
3145 -- Call to business logic.
3146 do_create_customer_profile (
3147 l_customer_profile_rec,
3148 p_create_profile_amt,
3149 x_cust_account_profile_id,
3150 x_return_status );
3151
3152 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3153 -- Invoke business event system.
3154 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN --10168091
3155 HZ_BUSINESS_EVENT_V2PVT.create_customer_profile_event (
3156 l_customer_profile_rec,
3157 p_create_profile_amt );
3158 END IF;
3159 END IF;
3160
3161 -- Standard call to get message count and if count is 1, get message info.
3162 FND_MSG_PUB.Count_And_Get(
3163 p_encoded => FND_API.G_FALSE,
3164 p_count => x_msg_count,
3165 p_data => x_msg_data );
3166
3167
3168 EXCEPTION
3169 WHEN FND_API.G_EXC_ERROR THEN
3170 ROLLBACK TO create_customer_profile;
3171 x_return_status := FND_API.G_RET_STS_ERROR;
3172
3173 FND_MSG_PUB.Count_And_Get(
3174 p_encoded => FND_API.G_FALSE,
3175 p_count => x_msg_count,
3176 p_data => x_msg_data );
3177
3178
3179 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3180 ROLLBACK TO create_customer_profile;
3181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3182
3183 FND_MSG_PUB.Count_And_Get(
3184 p_encoded => FND_API.G_FALSE,
3185 p_count => x_msg_count,
3186 p_data => x_msg_data );
3187
3188
3189 WHEN OTHERS THEN
3190 ROLLBACK TO create_customer_profile;
3191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3192
3193 fnd_message.set_name( 'AR', 'HZ_API_OTHERS_EXCEP' );
3194 fnd_message.set_token( 'ERROR' ,SQLERRM );
3195 fnd_msg_pub.add;
3196
3197 FND_MSG_PUB.Count_And_Get(
3198 p_encoded => FND_API.G_FALSE,
3199 p_count => x_msg_count,
3200 p_data => x_msg_data );
3201
3202
3203 END create_customer_profile;
3204
3205
3206
3207 PROCEDURE create_cust_profile_amt (
3208 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3209 p_check_foreign_key IN VARCHAR2 := FND_API.G_TRUE,
3210 p_cust_profile_amt_rec IN HZ_CUSTOMER_PROFILE_V2PUB.CUST_PROFILE_AMT_REC_TYPE,
3211 x_cust_acct_profile_amt_id OUT NOCOPY NUMBER,
3212 x_return_status OUT NOCOPY VARCHAR2,
3213 x_msg_count OUT NOCOPY NUMBER,
3214 x_msg_data OUT NOCOPY VARCHAR2
3215 ) IS
3216
3217 l_cust_profile_amt_rec HZ_CUSTOMER_PROFILE_V2PUB.CUST_PROFILE_AMT_REC_TYPE :=
3218 p_cust_profile_amt_rec;
3219
3220 BEGIN
3221
3222 -- Standard start of API savepoint
3223 SAVEPOINT create_cust_profile_amt;
3224
3225 -- Initialize message list if p_init_msg_list is set to TRUE.
3226 IF FND_API.to_Boolean(p_init_msg_list) THEN
3227 FND_MSG_PUB.initialize;
3228 END IF;
3229
3230 -- Initialize API return status to success.
3231 x_return_status := FND_API.G_RET_STS_SUCCESS;
3232
3233 -- Call to business logic.
3234 do_create_cust_profile_amt (
3235 p_check_foreign_key,
3236 l_cust_profile_amt_rec,
3237 x_cust_acct_profile_amt_id,
3238 x_return_status );
3239
3240 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3241 -- Invoke business event system.
3242 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN --10168091
3243 HZ_BUSINESS_EVENT_V2PVT.create_cust_profile_amt_event (
3244 l_cust_profile_amt_rec );
3245 END IF;
3246 END IF;
3247
3248 -- Standard call to get message count and if count is 1, get message info.
3249 FND_MSG_PUB.Count_And_Get(
3250 p_encoded => FND_API.G_FALSE,
3251 p_count => x_msg_count,
3252 p_data => x_msg_data );
3253
3254
3255 EXCEPTION
3256 WHEN FND_API.G_EXC_ERROR THEN
3257 ROLLBACK TO create_cust_profile_amt;
3258 x_return_status := FND_API.G_RET_STS_ERROR;
3259
3260 FND_MSG_PUB.Count_And_Get(
3261 p_encoded => FND_API.G_FALSE,
3262 p_count => x_msg_count,
3263 p_data => x_msg_data );
3264
3265 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3266 ROLLBACK TO create_cust_profile_amt;
3267 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3268
3269 FND_MSG_PUB.Count_And_Get(
3270 p_encoded => FND_API.G_FALSE,
3271 p_count => x_msg_count,
3272 p_data => x_msg_data );
3273
3274 WHEN OTHERS THEN
3275 ROLLBACK TO create_cust_profile_amt;
3276 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3277
3278 fnd_message.set_name( 'AR', 'HZ_API_OTHERS_EXCEP' );
3279 fnd_message.set_token( 'ERROR' ,SQLERRM );
3280 fnd_msg_pub.add;
3281
3282 FND_MSG_PUB.Count_And_Get(
3283 p_encoded => FND_API.G_FALSE,
3284 p_count => x_msg_count,
3285 p_data => x_msg_data );
3286
3287 END create_cust_profile_amt;
3288
3289 ------------------------CONTACT_POINTS------------------------
3290
3291 --------------------------------------
3292 -- declaration of private procedures and functions
3293 --------------------------------------
3294
3295 PROCEDURE do_create_contact_point (
3296 p_contact_point_rec IN OUT NOCOPY hz_contact_point_v2pub.contact_point_rec_type,
3297 p_edi_rec IN OUT NOCOPY hz_contact_point_v2pub.edi_rec_type,
3298 p_eft_rec IN OUT NOCOPY hz_contact_point_v2pub.eft_rec_type,
3299 p_email_rec IN OUT NOCOPY hz_contact_point_v2pub.email_rec_type,
3300 p_phone_rec IN OUT NOCOPY hz_contact_point_v2pub.phone_rec_type,
3301 p_telex_rec IN OUT NOCOPY hz_contact_point_v2pub.telex_rec_type,
3302 p_web_rec IN OUT NOCOPY hz_contact_point_v2pub.web_rec_type,
3303 x_contact_point_id OUT NOCOPY NUMBER,
3304 x_return_status IN OUT NOCOPY VARCHAR2
3305 );
3306
3307 PROCEDURE do_denormalize_contact_point (
3308 p_party_id IN NUMBER,
3309 p_contact_point_type IN VARCHAR2,
3310 p_url IN VARCHAR2,
3311 p_email_address IN VARCHAR2
3312 );
3313
3314 PROCEDURE do_unset_prim_contact_point (
3315 p_owner_table_name IN VARCHAR2,
3316 p_owner_table_id IN NUMBER,
3317 p_contact_point_type IN VARCHAR2,
3318 p_contact_point_id IN NUMBER
3319 );
3320
3321 PROCEDURE do_unset_primary_by_purpose (
3322 p_owner_table_name IN VARCHAR2,
3323 p_owner_table_id IN NUMBER,
3324 p_contact_point_type IN VARCHAR2,
3325 p_contact_point_purpose IN VARCHAR2,
3326 p_contact_point_id IN NUMBER
3327 );
3328
3329 --------------------------------------
3330 -- body of private procedures
3331 --------------------------------------
3332
3333
3334 PROCEDURE do_create_contact_point (
3335 p_contact_point_rec IN OUT NOCOPY hz_contact_point_v2pub.contact_point_rec_type,
3336 p_edi_rec IN OUT NOCOPY hz_contact_point_v2pub.edi_rec_type,
3337 p_eft_rec IN OUT NOCOPY hz_contact_point_v2pub.eft_rec_type,
3338 p_email_rec IN OUT NOCOPY hz_contact_point_v2pub.email_rec_type,
3339 p_phone_rec IN OUT NOCOPY hz_contact_point_v2pub.phone_rec_type,
3340 p_telex_rec IN OUT NOCOPY hz_contact_point_v2pub.telex_rec_type,
3341 p_web_rec IN OUT NOCOPY hz_contact_point_v2pub.web_rec_type,
3342 x_contact_point_id OUT NOCOPY NUMBER,
3343 x_return_status IN OUT NOCOPY VARCHAR2
3344 ) IS
3345
3346 l_dummy VARCHAR2(1);
3347 l_message_count NUMBER;
3348 l_msg_count NUMBER;
3349 l_msg_data VARCHAR2(2000);
3350
3351 l_formatted_phone_number VARCHAR2(100);
3352 l_country_code hz_locations.country%TYPE;
3353 l_transposed_phone_number hz_contact_points.transposed_phone_number%TYPE;
3354
3355 l_edi_rec hz_contact_point_v2pub.edi_rec_type;
3356 l_eft_rec hz_contact_point_v2pub.eft_rec_type;
3357 l_email_rec hz_contact_point_v2pub.email_rec_type;
3358 l_phone_rec hz_contact_point_v2pub.phone_rec_type;
3359 l_telex_rec hz_contact_point_v2pub.telex_rec_type;
3360 l_web_rec hz_contact_point_v2pub.web_rec_type;
3361 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
3362
3363 -- Bug 2197181: added for mix-n-match project: the contact point
3364 -- must be visible.
3365
3366 -- SSM SST Integration and Extension
3367 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
3368 -- There is no need to check if the data-source is selected.
3369
3370 CURSOR c_cp (p_owner_table_name IN VARCHAR2,
3371 p_owner_table_id IN NUMBER,
3372 p_contact_point_type IN VARCHAR2) IS
3373 SELECT 'Y'
3374 FROM hz_contact_points
3375 WHERE owner_table_name = p_owner_table_name
3376 AND owner_table_id = p_owner_table_id
3377 AND contact_point_type = p_contact_point_type
3378 /* AND HZ_MIXNM_UTILITY.isDataSourceSelected (
3379 g_cpt_selected_datasources, actual_content_source ) = 'Y'*/
3380 AND status = 'A'
3381 AND rownum = 1;
3382
3383
3384 BEGIN
3385
3386 p_contact_point_rec.created_by_module := 'HZ_TCA_CUSTOMER_MERGE';
3387 p_contact_point_rec.contact_point_id := NULL;
3388 p_contact_point_rec.orig_system_reference := NULL;
3389
3390 IF p_contact_point_rec.contact_point_type = 'EDI' THEN
3391 l_edi_rec := p_edi_rec;
3392 ELSIF p_contact_point_rec.contact_point_type = 'EFT' THEN
3393 l_eft_rec := p_eft_rec;
3394 ELSIF p_contact_point_rec.contact_point_type = 'EMAIL' THEN
3395 l_email_rec := p_email_rec;
3396 ELSIF p_contact_point_rec.contact_point_type = 'PHONE' THEN
3397 l_phone_rec := p_phone_rec;
3398 ELSIF p_contact_point_rec.contact_point_type = 'TLX' THEN
3399 l_telex_rec := p_telex_rec;
3400 ELSIF p_contact_point_rec.contact_point_type = 'WEB' THEN
3401 l_web_rec := p_web_rec;
3402 ELSE
3403 l_edi_rec := p_edi_rec;
3404 l_eft_rec := p_eft_rec;
3405 l_email_rec := p_email_rec;
3406 l_phone_rec := p_phone_rec;
3407 l_telex_rec := p_telex_rec;
3408 l_web_rec := p_web_rec;
3409 END IF;
3410
3411
3412 -- If this is the first active contact point for the combination of
3413 -- owner_table_name, owner_table_id, contact_point_type, we need to
3414 -- mark it as primary no matter the value of primary_flag,
3415 -- If primary_flag = 'Y', we need to unmark the previous primary.
3416 -- Please note, if status is NULL or MISSING, we treat it as 'A'
3417 -- and in validation part, we already checked that primary_flag = 'Y'
3418 -- and status = 'I' can not both be set.
3419
3420 -- Bug 2197181: added for mix-n-match project: the primary flag
3421 -- can be set to 'Y' only if the contact point will be visible. If
3422 -- it is not visible, the flag must be reset to 'N'.
3423
3424
3425 IF p_contact_point_rec.status IS NULL OR
3426 p_contact_point_rec.status = fnd_api.g_miss_char OR
3427 p_contact_point_rec.status = 'A'
3428 THEN
3429 IF p_contact_point_rec.primary_flag = 'Y' THEN
3430 -- Bug 2197181: added for mix-n-match project
3431
3432 -- SSM SST Integration and Extension
3433 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
3434 -- There is no need to check if the data-source is selected.
3435
3436 -- IF g_cpt_is_datasource_selected = 'Y' THEN
3437 -- Unmark previous primary contact point.
3438 do_unset_prim_contact_point(p_contact_point_rec.owner_table_name,
3439 p_contact_point_rec.owner_table_id,
3440 p_contact_point_rec.contact_point_type,
3441 p_contact_point_rec.contact_point_id);
3442 -- ELSE
3443 -- p_contact_point_rec.primary_flag := 'N';
3444 -- END IF;
3445 ELSE
3446 -- Bug 2117973: modified to conform to Applications PL/SQL standards.
3447 OPEN c_cp (p_contact_point_rec.owner_table_name,
3448 p_contact_point_rec.owner_table_id,
3449 p_contact_point_rec.contact_point_type);
3450 FETCH c_cp INTO l_dummy;
3451
3452 IF c_cp%NOTFOUND /*AND
3453 -- Bug 2197181: added for mix-n-match project
3454 g_cpt_is_datasource_selected = 'Y'*/
3455 THEN
3456 -- First active and visible contact point per type for this entity
3457 p_contact_point_rec.primary_flag := 'Y';
3458 ELSE
3459 p_contact_point_rec.primary_flag := 'N';
3460 END IF;
3461 CLOSE c_cp;
3462 END IF;
3463
3464 -- De-normalize primary contact point to hz_parties.
3465 -- url is mandatory if contact_point_type = 'WEB'.
3466 -- email_address is mandatory if contact_point_type = 'EMAIL'.
3467
3468 IF p_contact_point_rec.primary_flag = 'Y' AND
3469 p_contact_point_rec.owner_table_name = 'HZ_PARTIES' AND
3470 (p_contact_point_rec.contact_point_type = 'WEB' OR
3471 p_contact_point_rec.contact_point_type = 'EMAIL')
3472 THEN
3473 do_denormalize_contact_point(p_contact_point_rec.owner_table_id,
3474 p_contact_point_rec.contact_point_type,
3475 l_web_rec.url,
3476 l_email_rec.email_address);
3477 END IF;
3478 END IF;
3479
3480 -- There is only one primary per purpose contact point exist for
3481 -- the combination of owner_table_name, owner_table_id, contact_point_type
3482 -- and contact_point_purpose. If primary_by_purpose is set to 'Y',
3483 -- we need to unset the previous primary per purpose contact point to
3484 -- non-primary. Since setting primary_by_purpose is only making
3485 -- sense when contact_point_purpose has some value, we ignore
3486 -- the primary_by_purpose (setting it to 'N') if contact_point_purpose
3487 -- is NULL.
3488
3489 -- Bug 2197181: added for mix-n-match project: the primary by purpose
3490 -- flag can be set to 'Y' only if the contact point will be visible.
3491 -- If it is not visible, the flag must be reset to 'N'.
3492
3493 IF p_contact_point_rec.contact_point_purpose IS NOT NULL AND
3494 p_contact_point_rec.contact_point_purpose <> fnd_api.g_miss_char
3495 THEN
3496 IF p_contact_point_rec.primary_by_purpose = 'Y' THEN
3497 -- Bug 2197181: added for mix-n-match project
3498
3499 -- SSM SST Integration and Extension
3500 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
3501 -- There is no need to check if the data-source is selected.
3502
3503 -- IF g_cpt_is_datasource_selected = 'Y' THEN
3504 do_unset_primary_by_purpose (p_contact_point_rec.owner_table_name,
3505 p_contact_point_rec.owner_table_id,
3506 p_contact_point_rec.contact_point_type,
3507 p_contact_point_rec.contact_point_purpose,
3508 p_contact_point_rec.contact_point_id);
3509 -- ELSE
3510 -- p_contact_point_rec.primary_by_purpose := 'N';
3511 -- END IF;
3512 END IF;
3513 ELSE
3514 p_contact_point_rec.primary_by_purpose := 'N';
3515 END IF;
3516 --Start of bug 7299887
3517 -- Populate transposed_phone_number
3518 IF p_contact_point_rec.contact_point_type = 'PHONE' THEN
3519 IF l_phone_rec.phone_country_code IS NOT NULL AND
3520 l_phone_rec.phone_country_code <> fnd_api.g_miss_char THEN
3521
3522 l_transposed_phone_number := l_phone_rec.phone_country_code;
3523 END IF;
3524
3525 IF l_phone_rec.phone_area_code IS NOT NULL AND
3526 l_phone_rec.phone_area_code <> fnd_api.g_miss_char THEN
3527
3528 l_transposed_phone_number := l_transposed_phone_number ||l_phone_rec.phone_area_code;
3529
3530 END IF;
3531
3532 -- phone_number is mandatory
3533 l_transposed_phone_number := hz_phone_number_pkg.transpose(
3534 l_transposed_phone_number || l_phone_rec.phone_number);
3535 END IF;
3536 --End of bug 7299887
3537
3538 -- Call table-handler.
3539 hz_contact_points_pkg.insert_row (
3540 x_contact_point_id => p_contact_point_rec.contact_point_id,
3541 x_contact_point_type => p_contact_point_rec.contact_point_type,
3542 x_status => p_contact_point_rec.status,
3543 x_owner_table_name => p_contact_point_rec.owner_table_name,
3544 x_owner_table_id => p_contact_point_rec.owner_table_id,
3545 x_primary_flag => p_contact_point_rec.primary_flag,
3546 x_orig_system_reference => p_contact_point_rec.orig_system_reference,
3547 x_attribute_category => p_contact_point_rec.attribute_category,
3548 x_attribute1 => p_contact_point_rec.attribute1,
3549 x_attribute2 => p_contact_point_rec.attribute2,
3550 x_attribute3 => p_contact_point_rec.attribute3,
3551 x_attribute4 => p_contact_point_rec.attribute4,
3552 x_attribute5 => p_contact_point_rec.attribute5,
3553 x_attribute6 => p_contact_point_rec.attribute6,
3554 x_attribute7 => p_contact_point_rec.attribute7,
3555 x_attribute8 => p_contact_point_rec.attribute8,
3556 x_attribute9 => p_contact_point_rec.attribute9,
3557 x_attribute10 => p_contact_point_rec.attribute10,
3558 x_attribute11 => p_contact_point_rec.attribute11,
3559 x_attribute12 => p_contact_point_rec.attribute12,
3560 x_attribute13 => p_contact_point_rec.attribute13,
3561 x_attribute14 => p_contact_point_rec.attribute14,
3562 x_attribute15 => p_contact_point_rec.attribute15,
3563 x_attribute16 => p_contact_point_rec.attribute16,
3564 x_attribute17 => p_contact_point_rec.attribute17,
3565 x_attribute18 => p_contact_point_rec.attribute18,
3566 x_attribute19 => p_contact_point_rec.attribute19,
3567 x_attribute20 => p_contact_point_rec.attribute20,
3568 x_edi_transaction_handling => l_edi_rec.edi_transaction_handling,
3569 x_edi_id_number => l_edi_rec.edi_id_number,
3570 x_edi_payment_method => l_edi_rec.edi_payment_method,
3571 x_edi_payment_format => l_edi_rec.edi_payment_format,
3572 x_edi_remittance_method => l_edi_rec.edi_remittance_method,
3573 x_edi_remittance_instruction => l_edi_rec.edi_remittance_instruction,
3574 x_edi_tp_header_id => l_edi_rec.edi_tp_header_id,
3575 x_edi_ece_tp_location_code => l_edi_rec.edi_ece_tp_location_code,
3576 x_eft_transmission_program_id => l_eft_rec.eft_transmission_program_id,
3577 x_eft_printing_program_id => l_eft_rec.eft_printing_program_id,
3578 x_eft_user_number => l_eft_rec.eft_user_number,
3579 x_eft_swift_code => l_eft_rec.eft_swift_code,
3580 x_email_format => l_email_rec.email_format,
3581 x_email_address => l_email_rec.email_address,
3582 x_phone_calling_calendar => l_phone_rec.phone_calling_calendar,
3583 x_last_contact_dt_time => l_phone_rec.last_contact_dt_time,
3584 x_timezone_id => l_phone_rec.timezone_id,
3585 x_phone_area_code => l_phone_rec.phone_area_code,
3586 x_phone_country_code => l_phone_rec.phone_country_code,
3587 x_phone_number => l_phone_rec.phone_number,
3588 x_phone_extension => l_phone_rec.phone_extension,
3589 x_phone_line_type => l_phone_rec.phone_line_type,
3590 x_telex_number => l_telex_rec.telex_number,
3591 x_web_type => l_web_rec.web_type,
3592 x_url => l_web_rec.url,
3593 x_content_source_type => p_contact_point_rec.content_source_type,
3594 x_raw_phone_number => l_phone_rec.raw_phone_number,
3595 x_object_version_number => 1,
3596 x_contact_point_purpose => p_contact_point_rec.contact_point_purpose,
3597 x_primary_by_purpose => p_contact_point_rec.primary_by_purpose,
3598 x_created_by_module => p_contact_point_rec.created_by_module,
3599 x_application_id => p_contact_point_rec.application_id,
3600 x_transposed_phone_number => l_transposed_phone_number,
3601 x_actual_content_source => p_contact_point_rec.actual_content_source
3602 );
3603
3604 x_contact_point_id := p_contact_point_rec.contact_point_id;
3605 /*
3606 per HLD,mosr record should not be created for copy case, since old osr is still active
3607 hz_orig_system_ref_pvt.create_mosr_for_merge(
3608 FND_API.G_FALSE,
3609 'HZ_CONTACT_POINTS',
3610 p_contact_point_rec.contact_point_id,
3611 x_return_status,
3612 l_msg_count,
3613 l_msg_data);
3614
3615 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3616 RAISE FND_API.G_EXC_ERROR;
3617 END IF;
3618 */
3619 END do_create_contact_point;
3620
3621 PROCEDURE do_unset_prim_contact_point (
3622 p_owner_table_name IN VARCHAR2,
3623 p_owner_table_id IN NUMBER,
3624 p_contact_point_type IN VARCHAR2,
3625 p_contact_point_id IN NUMBER
3626 ) IS
3627
3628 l_contact_point_id NUMBER;
3629
3630 BEGIN
3631
3632
3633 -- Check during insert.
3634 IF p_contact_point_id IS NULL THEN
3635 l_contact_point_id := fnd_api.g_miss_num;
3636 ELSE
3637 l_contact_point_id := p_contact_point_id;
3638 END IF;
3639
3640 UPDATE hz_contact_points
3641 SET primary_flag = 'N'
3642 WHERE owner_table_name = p_owner_table_name
3643 AND owner_table_id = p_owner_table_id
3644 AND contact_point_type = p_contact_point_type
3645 AND contact_point_id <> l_contact_point_id
3646 -- AND content_source_type = hz_party_v2pub.g_miss_content_source_type
3647 AND primary_flag = 'Y';
3648
3649
3650 END do_unset_prim_contact_point;
3651
3652
3653 PROCEDURE do_denormalize_contact_point (
3654 p_party_id IN NUMBER,
3655 p_contact_point_type IN VARCHAR2,
3656 p_url IN VARCHAR2,
3657 p_email_address IN VARCHAR2
3658 ) IS
3659 BEGIN
3660 IF p_contact_point_type = 'WEB' THEN
3661 UPDATE hz_parties
3662 SET url = p_url
3663 WHERE party_id = p_party_id;
3664 ELSIF p_contact_point_type = 'EMAIL' THEN
3665 UPDATE hz_parties
3666 SET email_address = p_email_address
3667 WHERE party_id = p_party_id;
3668 END IF;
3669 END do_denormalize_contact_point;
3670
3671 PROCEDURE do_unset_primary_by_purpose (
3672 p_owner_table_name IN VARCHAR2,
3673 p_owner_table_id IN NUMBER,
3674 p_contact_point_type IN VARCHAR2,
3675 p_contact_point_purpose IN VARCHAR2,
3676 p_contact_point_id IN NUMBER
3677 ) IS
3678
3679 l_contact_point_id NUMBER;
3680
3681 BEGIN
3682
3683
3684 -- Check during insert.
3685 IF p_contact_point_id IS NULL THEN
3686 l_contact_point_id := FND_API.G_MISS_NUM;
3687 ELSE
3688 l_contact_point_id := p_contact_point_id;
3689 END IF;
3690
3691 UPDATE hz_contact_points
3692 SET primary_by_purpose = 'N'
3693 WHERE owner_table_name = p_owner_table_name
3694 AND owner_table_id = p_owner_table_id
3695 AND contact_point_type = p_contact_point_type
3696 AND contact_point_purpose = p_contact_point_purpose
3697 AND contact_point_id <> l_contact_point_id
3698 -- AND content_source_type = hz_party_v2pub.g_miss_content_source_type
3699 AND primary_by_purpose = 'Y';
3700
3701 END do_unset_primary_by_purpose;
3702
3703 --------------------------------------
3704 -- public procedures and functions
3705 --------------------------------------
3706
3707
3708 PROCEDURE create_contact_point (
3709 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3710 p_contact_point_rec IN hz_contact_point_v2pub.contact_point_rec_type,
3711 p_edi_rec IN hz_contact_point_v2pub.edi_rec_type := g_miss_edi_rec,
3712 p_eft_rec IN hz_contact_point_v2pub.eft_rec_type := g_miss_eft_rec,
3713 p_email_rec IN hz_contact_point_v2pub.email_rec_type := g_miss_email_rec,
3714 p_phone_rec IN hz_contact_point_v2pub.phone_rec_type := g_miss_phone_rec,
3715 p_telex_rec IN hz_contact_point_v2pub.telex_rec_type := g_miss_telex_rec,
3716 p_web_rec IN hz_contact_point_v2pub.web_rec_type := g_miss_web_rec,
3717 x_contact_point_id OUT NOCOPY NUMBER,
3718 x_return_status OUT NOCOPY VARCHAR2,
3719 x_msg_count OUT NOCOPY NUMBER,
3720 x_msg_data OUT NOCOPY VARCHAR2
3721 ) IS
3722
3723 l_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type :=
3724 p_contact_point_rec;
3725 l_edi_rec hz_contact_point_v2pub.edi_rec_type := p_edi_rec;
3726 l_eft_rec hz_contact_point_v2pub.eft_rec_type := p_eft_rec;
3727 l_email_rec hz_contact_point_v2pub.email_rec_type := p_email_rec;
3728 l_phone_rec hz_contact_point_v2pub.phone_rec_type := p_phone_rec;
3729 l_telex_rec hz_contact_point_v2pub.telex_rec_type := p_telex_rec;
3730 l_web_rec hz_contact_point_v2pub.web_rec_type := p_web_rec;
3731
3732 BEGIN
3733
3734 -- Standard start of API savepoint
3735 SAVEPOINT create_contact_point;
3736
3737 -- Initialize API return status to success.
3738 x_return_status := fnd_api.g_ret_sts_success;
3739
3740 /* SSM SST Integration and Extension
3741 * For non-profile entities, the concept of select/de-select data-sources is obsoleted.
3742
3743 IF g_cpt_mixnmatch_enabled IS NULL THEN
3744 HZ_MIXNM_UTILITY.LoadDataSources(
3745 p_entity_name => 'HZ_CONTACT_POINTS',
3746 p_entity_attr_id => g_cpt_entity_attr_id,
3747 p_mixnmatch_enabled => g_cpt_mixnmatch_enabled,
3748 p_selected_datasources => g_cpt_selected_datasources );
3749 END IF;
3750 */
3751
3752 HZ_MIXNM_UTILITY.AssignDataSourceDuringCreation (
3753 p_entity_name => 'HZ_CONTACT_POINTS',
3754 p_entity_attr_id => g_cpt_entity_attr_id,
3755 p_mixnmatch_enabled => g_cpt_mixnmatch_enabled,
3756 p_selected_datasources => g_cpt_selected_datasources,
3757 p_content_source_type => l_contact_point_rec.content_source_type,
3758 p_actual_content_source => l_contact_point_rec.actual_content_source,
3759 x_is_datasource_selected => g_cpt_is_datasource_selected,
3760 x_return_status => x_return_status );
3761
3762
3763
3764 -- Call to business logic.
3765 do_create_contact_point(l_contact_point_rec,
3766 l_edi_rec,
3767 l_eft_rec,
3768 l_email_rec,
3769 l_phone_rec,
3770 l_telex_rec,
3771 l_web_rec,
3772 x_contact_point_id,
3773 x_return_status);
3774
3775 IF x_return_status = fnd_api.g_ret_sts_success THEN
3776 -- Invoke business event system.
3777 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN --10168091
3778 hz_business_event_v2pvt.create_contact_point_event(
3779 l_contact_point_rec,
3780 l_edi_rec,
3781 l_eft_rec,
3782 l_email_rec,
3783 l_phone_rec,
3784 l_telex_rec,
3785 l_web_rec);
3786 END IF;
3787 END IF;
3788
3789 -- Call to indicate contact point creation to DQM
3790 hz_dqm_sync.sync_contact_point(l_contact_point_rec.contact_point_id, 'C');
3791
3792 -- Standard call to get message count and if count is 1, get message info.
3793 fnd_msg_pub.count_and_get(
3794 p_encoded => fnd_api.g_false,
3795 p_count => x_msg_count,
3796 p_data => x_msg_data);
3797
3798
3799 EXCEPTION
3800 WHEN fnd_api.g_exc_error THEN
3801 ROLLBACK TO create_contact_point;
3802 x_return_status := fnd_api.g_ret_sts_error;
3803
3804 fnd_msg_pub.count_and_get(
3805 p_encoded => fnd_api.g_false,
3806 p_count => x_msg_count,
3807 p_data => x_msg_data);
3808
3809 WHEN fnd_api.g_exc_unexpected_error THEN
3810 ROLLBACK TO create_contact_point;
3811 x_return_status := fnd_api.g_ret_sts_unexp_error;
3812
3813 fnd_msg_pub.count_and_get(
3814 p_encoded => fnd_api.g_false,
3815 p_count => x_msg_count,
3816 p_data => x_msg_data);
3817
3818 WHEN OTHERS THEN
3819 ROLLBACK TO create_contact_point;
3820 x_return_status := fnd_api.g_ret_sts_unexp_error;
3821
3822 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
3823 fnd_message.set_token('ERROR' ,SQLERRM);
3824 fnd_msg_pub.add;
3825
3826 fnd_msg_pub.count_and_get(
3827 p_encoded => fnd_api.g_false,
3828 p_count => x_msg_count,
3829 p_data => x_msg_data);
3830
3831 END create_contact_point;
3832
3833
3834 PROCEDURE get_contact_point_rec (
3835 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3836 p_contact_point_id IN NUMBER,
3837 x_contact_point_rec OUT NOCOPY hz_contact_point_v2pub.contact_point_rec_type,
3838 x_edi_rec OUT NOCOPY hz_contact_point_v2pub.edi_rec_type,
3839 x_eft_rec OUT NOCOPY hz_contact_point_v2pub.eft_rec_type,
3840 x_email_rec OUT NOCOPY hz_contact_point_v2pub.email_rec_type,
3841 x_phone_rec OUT NOCOPY hz_contact_point_v2pub.phone_rec_type,
3842 x_telex_rec OUT NOCOPY hz_contact_point_v2pub.telex_rec_type,
3843 x_web_rec OUT NOCOPY hz_contact_point_v2pub.web_rec_type,
3844 x_return_status OUT NOCOPY VARCHAR2,
3845 x_msg_count OUT NOCOPY NUMBER,
3846 x_msg_data OUT NOCOPY VARCHAR2
3847 ) IS
3848
3849 l_transposed_phone_number hz_contact_points.transposed_phone_number%TYPE;
3850
3851 BEGIN
3852
3853 -- Initialize API return status to success.
3854 x_return_status := fnd_api.g_ret_sts_success;
3855
3856 -- Check whether primary key has been passed in.
3857 IF p_contact_point_id IS NULL OR
3858 p_contact_point_id = FND_API.G_MISS_NUM THEN
3859 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
3860 fnd_message.set_token('COLUMN', 'contact_point_id');
3861 fnd_msg_pub.add;
3862 RAISE fnd_api.g_exc_error;
3863 END IF;
3864
3865 x_contact_point_rec.contact_point_id := p_contact_point_id;
3866
3867 -- Call table-handler
3868 hz_contact_points_pkg.select_row(
3869 x_contact_point_id => x_contact_point_rec.contact_point_id,
3870 x_contact_point_type => x_contact_point_rec.contact_point_type,
3871 x_status => x_contact_point_rec.status,
3872 x_owner_table_name => x_contact_point_rec.owner_table_name,
3873 x_owner_table_id => x_contact_point_rec.owner_table_id,
3874 x_primary_flag => x_contact_point_rec.primary_flag,
3875 x_orig_system_reference => x_contact_point_rec.orig_system_reference,
3876 x_attribute_category => x_contact_point_rec.attribute_category,
3877 x_attribute1 => x_contact_point_rec.attribute1,
3878 x_attribute2 => x_contact_point_rec.attribute2,
3879 x_attribute3 => x_contact_point_rec.attribute3,
3880 x_attribute4 => x_contact_point_rec.attribute4,
3881 x_attribute5 => x_contact_point_rec.attribute5,
3882 x_attribute6 => x_contact_point_rec.attribute6,
3883 x_attribute7 => x_contact_point_rec.attribute7,
3884 x_attribute8 => x_contact_point_rec.attribute8,
3885 x_attribute9 => x_contact_point_rec.attribute9,
3886 x_attribute10 => x_contact_point_rec.attribute10,
3887 x_attribute11 => x_contact_point_rec.attribute11,
3888 x_attribute12 => x_contact_point_rec.attribute12,
3889 x_attribute13 => x_contact_point_rec.attribute13,
3890 x_attribute14 => x_contact_point_rec.attribute14,
3891 x_attribute15 => x_contact_point_rec.attribute15,
3892 x_attribute16 => x_contact_point_rec.attribute16,
3893 x_attribute17 => x_contact_point_rec.attribute17,
3894 x_attribute18 => x_contact_point_rec.attribute18,
3895 x_attribute19 => x_contact_point_rec.attribute19,
3896 x_attribute20 => x_contact_point_rec.attribute20,
3897 x_edi_transaction_handling => x_edi_rec.edi_transaction_handling,
3898 x_edi_id_number => x_edi_rec.edi_id_number,
3899 x_edi_payment_method => x_edi_rec.edi_payment_method,
3900 x_edi_payment_format => x_edi_rec.edi_payment_format,
3901 x_edi_remittance_method => x_edi_rec.edi_remittance_method,
3902 x_edi_remittance_instruction => x_edi_rec.edi_remittance_instruction,
3903 x_edi_tp_header_id => x_edi_rec.edi_tp_header_id,
3904 x_edi_ece_tp_location_code => x_edi_rec.edi_ece_tp_location_code,
3905 x_eft_transmission_program_id => x_eft_rec.eft_transmission_program_id,
3906 x_eft_printing_program_id => x_eft_rec.eft_printing_program_id,
3907 x_eft_user_number => x_eft_rec.eft_user_number,
3908 x_eft_swift_code => x_eft_rec.eft_swift_code,
3909 x_email_format => x_email_rec.email_format,
3910 x_email_address => x_email_rec.email_address,
3911 x_phone_calling_calendar => x_phone_rec.phone_calling_calendar,
3912 x_last_contact_dt_time => x_phone_rec.last_contact_dt_time,
3913 x_timezone_id => x_phone_rec.timezone_id,
3914 x_phone_area_code => x_phone_rec.phone_area_code,
3915 x_phone_country_code => x_phone_rec.phone_country_code,
3916 x_phone_number => x_phone_rec.phone_number,
3917 x_phone_extension => x_phone_rec.phone_extension,
3918 x_phone_line_type => x_phone_rec.phone_line_type,
3919 x_telex_number => x_telex_rec.telex_number,
3920 x_web_type => x_web_rec.web_type,
3921 x_url => x_web_rec.url,
3922 x_content_source_type => x_contact_point_rec.content_source_type,
3923 x_raw_phone_number => x_phone_rec.raw_phone_number,
3924 x_contact_point_purpose => x_contact_point_rec.contact_point_purpose,
3925 x_primary_by_purpose => x_contact_point_rec.primary_by_purpose,
3926 x_created_by_module => x_contact_point_rec.created_by_module,
3927 x_application_id => x_contact_point_rec.application_id,
3928 x_transposed_phone_number => l_transposed_phone_number,
3929 x_actual_content_source => x_contact_point_rec.actual_content_source
3930 );
3931
3932
3933 -- Standard call to get message count and if count is 1, get message info.
3934 fnd_msg_pub.count_and_get(
3935 p_encoded => fnd_api.g_false,
3936 p_count => x_msg_count,
3937 p_data => x_msg_data);
3938
3939 EXCEPTION
3940 WHEN fnd_api.g_exc_error THEN
3941 x_return_status := fnd_api.g_ret_sts_error;
3942
3943 fnd_msg_pub.count_and_get(
3944 p_encoded => fnd_api.g_false,
3945 p_count => x_msg_count,
3946 p_data => x_msg_data);
3947
3948 WHEN fnd_api.g_exc_unexpected_error THEN
3949 x_return_status := fnd_api.g_ret_sts_unexp_error;
3950
3951 fnd_msg_pub.count_and_get(
3952 p_encoded => fnd_api.g_false,
3953 p_count => x_msg_count,
3954 p_data => x_msg_data);
3955
3956 WHEN OTHERS THEN
3957 x_return_status := fnd_api.g_ret_sts_unexp_error;
3958
3959 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
3960 fnd_message.set_token('ERROR' ,SQLERRM);
3961 fnd_msg_pub.add;
3962
3963 fnd_msg_pub.count_and_get(
3964 p_encoded => fnd_api.g_false,
3965 p_count => x_msg_count,
3966 p_data => x_msg_data);
3967
3968 END get_contact_point_rec;
3969
3970 ------------------ PARTY_SITE_USE -------------------------------------
3971
3972
3973
3974 /*===========================================================================+
3975 | PROCEDURE
3976 | get_party_site_use_rec
3977 |
3978 | DESCRIPTION
3979 | Gets current record.
3980 |
3981 | SCOPE - PRIVATE
3982 |
3983 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3984 |
3985 | ARGUMENTS : IN:
3986 | p_init_msg_list
3987 | p_party_site_id
3988 | OUT:
3989 | x_party_site_rec
3990 | x_return_status
3991 | x_msg_count
3992 | x_msg_data
3993 | IN/ OUT:
3994 |
3995 | RETURNS : NONE
3996 |
3997 | NOTES
3998 |
3999 | MODIFICATION HISTORY
4000 |
4001 +===========================================================================*/
4002
4003 PROCEDURE get_party_site_use_rec (
4004 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4005 p_party_site_use_id IN NUMBER,
4006 x_party_site_use_rec OUT NOCOPY HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_REC_TYPE,
4007 x_return_status OUT NOCOPY VARCHAR2,
4008 x_msg_count OUT NOCOPY NUMBER,
4009 x_msg_data OUT NOCOPY VARCHAR2
4010 ) IS
4011
4012 l_api_name CONSTANT VARCHAR2(30) := 'get_party_site_rec';
4013
4014 BEGIN
4015
4016 --Initialize message list if p_init_msg_list is set to TRUE.
4017 IF FND_API.to_Boolean(p_init_msg_list) THEN
4018 FND_MSG_PUB.initialize;
4019 END IF;
4020
4021 --Initialize API return status to success.
4022 x_return_status := FND_API.G_RET_STS_SUCCESS;
4023
4024 --Check whether primary key has been passed in.
4025 IF p_party_site_use_id IS NULL OR
4026 p_party_site_use_id = FND_API.G_MISS_NUM THEN
4027 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
4028 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'p_party_site_use_id' );
4029 FND_MSG_PUB.ADD;
4030 RAISE FND_API.G_EXC_ERROR;
4031 END IF;
4032
4033 x_party_site_use_rec.party_site_use_id := p_party_site_use_id;
4034
4035 HZ_PARTY_SITE_USES_PKG.Select_Row (
4036 X_PARTY_SITE_USE_ID => x_party_site_use_rec.party_site_use_id,
4037 X_COMMENTS => x_party_site_use_rec.comments,
4038 X_PARTY_SITE_ID => x_party_site_use_rec.party_site_id,
4039 X_SITE_USE_TYPE => x_party_site_use_rec.site_use_type,
4040 X_PRIMARY_PER_TYPE => x_party_site_use_rec.primary_per_type,
4041 X_STATUS => x_party_site_use_rec.status,
4042 X_CREATED_BY_MODULE => x_party_site_use_rec.created_by_module,
4043 X_APPLICATION_ID => x_party_site_use_rec.application_id
4044 );
4045
4046 --Standard call to get message count and if count is 1, get message info.
4047 FND_MSG_PUB.Count_And_Get(
4048 p_encoded => FND_API.G_FALSE,
4049 p_count => x_msg_count,
4050 p_data => x_msg_data );
4051
4052 EXCEPTION
4053 WHEN FND_API.G_EXC_ERROR THEN
4054 x_return_status := FND_API.G_RET_STS_ERROR;
4055
4056 FND_MSG_PUB.Count_And_Get(
4057 p_encoded => FND_API.G_FALSE,
4058 p_count => x_msg_count,
4059 p_data => x_msg_data );
4060
4061 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4062 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4063
4064 FND_MSG_PUB.Count_And_Get(
4065 p_encoded => FND_API.G_FALSE,
4066 p_count => x_msg_count,
4067 p_data => x_msg_data );
4068
4069 WHEN OTHERS THEN
4070 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4071 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
4072 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
4073 FND_MSG_PUB.ADD;
4074 FND_MSG_PUB.Count_And_Get(
4075 p_encoded => FND_API.G_FALSE,
4076 p_count => x_msg_count,
4077 p_data => x_msg_data );
4078
4079 END get_party_site_use_rec;
4080
4081
4082 /*===========================================================================+
4083 | PROCEDURE
4084 | do_unmark_primary_per_type
4085 |
4086 | DESCRIPTION
4087 | unmark the primary_per_type in hz_party_site_uses
4088 | for those site uses that are not primary for
4089 | each party.
4090 |
4091 | SCOPE - PRIVATE
4092 |
4093 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
4094 |
4095 | ARGUMENTS : IN:
4096 | p_party_id
4097 | p_party_site_id
4098 | p_site_use_type
4099 | OUT:
4100 | IN/ OUT:
4101 |
4102 | RETURNS : NONE
4103 |
4104 | NOTES
4105 |
4106 | MODIFICATION HISTORY
4107 | 20-May-2004 Ramesh Ch Created.
4108 |
4109 +===========================================================================*/
4110
4111 PROCEDURE do_unmark_primary_per_type(
4112 p_party_id IN NUMBER,
4113 p_party_site_id IN NUMBER,
4114 p_site_use_type IN VARCHAR2
4115 ) IS
4116
4117 CURSOR c_party_site_uses IS
4118 SELECT ROWID
4119 FROM HZ_PARTY_SITE_USES SU
4120 WHERE SU.PARTY_SITE_ID IN (
4121 SELECT PS.PARTY_SITE_ID
4122 FROM HZ_PARTY_SITES PS
4123 WHERE PARTY_ID = p_party_id )
4124 AND SU.PARTY_SITE_ID <> p_party_site_id
4125 AND SU.SITE_USE_TYPE = p_site_use_type
4126 AND SU.PRIMARY_PER_TYPE = 'Y'
4127 AND ROWNUM = 1
4128 FOR UPDATE NOWAIT;
4129
4130 l_rowid VARCHAR2(100);
4131
4132 BEGIN
4133
4134 -- check if party site use record is locked by any one else.
4135 -- notice the combination of party_site_id and site_use_type
4136 -- is unique.
4137
4138 BEGIN
4139 OPEN c_party_site_uses;
4140 FETCH c_party_site_uses INTO l_rowid;
4141 CLOSE c_party_site_uses;
4142 EXCEPTION
4143 WHEN OTHERS THEN
4144 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
4145 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_PARTY_SITE_USES');
4146 FND_MSG_PUB.ADD;
4147 RAISE FND_API.G_EXC_ERROR;
4148 END;
4149
4150 IF l_rowid IS NOT NULL THEN
4151 UPDATE HZ_PARTY_SITE_USES
4152 SET PRIMARY_PER_TYPE = 'N',
4153 last_update_date = hz_utility_v2pub.last_update_date,
4154 last_updated_by = hz_utility_v2pub.last_updated_by,
4155 last_update_login = hz_utility_v2pub.last_update_login,
4156 request_id = hz_utility_v2pub.request_id,
4157 program_id = hz_utility_v2pub.program_id,
4158 program_application_id = hz_utility_v2pub.program_application_id,
4159 program_update_date = hz_utility_v2pub.program_update_date
4160 WHERE ROWID = l_rowid;
4161 END IF;
4162
4163 END do_unmark_primary_per_type;
4164
4165
4166 /*===========================================================================+
4167 | PROCEDURE
4168 | do_create_party_site_use
4169 |
4170 | DESCRIPTION
4171 | Creates party_site_use.
4172 |
4173 | SCOPE - PRIVATE
4174 |
4175 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
4176 |
4177 | ARGUMENTS : IN:
4178 | OUT:
4179 | x_party_site_use_id
4180 | IN/ OUT:
4181 | p_party_site_use_rec
4182 | x_return_status
4183 |
4184 | RETURNS : NONE
4185 |
4186 | NOTES
4187 |
4188 | MODIFICATION HISTORY
4189 |
4190 +===========================================================================*/
4191
4192 PROCEDURE do_create_party_site_use(
4193 p_party_site_use_rec IN OUT NOCOPY HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_REC_TYPE,
4194 x_party_site_use_id OUT NOCOPY NUMBER,
4195 x_return_status IN OUT NOCOPY VARCHAR2
4196 ) IS
4197
4198 l_party_site_use_id NUMBER := p_party_site_use_rec.party_site_use_id;
4199 l_rowid ROWID := NULL;
4200 l_count NUMBER;
4201 l_exist VARCHAR2(1) := 'N';
4202 l_party_id NUMBER;
4203 l_primary_per_type VARCHAR2(1) := p_party_site_use_rec.primary_per_type;
4204 l_msg_count NUMBER;
4205 l_msg_data VARCHAR2(2000);
4206 l_dummy VARCHAR2(1);
4207 l_debug_prefix VARCHAR2(30) := '';
4208
4209 BEGIN
4210
4211 -- if this is the first party site use per type,,
4212 -- we need to mark it with primary_per_type = 'Y'.
4213 SELECT PARTY_ID
4214 INTO l_party_id
4215 FROM HZ_PARTY_SITES
4216 WHERE PARTY_SITE_ID = p_party_site_use_rec.party_site_id;
4217
4218 IF p_party_site_use_rec.primary_per_type = 'Y' THEN --Bug No:3560167
4219 do_unmark_primary_per_type(l_party_id,p_party_site_use_rec.party_site_id,p_party_site_use_rec.site_use_type); --Bug No:3560167
4220 ELSE
4221 l_primary_per_type := 'N';
4222 BEGIN
4223 SELECT 'Y'
4224 INTO l_exist
4225 FROM HZ_PARTY_SITE_USES SU
4226 WHERE PARTY_SITE_ID IN (
4227 SELECT PARTY_SITE_ID
4228 FROM HZ_PARTY_SITES PS
4229 WHERE PS.PARTY_ID = l_party_id )
4230 AND SU.SITE_USE_TYPE = p_party_site_use_rec.site_use_type
4231 AND ROWNUM = 1;
4232
4233 EXCEPTION
4234
4235 --this is a new site use type
4236 WHEN NO_DATA_FOUND THEN
4237 l_primary_per_type := 'Y';
4238 END;
4239 p_party_site_use_rec.primary_per_type := l_primary_per_type;
4240 END IF;
4241 -- call table-handler.
4242 HZ_PARTY_SITE_USES_PKG.Insert_Row (
4243 X_PARTY_SITE_USE_ID => p_party_site_use_rec.party_site_use_id,
4244 X_COMMENTS => p_party_site_use_rec.comments,
4245 X_PARTY_SITE_ID => p_party_site_use_rec.party_site_id,
4246 X_SITE_USE_TYPE => p_party_site_use_rec.site_use_type,
4247 X_PRIMARY_PER_TYPE => p_party_site_use_rec.primary_per_type,
4248 X_STATUS => p_party_site_use_rec.status,
4249 X_OBJECT_VERSION_NUMBER => 1,
4250 X_CREATED_BY_MODULE => p_party_site_use_rec.created_by_module,
4251 X_APPLICATION_ID => p_party_site_use_rec.application_id
4252 );
4253
4254 x_party_site_use_id := p_party_site_use_rec.party_site_use_id;
4255
4256 END do_create_party_site_use;
4257
4258
4259 /*===========================================================================+
4260 | PROCEDURE
4261 | create_party_site_use
4262 |
4263 | DESCRIPTION
4264 | Creates party_site_use.
4265 |
4266 | SCOPE - PUBLIC
4267 |
4268 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
4269 |
4270 | ARGUMENTS : IN:
4271 | p_init_msg_list
4272 | p_party_site_use_rec
4273 | OUT:
4274 | x_return_status
4275 | x_msg_count
4276 | x_msg_data
4277 | x_party_site_use_id
4278 | IN/ OUT:
4279 |
4280 | RETURNS : NONE
4281 |
4282 | NOTES
4283 |
4284 | MODIFICATION HISTORY
4285 | Rashmi Goyal 31-AUG-99 Created
4286 |
4287 +===========================================================================*/
4288
4289 PROCEDURE create_party_site_use (
4290 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4291 p_party_site_use_rec IN HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_REC_TYPE,
4292 x_party_site_use_id OUT NOCOPY NUMBER,
4293 x_return_status OUT NOCOPY VARCHAR2,
4294 x_msg_count OUT NOCOPY NUMBER,
4295 x_msg_data OUT NOCOPY VARCHAR2
4296 ) IS
4297
4298 l_api_name CONSTANT VARCHAR2(30) := 'create_party_site_use';
4299 l_api_version CONSTANT NUMBER := 1.0;
4300 l_party_site_use_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_REC_TYPE := p_party_site_use_rec;
4301
4302 BEGIN
4303
4304 -- standard start of API savepoint
4305 SAVEPOINT create_party_site_use;
4306
4307 -- initialize message list if p_init_msg_list is set to TRUE.
4308 IF FND_API.to_Boolean(p_init_msg_list) THEN
4309 FND_MSG_PUB.initialize;
4310 END IF;
4311
4312 -- initialize API return status to success.
4313 x_return_status := FND_API.G_RET_STS_SUCCESS;
4314
4315 -- call to business logic.
4316 do_create_party_site_use(
4317 l_party_site_use_rec,
4318 x_party_site_use_id,
4319 x_return_status
4320 );
4321
4322 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
4323 -- Invoke business event system.
4324 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN --10168091
4325 HZ_BUSINESS_EVENT_V2PVT.create_party_site_use_event (
4326 l_party_site_use_rec );
4327 END IF;
4328 END IF;
4329
4330 -- standard call to get message count and if count is 1, get message info.
4331 FND_MSG_PUB.Count_And_Get(
4332 p_encoded => FND_API.G_FALSE,
4333 p_count => x_msg_count,
4334 p_data => x_msg_data);
4335
4336
4337 EXCEPTION
4338 WHEN FND_API.G_EXC_ERROR THEN
4339 ROLLBACK TO create_party_site_use;
4340 x_return_status := FND_API.G_RET_STS_ERROR;
4341 FND_MSG_PUB.Count_And_Get(
4342 p_encoded => FND_API.G_FALSE,
4343 p_count => x_msg_count,
4344 p_data => x_msg_data);
4345
4346 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4347 ROLLBACK TO create_party_site_use;
4348 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4349 FND_MSG_PUB.Count_And_Get(
4350 p_encoded => FND_API.G_FALSE,
4351 p_count => x_msg_count,
4352 p_data => x_msg_data);
4353
4354 WHEN OTHERS THEN
4355 ROLLBACK TO create_party_site_use;
4356 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4357 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
4358 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4359 FND_MSG_PUB.ADD;
4360 FND_MSG_PUB.Count_And_Get(
4361 p_encoded => FND_API.G_FALSE,
4362 p_count => x_msg_count,
4363 p_data => x_msg_data);
4364
4365
4366 END create_party_site_use;
4367
4368 END hz_cust_account_merge_v2pvt;