[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.16 2006/05/16 10:51:04 ansingha noship $ */
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 HZ_BUSINESS_EVENT_V2PVT.create_relationship_event (
1169 l_rel_rec,
1170 l_created_party );
1171 END IF;
1172
1173 -- Standard call to get message count and if count is 1, get message info.
1174 FND_MSG_PUB.Count_And_Get(
1175 p_encoded => FND_API.G_FALSE,
1176 p_count => x_msg_count,
1177 p_data => x_msg_data);
1178
1179 EXCEPTION
1180 WHEN FND_API.G_EXC_ERROR THEN
1181 ROLLBACK TO create_relationship;
1182 x_return_status := FND_API.G_RET_STS_ERROR;
1183 FND_MSG_PUB.Count_And_Get(
1184 p_encoded => FND_API.G_FALSE,
1185 p_count => x_msg_count,
1186 p_data => x_msg_data);
1187
1188
1189 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1190 ROLLBACK TO create_relationship;
1191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1192 FND_MSG_PUB.Count_And_Get(
1193 p_encoded => FND_API.G_FALSE,
1194 p_count => x_msg_count,
1195 p_data => x_msg_data);
1196
1197 WHEN OTHERS THEN
1198 ROLLBACK TO create_relationship;
1199 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1200 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1201 fnd_message.set_token('ERROR' ,SQLERRM);
1202 fnd_msg_pub.add;
1203 FND_MSG_PUB.Count_And_Get(
1204 p_encoded => FND_API.G_FALSE,
1205 p_count => x_msg_count,
1206 p_data => x_msg_data);
1207
1208 END create_relationship;
1209
1210 PROCEDURE get_relationship_rec (
1211 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1212 p_relationship_id IN NUMBER,
1213 p_directional_flag IN VARCHAR2 := 'F',
1214 x_rel_rec OUT NOCOPY HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE,
1215 x_direction_code OUT NOCOPY VARCHAR2,
1216 x_return_status OUT NOCOPY VARCHAR2,
1217 x_msg_count OUT NOCOPY NUMBER,
1218 x_msg_data OUT NOCOPY VARCHAR2
1219 ) IS
1220
1221 l_party_id NUMBER;
1222 l_directional_flag VARCHAR2(1);
1223 l_direction_code VARCHAR2(255);
1224
1225 BEGIN
1226
1227 --Initialize message list if p_init_msg_list is set to TRUE.
1228 IF FND_API.to_Boolean(p_init_msg_list) THEN
1229 FND_MSG_PUB.initialize;
1230 END IF;
1231
1232 --Initialize API return status to success.
1233 x_return_status := FND_API.G_RET_STS_SUCCESS;
1234
1235 --Check whether primary key has been passed in.
1236 IF p_relationship_id IS NULL OR
1237 p_relationship_id = FND_API.G_MISS_NUM THEN
1238 fnd_message.set_name( 'AR', 'HZ_API_MISSING_COLUMN' );
1239 fnd_message.set_token( 'COLUMN', 'relationship_id' );
1240 fnd_msg_pub.add;
1241 RAISE FND_API.G_EXC_ERROR;
1242 END IF;
1243
1244 x_rel_rec.relationship_id := p_relationship_id;
1245 IF p_directional_flag <> 'F'
1246 AND
1247 p_directional_flag <> 'B'
1248 THEN
1249 l_directional_flag := 'F';
1250 ELSE
1251 l_directional_flag := NVL(p_directional_flag, 'F');
1252 END IF;
1253
1254 HZ_RELATIONSHIPS_PKG.Select_Row (
1255 X_RELATIONSHIP_ID => x_rel_rec.relationship_id,
1256 X_DIRECTIONAL_FLAG => l_directional_flag,
1257 X_SUBJECT_ID => x_rel_rec.subject_id,
1258 X_SUBJECT_TYPE => x_rel_rec.subject_type,
1259 X_SUBJECT_TABLE_NAME => x_rel_rec.subject_table_name,
1260 X_OBJECT_ID => x_rel_rec.object_id,
1261 X_OBJECT_TYPE => x_rel_rec.object_type,
1262 X_OBJECT_TABLE_NAME => x_rel_rec.object_table_name,
1263 X_PARTY_ID => l_party_id,
1264 X_RELATIONSHIP_CODE => x_rel_rec.relationship_code,
1265 X_COMMENTS => x_rel_rec.comments,
1266 X_START_DATE => x_rel_rec.start_date,
1267 X_END_DATE => x_rel_rec.end_date,
1268 X_STATUS => x_rel_rec.status,
1269 X_ATTRIBUTE_CATEGORY => x_rel_rec.attribute_category,
1270 X_ATTRIBUTE1 => x_rel_rec.attribute1,
1271 X_ATTRIBUTE2 => x_rel_rec.attribute2,
1272 X_ATTRIBUTE3 => x_rel_rec.attribute3,
1273 X_ATTRIBUTE4 => x_rel_rec.attribute4,
1274 X_ATTRIBUTE5 => x_rel_rec.attribute5,
1275 X_ATTRIBUTE6 => x_rel_rec.attribute6,
1276 X_ATTRIBUTE7 => x_rel_rec.attribute7,
1277 X_ATTRIBUTE8 => x_rel_rec.attribute8,
1278 X_ATTRIBUTE9 => x_rel_rec.attribute9,
1279 X_ATTRIBUTE10 => x_rel_rec.attribute10,
1280 X_ATTRIBUTE11 => x_rel_rec.attribute11,
1281 X_ATTRIBUTE12 => x_rel_rec.attribute12,
1282 X_ATTRIBUTE13 => x_rel_rec.attribute13,
1283 X_ATTRIBUTE14 => x_rel_rec.attribute14,
1284 X_ATTRIBUTE15 => x_rel_rec.attribute15,
1285 X_ATTRIBUTE16 => x_rel_rec.attribute16,
1286 X_ATTRIBUTE17 => x_rel_rec.attribute17,
1287 X_ATTRIBUTE18 => x_rel_rec.attribute18,
1288 X_ATTRIBUTE19 => x_rel_rec.attribute19,
1289 X_ATTRIBUTE20 => x_rel_rec.attribute20,
1290 X_CONTENT_SOURCE_TYPE => x_rel_rec.content_source_type,
1291 X_RELATIONSHIP_TYPE => x_rel_rec.relationship_type,
1292 X_CREATED_BY_MODULE => x_rel_rec.created_by_module,
1293 X_APPLICATION_ID => x_rel_rec.application_id,
1294 X_ADDITIONAL_INFORMATION1 => x_rel_rec.additional_information1,
1295 X_ADDITIONAL_INFORMATION2 => x_rel_rec.additional_information2,
1296 X_ADDITIONAL_INFORMATION3 => x_rel_rec.additional_information3,
1297 X_ADDITIONAL_INFORMATION4 => x_rel_rec.additional_information4,
1298 X_ADDITIONAL_INFORMATION5 => x_rel_rec.additional_information5,
1299 X_ADDITIONAL_INFORMATION6 => x_rel_rec.additional_information6,
1300 X_ADDITIONAL_INFORMATION7 => x_rel_rec.additional_information7,
1301 X_ADDITIONAL_INFORMATION8 => x_rel_rec.additional_information8,
1302 X_ADDITIONAL_INFORMATION9 => x_rel_rec.additional_information9,
1303 X_ADDITIONAL_INFORMATION10 => x_rel_rec.additional_information10,
1304 X_ADDITIONAL_INFORMATION11 => x_rel_rec.additional_information11,
1305 X_ADDITIONAL_INFORMATION12 => x_rel_rec.additional_information12,
1306 X_ADDITIONAL_INFORMATION13 => x_rel_rec.additional_information13,
1307 X_ADDITIONAL_INFORMATION14 => x_rel_rec.additional_information14,
1308 X_ADDITIONAL_INFORMATION15 => x_rel_rec.additional_information15,
1309 X_ADDITIONAL_INFORMATION16 => x_rel_rec.additional_information16,
1310 X_ADDITIONAL_INFORMATION17 => x_rel_rec.additional_information17,
1311 X_ADDITIONAL_INFORMATION18 => x_rel_rec.additional_information18,
1312 X_ADDITIONAL_INFORMATION19 => x_rel_rec.additional_information19,
1313 X_ADDITIONAL_INFORMATION20 => x_rel_rec.additional_information20,
1314 X_ADDITIONAL_INFORMATION21 => x_rel_rec.additional_information21,
1315 X_ADDITIONAL_INFORMATION22 => x_rel_rec.additional_information22,
1316 X_ADDITIONAL_INFORMATION23 => x_rel_rec.additional_information23,
1317 x_ADDITIONAL_INFORMATION24 => x_rel_rec.additional_information24,
1318 X_ADDITIONAL_INFORMATION25 => x_rel_rec.additional_information25,
1319 X_ADDITIONAL_INFORMATION26 => x_rel_rec.additional_information26,
1320 X_ADDITIONAL_INFORMATION27 => x_rel_rec.additional_information27,
1321 X_ADDITIONAL_INFORMATION28 => x_rel_rec.additional_information28,
1322 X_ADDITIONAL_INFORMATION29 => x_rel_rec.additional_information29,
1323 X_ADDITIONAL_INFORMATION30 => x_rel_rec.additional_information30,
1324 X_DIRECTION_CODE => x_direction_code,
1325 X_PERCENTAGE_OWNERSHIP => x_rel_rec.percentage_ownership,
1326 X_ACTUAL_CONTENT_SOURCE => x_rel_rec.ACTUAL_CONTENT_SOURCE
1327
1328 );
1329
1330 IF l_party_id IS NOT NULL
1331 AND
1332 l_party_id <> FND_API.G_MISS_NUM
1333 THEN
1334 get_party_rec (
1335 p_party_id => l_party_id,
1336 x_party_rec => x_rel_rec.party_rec,
1337 x_return_status => x_return_status,
1338 x_msg_count => x_msg_count,
1339 x_msg_data => x_msg_data
1340 );
1341
1342 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1343 RAISE FND_API.G_EXC_ERROR;
1344 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1345 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1346 END IF;
1347 END IF;
1348
1349 --Standard call to get message count and if count is 1, get message info.
1350 FND_MSG_PUB.Count_And_Get(
1351 p_encoded => FND_API.G_FALSE,
1352 p_count => x_msg_count,
1353 p_data => x_msg_data );
1354
1355 EXCEPTION
1356 WHEN FND_API.G_EXC_ERROR THEN
1357 x_return_status := FND_API.G_RET_STS_ERROR;
1358
1359 FND_MSG_PUB.Count_And_Get(
1360 p_encoded => FND_API.G_FALSE,
1361 p_count => x_msg_count,
1362 p_data => x_msg_data );
1363
1364 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1365 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1366
1367 FND_MSG_PUB.Count_And_Get(
1368 p_encoded => FND_API.G_FALSE,
1369 p_count => x_msg_count,
1370 p_data => x_msg_data );
1371
1372 WHEN OTHERS THEN
1373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1374
1375 fnd_message.set_name( 'AR', 'HZ_API_OTHERS_EXCEP' );
1376 fnd_message.set_token( 'ERROR' ,SQLERRM );
1377 fnd_msg_pub.add;
1378
1379 FND_MSG_PUB.Count_And_Get(
1380 p_encoded => FND_API.G_FALSE,
1381 p_count => x_msg_count,
1382 p_data => x_msg_data );
1383
1384
1385 END get_relationship_rec;
1386
1387 --------------------PARTY_CONTACT--------------------------------------------
1388
1389 ------------------------------------
1390 -- declaration of private procedures
1391 ------------------------------------
1392
1393
1394 PROCEDURE do_create_org_contact(
1395 p_org_contact_rec IN OUT NOCOPY HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE,
1396 p_direction_code IN VARCHAR2,
1397 x_return_status IN OUT NOCOPY VARCHAR2,
1398 x_org_contact_id OUT NOCOPY NUMBER,
1399 x_party_rel_id OUT NOCOPY NUMBER,
1400 x_party_id OUT NOCOPY NUMBER,
1401 x_party_number OUT NOCOPY VARCHAR2
1402 ) IS
1403
1404 l_org_contact_id NUMBER := p_org_contact_rec.org_contact_id;
1405 l_rowid ROWID := NULL;
1406 l_count NUMBER;
1407 l_gen_contact_number VARCHAR2(1);
1408 l_contact_number VARCHAR2(30) := p_org_contact_rec.contact_number;
1409 l_msg_count NUMBER;
1410 l_msg_data VARCHAR2(2000);
1411 l_dummy VARCHAR2(1);
1412 l_debug_prefix VARCHAR2(30);
1413 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
1414 BEGIN
1415
1416 --Initialize the created by module
1417 p_org_contact_rec.created_by_module := 'HZ_TCA_CUSTOMER_MERGE';
1418 p_org_contact_rec.orig_system_reference := null;
1419
1420 ---Set the contact_number to null so that it is generated
1421 p_org_contact_rec.contact_number := null;
1422 p_org_contact_rec.org_contact_id := null;
1423
1424 --- Retain the application ID of the org contact rec in party reln rec and party rec
1425 p_org_contact_rec.party_rel_rec.application_id := p_org_contact_rec.application_id;
1426
1427 p_org_contact_rec.party_rel_rec.party_rec.party_number := null;
1428
1429 --
1430 -- create party relationship.
1431 --
1432 create_relationship (
1433 p_relationship_rec => p_org_contact_rec.party_rel_rec,
1434 p_direction_code => p_direction_code,
1435 x_relationship_id => x_party_rel_id,
1436 x_party_id => x_party_id,
1437 x_party_number => x_party_number,
1438 x_return_status => x_return_status,
1439 x_msg_count => l_msg_count,
1440 x_msg_data => l_msg_data
1441 );
1442
1443
1444 p_org_contact_rec.party_rel_rec.party_rec.party_id := x_party_id;
1445 p_org_contact_rec.party_rel_rec.party_rec.party_number := x_party_number;
1446 p_org_contact_rec.party_rel_rec.relationship_id := x_party_rel_id;
1447
1448 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1449 RAISE FND_API.G_EXC_ERROR;
1450 END IF;
1451
1452 -- call table-handler.
1453 HZ_ORG_CONTACTS_PKG.Insert_Row (
1454 X_ORG_CONTACT_ID => p_org_contact_rec.org_contact_id,
1455 X_PARTY_RELATIONSHIP_ID => x_party_rel_id,
1456 X_COMMENTS => p_org_contact_rec.comments,
1457 X_CONTACT_NUMBER => l_contact_number,
1458 X_DEPARTMENT_CODE => p_org_contact_rec.department_code,
1459 X_DEPARTMENT => p_org_contact_rec.department,
1460 X_TITLE => p_org_contact_rec.title,
1461 X_JOB_TITLE => p_org_contact_rec.job_title,
1462 X_DECISION_MAKER_FLAG => p_org_contact_rec.decision_maker_flag,
1463 X_JOB_TITLE_CODE => p_org_contact_rec.job_title_code,
1464 X_REFERENCE_USE_FLAG => p_org_contact_rec.reference_use_flag,
1465 X_RANK => p_org_contact_rec.rank,
1466 X_ORIG_SYSTEM_REFERENCE => p_org_contact_rec.orig_system_reference,
1467 X_ATTRIBUTE_CATEGORY => p_org_contact_rec.attribute_category,
1468 X_ATTRIBUTE1 => p_org_contact_rec.attribute1,
1469 X_ATTRIBUTE2 => p_org_contact_rec.attribute2,
1470 X_ATTRIBUTE3 => p_org_contact_rec.attribute3,
1471 X_ATTRIBUTE4 => p_org_contact_rec.attribute4,
1472 X_ATTRIBUTE5 => p_org_contact_rec.attribute5,
1473 X_ATTRIBUTE6 => p_org_contact_rec.attribute6,
1474 X_ATTRIBUTE7 => p_org_contact_rec.attribute7,
1475 X_ATTRIBUTE8 => p_org_contact_rec.attribute8,
1476 X_ATTRIBUTE9 => p_org_contact_rec.attribute9,
1477 X_ATTRIBUTE10 => p_org_contact_rec.attribute10,
1478 X_ATTRIBUTE11 => p_org_contact_rec.attribute11,
1479 X_ATTRIBUTE12 => p_org_contact_rec.attribute12,
1480 X_ATTRIBUTE13 => p_org_contact_rec.attribute13,
1481 X_ATTRIBUTE14 => p_org_contact_rec.attribute14,
1482 X_ATTRIBUTE15 => p_org_contact_rec.attribute15,
1483 X_ATTRIBUTE16 => p_org_contact_rec.attribute16,
1484 X_ATTRIBUTE17 => p_org_contact_rec.attribute17,
1485 X_ATTRIBUTE18 => p_org_contact_rec.attribute18,
1486 X_ATTRIBUTE19 => p_org_contact_rec.attribute19,
1487 X_ATTRIBUTE20 => p_org_contact_rec.attribute20,
1488 X_ATTRIBUTE21 => p_org_contact_rec.attribute21,
1489 X_ATTRIBUTE22 => p_org_contact_rec.attribute22,
1490 X_ATTRIBUTE23 => p_org_contact_rec.attribute23,
1491 X_ATTRIBUTE24 => p_org_contact_rec.attribute24,
1492 X_PARTY_SITE_ID => p_org_contact_rec.party_site_id,
1493 X_OBJECT_VERSION_NUMBER => 1,
1494 X_CREATED_BY_MODULE => p_org_contact_rec.created_by_module,
1495 X_APPLICATION_ID => p_org_contact_rec.application_id,
1496 X_STATUS => p_org_contact_rec.party_rel_rec.status
1497 );
1498 /*
1499 per HLD,mosr record should not be created for copy case, since old osr is still active
1500 hz_orig_system_ref_pvt.create_mosr_for_merge(
1501 FND_API.G_FALSE,
1502 'HZ_ORG_CONTACTS',
1503 p_org_contact_rec.org_contact_id,
1504 x_return_status,
1505 l_msg_count,
1506 l_msg_data);
1507
1508 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1509 RAISE FND_API.G_EXC_ERROR;
1510 END IF;
1511 */
1512 x_org_contact_id := p_org_contact_rec.org_contact_id;
1513 END do_create_org_contact;
1514
1515 PROCEDURE create_org_contact (
1516 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
1517 p_org_contact_rec IN HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE,
1518 p_direction_code IN VARCHAR2,
1519 x_org_contact_id OUT NOCOPY NUMBER,
1520 x_party_rel_id OUT NOCOPY NUMBER,
1521 x_party_id OUT NOCOPY NUMBER,
1522 x_party_number OUT NOCOPY VARCHAR2,
1523 x_return_status OUT NOCOPY VARCHAR2,
1524 x_msg_count OUT NOCOPY NUMBER,
1525 x_msg_data OUT NOCOPY VARCHAR2
1526 ) IS
1527
1528 l_api_name CONSTANT VARCHAR2(30) := 'create_org_contact';
1529 l_api_version CONSTANT NUMBER := 1.0;
1530 l_org_contact_rec HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE := p_org_contact_rec;
1531
1532 BEGIN
1533
1534 -- standard start of API savepoint
1535 SAVEPOINT create_org_contact;
1536
1537 -- initialize API return status to success.
1538 x_return_status := FND_API.G_RET_STS_SUCCESS;
1539
1540 -- call to business logic.
1541 do_create_org_contact(
1542 l_org_contact_rec,
1543 p_direction_code,
1544 x_return_status,
1545 x_org_contact_id,
1546 x_party_rel_id,
1547 x_party_id,
1548 x_party_number
1549 );
1550
1551 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1552 -- Invoke business event system.
1553 HZ_BUSINESS_EVENT_V2PVT.create_org_contact_event (
1554 l_org_contact_rec );
1555 END IF;
1556
1557 -- Call to indicate Org Contact creation to DQM
1558 HZ_DQM_SYNC.sync_contact(l_org_contact_rec.org_contact_id, 'C');
1559
1560 -- standard call to get message count and if count is 1, get message info.
1561 FND_MSG_PUB.Count_And_Get(
1562 p_encoded => FND_API.G_FALSE,
1563 p_count => x_msg_count,
1564 p_data => x_msg_data);
1565
1566
1567 EXCEPTION
1568 WHEN FND_API.G_EXC_ERROR THEN
1569 ROLLBACK TO create_org_contact;
1570 x_return_status := FND_API.G_RET_STS_ERROR;
1571 FND_MSG_PUB.Count_And_Get(
1572 p_encoded => FND_API.G_FALSE,
1573 p_count => x_msg_count,
1574 p_data => x_msg_data);
1575
1576
1577 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1578 ROLLBACK TO create_org_contact;
1579 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1580 FND_MSG_PUB.Count_And_Get(
1581 p_encoded => FND_API.G_FALSE,
1582 p_count => x_msg_count,
1583 p_data => x_msg_data);
1584
1585
1586 WHEN OTHERS THEN
1587 ROLLBACK TO create_org_contact;
1588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1589 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1590 fnd_message.set_token('ERROR' ,SQLERRM);
1591 fnd_msg_pub.add;
1592 FND_MSG_PUB.Count_And_Get(
1593 p_encoded => FND_API.G_FALSE,
1594 p_count => x_msg_count,
1595 p_data => x_msg_data);
1596
1597 END create_org_contact;
1598
1599
1600 PROCEDURE get_org_contact_rec (
1601 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1602 p_org_contact_id IN NUMBER,
1603 x_org_contact_rec OUT NOCOPY HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE,
1604 x_direction_code OUT NOCOPY VARCHAR2,
1605 x_return_status OUT NOCOPY VARCHAR2,
1606 x_msg_count OUT NOCOPY NUMBER,
1607 x_msg_data OUT NOCOPY VARCHAR2
1608 ) IS
1609
1610 l_api_name CONSTANT VARCHAR2(30) := 'get_org_contact_rec';
1611 l_api_version CONSTANT NUMBER := 1.0;
1612 l_party_relationship_id NUMBER;
1613
1614 BEGIN
1615
1616 --Initialize message list if p_init_msg_list is set to TRUE.
1617 IF FND_API.to_Boolean(p_init_msg_list) THEN
1618 FND_MSG_PUB.initialize;
1619 END IF;
1620
1621 --Initialize API return status to success.
1622 x_return_status := FND_API.G_RET_STS_SUCCESS;
1623 --Check whether primary key has been passed in.
1624 IF p_org_contact_id IS NULL OR
1625 p_org_contact_id = FND_API.G_MISS_NUM THEN
1626 fnd_message.set_name( 'AR', 'HZ_API_MISSING_COLUMN' );
1627 fnd_message.set_token( 'COLUMN', 'org_contact_id' );
1628 fnd_msg_pub.add;
1629 RAISE FND_API.G_EXC_ERROR;
1630 END IF;
1631
1632 x_org_contact_rec.org_contact_id := p_org_contact_id;
1633
1634 HZ_ORG_CONTACTS_PKG.Select_Row (
1635 X_ORG_CONTACT_ID => x_org_contact_rec.org_contact_id,
1636 X_PARTY_RELATIONSHIP_ID => l_party_relationship_id,
1637 X_COMMENTS => x_org_contact_rec.comments,
1638 X_CONTACT_NUMBER => x_org_contact_rec.contact_number,
1639 X_DEPARTMENT_CODE => x_org_contact_rec.department_code,
1640 X_DEPARTMENT => x_org_contact_rec.department,
1641 X_TITLE => x_org_contact_rec.title,
1642 X_JOB_TITLE => x_org_contact_rec.job_title,
1643 X_DECISION_MAKER_FLAG => x_org_contact_rec.decision_maker_flag,
1644 X_JOB_TITLE_CODE => x_org_contact_rec.job_title_code,
1645 X_REFERENCE_USE_FLAG => x_org_contact_rec.reference_use_flag,
1646 X_RANK => x_org_contact_rec.rank,
1647 X_ORIG_SYSTEM_REFERENCE => x_org_contact_rec.orig_system_reference,
1648 X_ATTRIBUTE_CATEGORY => x_org_contact_rec.attribute_category,
1649 X_ATTRIBUTE1 => x_org_contact_rec.attribute1,
1650 X_ATTRIBUTE2 => x_org_contact_rec.attribute2,
1651 X_ATTRIBUTE3 => x_org_contact_rec.attribute3,
1652 X_ATTRIBUTE4 => x_org_contact_rec.attribute4,
1653 X_ATTRIBUTE5 => x_org_contact_rec.attribute5,
1654 X_ATTRIBUTE6 => x_org_contact_rec.attribute6,
1655 X_ATTRIBUTE7 => x_org_contact_rec.attribute7,
1656 X_ATTRIBUTE8 => x_org_contact_rec.attribute8,
1657 X_ATTRIBUTE9 => x_org_contact_rec.attribute9,
1658 X_ATTRIBUTE10 => x_org_contact_rec.attribute10,
1659 X_ATTRIBUTE11 => x_org_contact_rec.attribute11,
1660 X_ATTRIBUTE12 => x_org_contact_rec.attribute12,
1661 X_ATTRIBUTE13 => x_org_contact_rec.attribute13,
1662 X_ATTRIBUTE14 => x_org_contact_rec.attribute14,
1663 X_ATTRIBUTE15 => x_org_contact_rec.attribute15,
1664 X_ATTRIBUTE16 => x_org_contact_rec.attribute16,
1665 X_ATTRIBUTE17 => x_org_contact_rec.attribute17,
1666 X_ATTRIBUTE18 => x_org_contact_rec.attribute18,
1667 X_ATTRIBUTE19 => x_org_contact_rec.attribute19,
1668 X_ATTRIBUTE20 => x_org_contact_rec.attribute20,
1669 X_ATTRIBUTE21 => x_org_contact_rec.attribute21,
1670 X_ATTRIBUTE22 => x_org_contact_rec.attribute22,
1671 X_ATTRIBUTE23 => x_org_contact_rec.attribute23,
1672 X_ATTRIBUTE24 => x_org_contact_rec.attribute24,
1673 X_PARTY_SITE_ID => x_org_contact_rec.party_site_id,
1674 X_CREATED_BY_MODULE => x_org_contact_rec.created_by_module,
1675 X_APPLICATION_ID => x_org_contact_rec.application_id
1676 );
1677
1678
1679 IF l_party_relationship_id IS NOT NULL
1680 AND
1681 l_party_relationship_id <> FND_API.G_MISS_NUM
1682 THEN
1683 get_relationship_rec (
1684 p_relationship_id => l_party_relationship_id,
1685 p_directional_flag => 'F',
1686 x_rel_rec => x_org_contact_rec.party_rel_rec,
1687 x_direction_code => x_direction_code,
1688 x_return_status => x_return_status,
1689 x_msg_count => x_msg_count,
1690 x_msg_data => x_msg_data
1691 );
1692
1693 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1694 RAISE FND_API.G_EXC_ERROR;
1695 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1696 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1697 END IF;
1698 END IF;
1699
1700 --Standard call to get message count and if count is 1, get message info.
1701 FND_MSG_PUB.Count_And_Get(
1702 p_encoded => FND_API.G_FALSE,
1703 p_count => x_msg_count,
1704 p_data => x_msg_data );
1705
1706 EXCEPTION
1707 WHEN FND_API.G_EXC_ERROR THEN
1708 x_return_status := FND_API.G_RET_STS_ERROR;
1709
1710 FND_MSG_PUB.Count_And_Get(
1711 p_encoded => FND_API.G_FALSE,
1712 p_count => x_msg_count,
1713 p_data => x_msg_data );
1714
1715 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1716 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1717
1718 FND_MSG_PUB.Count_And_Get(
1719 p_encoded => FND_API.G_FALSE,
1720 p_count => x_msg_count,
1721 p_data => x_msg_data );
1722
1723 WHEN OTHERS THEN
1724 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1725 fnd_message.set_name( 'AR', 'HZ_API_OTHERS_EXCEP' );
1726 fnd_message.set_token( 'ERROR' ,SQLERRM );
1727 fnd_msg_pub.add;
1728 FND_MSG_PUB.Count_And_Get(
1729 p_encoded => FND_API.G_FALSE,
1730 p_count => x_msg_count,
1731 p_data => x_msg_data );
1732
1733 END get_org_contact_rec;
1734
1735 --------------------PARTY_SITE--------------------------------------------------
1736
1737 --------------------------------------------------
1738 -- declaration of private procedures and functions
1739 --------------------------------------------------
1740
1741
1742 PROCEDURE do_create_party_site (
1743 p_party_site_rec IN OUT NOCOPY HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE,
1744 p_actual_cont_source IN VARCHAR2,
1745 x_party_site_id OUT NOCOPY NUMBER,
1746 x_party_site_number OUT NOCOPY VARCHAR2,
1747 x_return_status IN OUT NOCOPY VARCHAR2
1748 );
1749
1750 PROCEDURE do_update_address(
1751 p_party_id IN NUMBER,
1752 p_location_id IN NUMBER
1753 );
1754
1755 PROCEDURE do_unmark_address_flag(
1756 p_party_id IN NUMBER,
1757 p_party_site_id IN NUMBER := NULL
1758 );
1759
1760
1761 -----------------------------
1762 -- body of private procedures
1763 -----------------------------
1764
1765
1766 PROCEDURE do_create_party_site(
1767 p_party_site_rec IN OUT NOCOPY HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE,
1768 p_actual_cont_source IN VARCHAR2,
1769 x_party_site_id OUT NOCOPY NUMBER,
1770 x_party_site_number OUT NOCOPY VARCHAR2,
1771 x_return_status IN OUT NOCOPY VARCHAR2
1772 ) IS
1773
1774 l_party_site_id NUMBER := p_party_site_rec.party_site_id;
1775 l_party_site_number VARCHAR2(30) := p_party_site_rec.party_site_number;
1776 l_gen_party_site_number VARCHAR2(1);
1777 l_rowid ROWID := NULL;
1778 l_count NUMBER;
1779 l_exist VARCHAR2(1) := 'N';
1780 l_msg_count NUMBER;
1781 l_msg_data VARCHAR2(2000);
1782 l_dummy VARCHAR2(1);
1783 l_debug_prefix VARCHAR2(30) := '';
1784
1785 -- Bug 2197181
1786 l_loc_actual_content_source hz_locations.actual_content_source%TYPE;
1787 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
1788 BEGIN
1789
1790 --Initialize the created by module
1791 p_party_site_rec.created_by_module := 'HZ_TCA_CUSTOMER_MERGE';
1792
1793 p_party_site_rec.orig_system_reference := null;
1794 p_party_site_rec.party_site_number := null;
1795 p_party_site_rec.party_site_id := null;
1796
1797 -- Bug 2197181
1798 select actual_content_source
1799 into l_loc_actual_content_source
1800 from hz_locations
1801 where location_id = p_party_site_rec.location_id;
1802
1803 /* SSM SST Integration and Extension
1804 * For non-profile entities, the concept of select/de-select data-sources is obsoleted.
1805 * There is no need to check if the data-source is selected.
1806
1807 g_pst_is_datasource_selected :=
1808 HZ_MIXNM_UTILITY.isDataSourceSelected (
1809 p_selected_datasources => g_pst_selected_datasources,
1810 p_actual_content_source => l_loc_actual_content_source );
1811 */
1812 -- if this is the first active, visible party site,
1813 -- we need to mark it with identifying flag = 'Y'.
1814
1815 BEGIN
1816 -- Bug 2197181: Added the checking if the party site is visible
1817 -- or not. The identifying address should be visible.
1818
1819 -- SSM SST Integration and Extension
1820 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
1821 -- There is no need to check if the data-source is selected.
1822
1823 SELECT 'Y' INTO l_dummy
1824 FROM HZ_PARTY_SITES
1825 WHERE PARTY_ID = p_party_site_rec.party_id
1826 AND STATUS = 'A'
1827 /* AND HZ_MIXNM_UTILITY.isDataSourceSelected (
1828 g_pst_selected_datasources, actual_content_source ) = 'Y'*/
1829 AND ROWNUM = 1;
1830
1831 -- no exception raise, means 'a primary party site exist'
1832 -- if the current party site is to be identifying, then unmark
1833 -- the previous party sites with identifying flag = 'Y'.
1834
1835 -- Bug 2197181: added for mix-n-match project: the identifying_flag
1836 -- can be set to 'Y' only if the party site will be visible. If it
1837 -- is not visible, the flag must be reset to 'N'.
1838
1839 -- SSM SST Integration and Extension
1840 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
1841 -- There is no need to check if the data-source is selected.
1842
1843 IF p_party_site_rec.identifying_address_flag = 'Y' /*AND
1844 g_pst_is_datasource_selected = 'Y'*/
1845 THEN
1846 do_unmark_address_flag(p_party_site_rec.party_id);
1847 ELSE
1848 p_party_site_rec.identifying_address_flag := 'N';
1849 END IF;
1850
1851 EXCEPTION
1852 WHEN NO_DATA_FOUND THEN
1853 -- this is the first visible, active address, so this will be
1854 -- set as identifying address.
1855
1856 -- Bug 2197181: added for mix-n-match project: the identifying_flag
1857 -- can be set to 'Y' only if the party site will be visible. If it i
1858 -- not visible, the flag must be reset to 'N'.
1859
1860 -- SSM SST Integration and Extension
1861 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
1862 -- There is no need to check if the data-source is selected.
1863
1864 IF (NVL(p_party_site_rec.status, 'A') = 'A' OR
1865 p_party_site_rec.status = FND_API.G_MISS_CHAR)/* AND
1866 g_pst_is_datasource_selected = 'Y'*/
1867 THEN
1868 p_party_site_rec.identifying_address_flag := 'Y';
1869 ELSE
1870 p_party_site_rec.identifying_address_flag := 'N';
1871 END IF;
1872 END;
1873
1874 --denormalize primary address
1875 IF p_party_site_rec.identifying_address_flag = 'Y' THEN
1876 IF p_party_site_rec.party_id <> -1 THEN
1877 do_update_address(
1878 p_party_site_rec.party_id,
1879 p_party_site_rec.location_id);
1880 END IF;
1881
1882 END IF;
1883
1884
1885 p_party_site_rec.party_site_id := l_party_site_id;
1886 p_party_site_rec.party_site_number := l_party_site_number;
1887
1888 -- this is for orig_system_defaulting
1889 IF p_party_site_rec.party_site_id = FND_API.G_MISS_NUM THEN
1890 p_party_site_rec.party_site_id := NULL;
1891 END IF;
1892
1893
1894 -- call table-handler.
1895 HZ_PARTY_SITES_PKG.Insert_Row (
1896 X_PARTY_SITE_ID => p_party_site_rec.party_site_id,
1897 X_PARTY_ID => p_party_site_rec.party_id,
1898 X_LOCATION_ID => p_party_site_rec.location_id,
1899 X_PARTY_SITE_NUMBER => p_party_site_rec.party_site_number,
1900 X_ATTRIBUTE_CATEGORY => p_party_site_rec.attribute_category,
1901 X_ATTRIBUTE1 => p_party_site_rec.attribute1,
1902 X_ATTRIBUTE2 => p_party_site_rec.attribute2,
1903 X_ATTRIBUTE3 => p_party_site_rec.attribute3,
1904 X_ATTRIBUTE4 => p_party_site_rec.attribute4,
1905 X_ATTRIBUTE5 => p_party_site_rec.attribute5,
1906 X_ATTRIBUTE6 => p_party_site_rec.attribute6,
1907 X_ATTRIBUTE7 => p_party_site_rec.attribute7,
1908 X_ATTRIBUTE8 => p_party_site_rec.attribute8,
1909 X_ATTRIBUTE9 => p_party_site_rec.attribute9,
1910 X_ATTRIBUTE10 => p_party_site_rec.attribute10,
1911 X_ATTRIBUTE11 => p_party_site_rec.attribute11,
1912 X_ATTRIBUTE12 => p_party_site_rec.attribute12,
1913 X_ATTRIBUTE13 => p_party_site_rec.attribute13,
1914 X_ATTRIBUTE14 => p_party_site_rec.attribute14,
1915 X_ATTRIBUTE15 => p_party_site_rec.attribute15,
1916 X_ATTRIBUTE16 => p_party_site_rec.attribute16,
1917 X_ATTRIBUTE17 => p_party_site_rec.attribute17,
1918 X_ATTRIBUTE18 => p_party_site_rec.attribute18,
1919 X_ATTRIBUTE19 => p_party_site_rec.attribute19,
1920 X_ATTRIBUTE20 => p_party_site_rec.attribute20,
1921 X_ORIG_SYSTEM_REFERENCE => p_party_site_rec.orig_system_reference,
1922 X_LANGUAGE => p_party_site_rec.language,
1923 X_MAILSTOP => p_party_site_rec.mailstop,
1924 X_IDENTIFYING_ADDRESS_FLAG => p_party_site_rec.identifying_address_flag,
1925 X_STATUS => p_party_site_rec.status,
1926 X_PARTY_SITE_NAME => p_party_site_rec.party_site_name,
1927 X_ADDRESSEE => p_party_site_rec.addressee,
1928 X_OBJECT_VERSION_NUMBER => 1,
1929 X_CREATED_BY_MODULE => p_party_site_rec.created_by_module,
1930 X_APPLICATION_ID => p_party_site_rec.application_id,
1931 X_ACTUAL_CONTENT_SOURCE => p_actual_cont_source,
1932 X_GLOBAL_LOCATION_NUMBER => p_party_site_rec.global_location_number,
1933 X_DUNS_NUMBER_C => p_party_site_rec.duns_number_c
1934 );
1935 /*
1936 per HLD,mosr record should not be created for copy case, since old osr is still active
1937 hz_orig_system_ref_pvt.create_mosr_for_merge(
1938 FND_API.G_FALSE,
1939 'HZ_PARTY_SITES',
1940 p_party_site_rec.party_site_id,
1941 x_return_status,
1942 l_msg_count,
1943 l_msg_data);
1944
1945 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1946 RAISE FND_API.G_EXC_ERROR;
1947 END IF;
1948 */
1949 x_party_site_id := p_party_site_rec.party_site_id;
1950 x_party_site_number := p_party_site_rec.party_site_number;
1951
1952
1953 END do_create_party_site;
1954
1955 procedure do_update_address(
1956 p_party_id IN NUMBER,
1957 p_location_id IN NUMBER
1958 ) IS
1959
1960 CURSOR c_loc IS
1961 SELECT * FROM hz_locations
1962 WHERE location_id = p_location_id;
1963
1964 CURSOR c_party IS
1965 SELECT 'Y'
1966 FROM hz_parties
1967 WHERE party_id = p_party_id
1968 FOR UPDATE NOWAIT;
1969
1970 l_location_rec c_loc%ROWTYPE;
1971 l_exists VARCHAR2(1);
1972 l_do_not_normalize VARCHAR2(1):= 'N';
1973
1974 BEGIN
1975
1976 --check if party record is locked by any one else.
1977 BEGIN
1978 OPEN c_party;
1979 FETCH c_party INTO l_exists;
1980 CLOSE c_party;
1981 EXCEPTION
1982 WHEN OTHERS THEN
1983 l_do_not_normalize := 'Y';
1984 END;
1985
1986
1987 -- if location_id is null, we will null out the location
1988 -- components in hz_parties.
1989
1990 IF p_location_id IS NULL THEN
1991 l_location_rec.country := NULL;
1992 l_location_rec.address1 := NULL;
1993 l_location_rec.address2 := NULL;
1994 l_location_rec.address3 := NULL;
1995 l_location_rec.address4 := NULL;
1996 l_location_rec.city := NULL;
1997 l_location_rec.postal_code := NULL;
1998 l_location_rec.state := NULL;
1999 l_location_rec.province := NULL;
2000 l_location_rec.county := NULL;
2001 ELSE
2002 --Open the cursor and fetch location components and
2003 --content_source_type.
2004
2005 OPEN c_loc;
2006 FETCH c_loc INTO l_location_rec;
2007 CLOSE c_loc;
2008 END IF;
2009
2010 if l_do_not_normalize <> 'Y' then
2011
2012 UPDATE hz_parties
2013 SET country = l_location_rec.country,
2014 address1 = l_location_rec.address1,
2015 address2 = l_location_rec.address2,
2016 address3 = l_location_rec.address3,
2017 address4 = l_location_rec.address4,
2018 city = l_location_rec.city,
2019 postal_code = l_location_rec.postal_code,
2020 state = l_location_rec.state,
2021 province = l_location_rec.province,
2022 county = l_location_rec.county
2023 WHERE party_id = p_party_id;
2024
2025 end if;
2026
2027
2028 END do_update_address;
2029
2030 PROCEDURE do_unmark_address_flag(
2031 p_party_id IN NUMBER,
2032 p_party_site_id IN NUMBER := NULL
2033 ) IS
2034
2035 CURSOR c_party_sites IS
2036 SELECT rowid
2037 FROM hz_party_sites
2038 WHERE party_id = p_party_id
2039 AND party_site_id <> nvl(p_party_site_id,-999)
2040 AND identifying_address_flag = 'Y'
2041 AND rownum = 1
2042 FOR UPDATE NOWAIT;
2043
2044 l_rowid VARCHAR2(100);
2045 l_record_locked VARCHAR2(1) := 'N';
2046
2047 BEGIN
2048
2049 --check if party record is locked by any one else.
2050 BEGIN
2051 OPEN c_party_sites;
2052 FETCH c_party_sites INTO l_rowid;
2053 CLOSE c_party_sites;
2054 EXCEPTION
2055 WHEN OTHERS THEN
2056 l_record_locked := 'Y';
2057 END;
2058
2059 IF l_rowid IS NOT NULL AND l_record_locked <> 'Y' THEN
2060 UPDATE hz_party_sites
2061 SET identifying_address_flag = 'N'
2062 WHERE rowid = l_rowid;
2063 END IF;
2064
2065 END do_unmark_address_flag;
2066
2067
2068
2069 ----------------------------
2070 -- body of public procedures
2071 ----------------------------
2072
2073 PROCEDURE create_party_site (
2074 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2075 p_party_site_rec IN HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE,
2076 p_actual_cont_source IN VARCHAR2,
2077 x_party_site_id OUT NOCOPY NUMBER,
2078 x_party_site_number OUT NOCOPY VARCHAR2,
2079 x_return_status OUT NOCOPY VARCHAR2,
2080 x_msg_count OUT NOCOPY NUMBER,
2081 x_msg_data OUT NOCOPY VARCHAR2
2082 ) IS
2083
2084 l_api_name CONSTANT VARCHAR2(30) := 'create_party_site';
2085 l_api_version CONSTANT NUMBER := 1.0;
2086 l_party_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE := p_party_site_rec;
2087
2088 BEGIN
2089 -- standard start of API savepoint
2090 SAVEPOINT create_party_site;
2091
2092 -- initialize message list if p_init_msg_list is set to TRUE.
2093 IF FND_API.to_Boolean(p_init_msg_list) THEN
2094 FND_MSG_PUB.initialize;
2095 END IF;
2096
2097 -- initialize API return status to success.
2098 x_return_status := FND_API.G_RET_STS_SUCCESS;
2099
2100 -- Bug 2197181: added for mix-n-match project. first load data
2101 -- sources for this entity.
2102 /*
2103 IF g_pst_mixnmatch_enabled IS NULL THEN
2104 HZ_MIXNM_UTILITY.LoadDataSources(
2105 p_entity_name => 'HZ_LOCATIONS',
2106 p_entity_attr_id => g_pst_entity_attr_id,
2107 p_mixnmatch_enabled => g_pst_mixnmatch_enabled,
2108 p_selected_datasources => g_pst_selected_datasources );
2109 END IF;
2110 */
2111 -- call to business logic.
2112 do_create_party_site(
2113 l_party_site_rec,
2114 p_actual_cont_source,
2115 x_party_site_id,
2116 x_party_site_number,
2117 x_return_status
2118 );
2119
2120 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2121 -- Invoke business event system.
2122 HZ_BUSINESS_EVENT_V2PVT.create_party_site_event (
2123 l_party_site_rec );
2124 END IF;
2125
2126 -- Call to indicate Party Site creation to DQM
2127 HZ_DQM_SYNC.sync_party_site(l_party_site_rec.party_site_id,'C');
2128
2129 -- standard call to get message count and if count is 1, get message info.
2130 FND_MSG_PUB.Count_And_Get(
2131 p_encoded => FND_API.G_FALSE,
2132 p_count => x_msg_count,
2133 p_data => x_msg_data);
2134
2135 EXCEPTION
2136 WHEN FND_API.G_EXC_ERROR THEN
2137 ROLLBACK TO create_party_site;
2138 x_return_status := FND_API.G_RET_STS_ERROR;
2139 FND_MSG_PUB.Count_And_Get(
2140 p_encoded => FND_API.G_FALSE,
2141 p_count => x_msg_count,
2142 p_data => x_msg_data);
2143
2144 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2145 ROLLBACK TO create_party_site;
2146 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2147 FND_MSG_PUB.Count_And_Get(
2148 p_encoded => FND_API.G_FALSE,
2149 p_count => x_msg_count,
2150 p_data => x_msg_data);
2151 WHEN OTHERS THEN
2152 ROLLBACK TO create_party_site;
2153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2154 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
2155 fnd_message.set_token('ERROR' ,SQLERRM);
2156 fnd_msg_pub.add;
2157 FND_MSG_PUB.Count_And_Get(
2158 p_encoded => FND_API.G_FALSE,
2159 p_count => x_msg_count,
2160 p_data => x_msg_data);
2161
2162 END create_party_site;
2163
2164
2165 PROCEDURE get_party_site_rec (
2166 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2167 p_party_site_id IN NUMBER,
2168 x_party_site_rec OUT NOCOPY HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE,
2169 x_actual_cont_source OUT NOCOPY VARCHAR2,
2170 x_return_status OUT NOCOPY VARCHAR2,
2171 x_msg_count OUT NOCOPY NUMBER,
2172 x_msg_data OUT NOCOPY VARCHAR2
2173
2174 ) IS
2175
2176 l_api_name CONSTANT VARCHAR2(30) := 'get_party_site_rec';
2177 l_api_version CONSTANT NUMBER := 1.0;
2178
2179 BEGIN
2180
2181 --Initialize message list if p_init_msg_list is set to TRUE.
2182 IF FND_API.to_Boolean(p_init_msg_list) THEN
2183 FND_MSG_PUB.initialize;
2184 END IF;
2185
2186 --Initialize API return status to success.
2187 x_return_status := FND_API.G_RET_STS_SUCCESS;
2188
2189 --Check whether primary key has been passed in.
2190 IF p_party_site_id IS NULL OR
2191 p_party_site_id = FND_API.G_MISS_NUM THEN
2192 fnd_message.set_name( 'AR', 'HZ_API_MISSING_COLUMN' );
2193 fnd_message.set_token( 'COLUMN', 'party_site_id' );
2194 fnd_msg_pub.add;
2195 RAISE FND_API.G_EXC_ERROR;
2196 END IF;
2197
2198 x_party_site_rec.party_site_id := p_party_site_id;
2199
2200 HZ_PARTY_SITES_PKG.Select_Row (
2201 X_PARTY_SITE_ID => x_party_site_rec.party_site_id,
2202 X_PARTY_ID => x_party_site_rec.party_id,
2203 X_LOCATION_ID => x_party_site_rec.location_id,
2204 X_PARTY_SITE_NUMBER => x_party_site_rec.party_site_number,
2205 X_ATTRIBUTE_CATEGORY => x_party_site_rec.attribute_category,
2206 X_ATTRIBUTE1 => x_party_site_rec.attribute1,
2207 X_ATTRIBUTE2 => x_party_site_rec.attribute2,
2208 X_ATTRIBUTE3 => x_party_site_rec.attribute3,
2209 X_ATTRIBUTE4 => x_party_site_rec.attribute4,
2210 X_ATTRIBUTE5 => x_party_site_rec.attribute5,
2211 X_ATTRIBUTE6 => x_party_site_rec.attribute6,
2212 X_ATTRIBUTE7 => x_party_site_rec.attribute7,
2213 X_ATTRIBUTE8 => x_party_site_rec.attribute8,
2214 X_ATTRIBUTE9 => x_party_site_rec.attribute9,
2215 X_ATTRIBUTE10 => x_party_site_rec.attribute10,
2216 X_ATTRIBUTE11 => x_party_site_rec.attribute11,
2217 X_ATTRIBUTE12 => x_party_site_rec.attribute12,
2218 X_ATTRIBUTE13 => x_party_site_rec.attribute13,
2219 X_ATTRIBUTE14 => x_party_site_rec.attribute14,
2220 X_ATTRIBUTE15 => x_party_site_rec.attribute15,
2221 X_ATTRIBUTE16 => x_party_site_rec.attribute16,
2222 X_ATTRIBUTE17 => x_party_site_rec.attribute17,
2223 X_ATTRIBUTE18 => x_party_site_rec.attribute18,
2224 X_ATTRIBUTE19 => x_party_site_rec.attribute19,
2225 X_ATTRIBUTE20 => x_party_site_rec.attribute20,
2226 X_ORIG_SYSTEM_REFERENCE => x_party_site_rec.orig_system_reference,
2227 X_LANGUAGE => x_party_site_rec.language,
2228 X_MAILSTOP => x_party_site_rec.mailstop,
2229 X_IDENTIFYING_ADDRESS_FLAG => x_party_site_rec.identifying_address_flag,
2230 X_STATUS => x_party_site_rec.status,
2231 X_PARTY_SITE_NAME => x_party_site_rec.party_site_name,
2232 X_ADDRESSEE => x_party_site_rec.addressee,
2233 X_CREATED_BY_MODULE => x_party_site_rec.created_by_module,
2234 X_APPLICATION_ID => x_party_site_rec.application_id,
2235 X_ACTUAL_CONTENT_SOURCE => x_actual_cont_source,
2236 X_GLOBAL_LOCATION_NUMBER => x_party_site_rec.global_location_number,
2237 X_DUNS_NUMBER_C => x_party_site_rec.duns_number_c
2238 );
2239
2240 --Standard call to get message count and if count is 1, get message info.
2241 FND_MSG_PUB.Count_And_Get(
2242 p_encoded => FND_API.G_FALSE,
2243 p_count => x_msg_count,
2244 p_data => x_msg_data );
2245
2246 EXCEPTION
2247 WHEN FND_API.G_EXC_ERROR THEN
2248 x_return_status := FND_API.G_RET_STS_ERROR;
2249
2250 FND_MSG_PUB.Count_And_Get(
2251 p_encoded => FND_API.G_FALSE,
2252 p_count => x_msg_count,
2253 p_data => x_msg_data );
2254
2255 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2257
2258 FND_MSG_PUB.Count_And_Get(
2259 p_encoded => FND_API.G_FALSE,
2260 p_count => x_msg_count,
2261 p_data => x_msg_data );
2262
2263 WHEN OTHERS THEN
2264 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2265 fnd_message.set_name( 'AR', 'HZ_API_OTHERS_EXCEP' );
2266 fnd_message.set_token( 'ERROR' ,SQLERRM );
2267 fnd_msg_pub.add;
2268 FND_MSG_PUB.Count_And_Get(
2269 p_encoded => FND_API.G_FALSE,
2270 p_count => x_msg_count,
2271 p_data => x_msg_data );
2272
2273 END get_party_site_rec;
2274
2275 ------------------ACCOUNT_SITE_-----------------------------------------------
2276
2277 PROCEDURE do_create_cust_acct_site (
2278 p_cust_acct_site_rec IN OUT NOCOPY
2279 HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE,
2280 p_org_id IN NUMBER DEFAULT null,
2281 x_cust_acct_site_id OUT NOCOPY NUMBER,
2282 x_return_status IN OUT NOCOPY VARCHAR2
2283 );
2284
2285 PROCEDURE do_create_cust_site_use (
2286 p_cust_site_use_rec IN OUT NOCOPY HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE,
2287 p_customer_profile_rec IN OUT NOCOPY HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE,
2288 p_create_profile IN VARCHAR2 := FND_API.G_TRUE,
2289 p_create_profile_amt IN VARCHAR2 := FND_API.G_TRUE,
2290 p_org_id IN NUMBER DEFAULT null,
2291 x_site_use_id OUT NOCOPY NUMBER,
2292 x_return_status IN OUT NOCOPY VARCHAR2
2293 );
2294
2295 PROCEDURE denormalize_site_use_flag (
2296 p_cust_acct_site_id IN NUMBER,
2297 p_site_use_code IN VARCHAR2,
2298 p_flag IN VARCHAR2
2299 );
2300
2301 --------------------------------------
2302 -- private procedures and functions
2303 --------------------------------------
2304
2305
2306 PROCEDURE do_create_cust_acct_site (
2307 p_cust_acct_site_rec IN OUT NOCOPY
2308 HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE,
2309 p_org_id IN NUMBER DEFAULT null,
2310 x_cust_acct_site_id OUT NOCOPY NUMBER,
2311 x_return_status IN OUT NOCOPY VARCHAR2
2312 ) IS
2313
2314 l_msg_count NUMBER;
2315 l_msg_data VARCHAR2(2000);
2316
2317 l_location_id NUMBER;
2318 l_loc_id NUMBER;
2319 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
2320
2321 BEGIN
2322
2323 --Initialize the created by module
2324 p_cust_acct_site_rec.created_by_module := 'HZ_TCA_CUSTOMER_MERGE';
2325
2326 p_cust_acct_site_rec.orig_system_reference := null;
2327 p_cust_acct_site_rec.cust_acct_site_id := null;
2328
2329
2330 -- Call table-handler.
2331 HZ_CUST_ACCT_SITES_PKG.Insert_Row (
2332 X_CUST_ACCT_SITE_ID => p_cust_acct_site_rec.cust_acct_site_id,
2333 X_CUST_ACCOUNT_ID => p_cust_acct_site_rec.cust_account_id,
2334 X_PARTY_SITE_ID => p_cust_acct_site_rec.party_site_id,
2335 X_ATTRIBUTE_CATEGORY => p_cust_acct_site_rec.attribute_category,
2336 X_ATTRIBUTE1 => p_cust_acct_site_rec.attribute1,
2337 X_ATTRIBUTE2 => p_cust_acct_site_rec.attribute2,
2338 X_ATTRIBUTE3 => p_cust_acct_site_rec.attribute3,
2339 X_ATTRIBUTE4 => p_cust_acct_site_rec.attribute4,
2340 X_ATTRIBUTE5 => p_cust_acct_site_rec.attribute5,
2341 X_ATTRIBUTE6 => p_cust_acct_site_rec.attribute6,
2342 X_ATTRIBUTE7 => p_cust_acct_site_rec.attribute7,
2343 X_ATTRIBUTE8 => p_cust_acct_site_rec.attribute8,
2344 X_ATTRIBUTE9 => p_cust_acct_site_rec.attribute9,
2345 X_ATTRIBUTE10 => p_cust_acct_site_rec.attribute10,
2346 X_ATTRIBUTE11 => p_cust_acct_site_rec.attribute11,
2347 X_ATTRIBUTE12 => p_cust_acct_site_rec.attribute12,
2348 X_ATTRIBUTE13 => p_cust_acct_site_rec.attribute13,
2349 X_ATTRIBUTE14 => p_cust_acct_site_rec.attribute14,
2350 X_ATTRIBUTE15 => p_cust_acct_site_rec.attribute15,
2351 X_ATTRIBUTE16 => p_cust_acct_site_rec.attribute16,
2352 X_ATTRIBUTE17 => p_cust_acct_site_rec.attribute17,
2353 X_ATTRIBUTE18 => p_cust_acct_site_rec.attribute18,
2354 X_ATTRIBUTE19 => p_cust_acct_site_rec.attribute19,
2355 X_ATTRIBUTE20 => p_cust_acct_site_rec.attribute20,
2356 X_GLOBAL_ATTRIBUTE_CATEGORY=>p_cust_acct_site_rec.global_attribute_category,
2357 X_GLOBAL_ATTRIBUTE1 => p_cust_acct_site_rec.global_attribute1,
2358 X_GLOBAL_ATTRIBUTE2 => p_cust_acct_site_rec.global_attribute2,
2359 X_GLOBAL_ATTRIBUTE3 => p_cust_acct_site_rec.global_attribute3,
2360 X_GLOBAL_ATTRIBUTE4 => p_cust_acct_site_rec.global_attribute4,
2361 X_GLOBAL_ATTRIBUTE5 => p_cust_acct_site_rec.global_attribute5,
2362 X_GLOBAL_ATTRIBUTE6 => p_cust_acct_site_rec.global_attribute6,
2363 X_GLOBAL_ATTRIBUTE7 => p_cust_acct_site_rec.global_attribute7,
2364 X_GLOBAL_ATTRIBUTE8 => p_cust_acct_site_rec.global_attribute8,
2365 X_GLOBAL_ATTRIBUTE9 => p_cust_acct_site_rec.global_attribute9,
2366 X_GLOBAL_ATTRIBUTE10 => p_cust_acct_site_rec.global_attribute10,
2367 X_GLOBAL_ATTRIBUTE11 => p_cust_acct_site_rec.global_attribute11,
2368 X_GLOBAL_ATTRIBUTE12 => p_cust_acct_site_rec.global_attribute12,
2369 X_GLOBAL_ATTRIBUTE13 => p_cust_acct_site_rec.global_attribute13,
2370 X_GLOBAL_ATTRIBUTE14 => p_cust_acct_site_rec.global_attribute14,
2371 X_GLOBAL_ATTRIBUTE15 => p_cust_acct_site_rec.global_attribute15,
2372 X_GLOBAL_ATTRIBUTE16 => p_cust_acct_site_rec.global_attribute16,
2373 X_GLOBAL_ATTRIBUTE17 => p_cust_acct_site_rec.global_attribute17,
2374 X_GLOBAL_ATTRIBUTE18 => p_cust_acct_site_rec.global_attribute18,
2375 X_GLOBAL_ATTRIBUTE19 => p_cust_acct_site_rec.global_attribute19,
2376 X_GLOBAL_ATTRIBUTE20 => p_cust_acct_site_rec.global_attribute20,
2377 X_ORIG_SYSTEM_REFERENCE => p_cust_acct_site_rec.orig_system_reference,
2378 X_STATUS => p_cust_acct_site_rec.status,
2379 X_CUSTOMER_CATEGORY_CODE => p_cust_acct_site_rec.customer_category_code,
2380 X_LANGUAGE => p_cust_acct_site_rec.language,
2381 X_KEY_ACCOUNT_FLAG => p_cust_acct_site_rec.key_account_flag,
2382 X_TP_HEADER_ID => p_cust_acct_site_rec.tp_header_id,
2383 X_ECE_TP_LOCATION_CODE => p_cust_acct_site_rec.ece_tp_location_code,
2384 X_PRIMARY_SPECIALIST_ID=> p_cust_acct_site_rec.primary_specialist_id,
2385 X_SECONDARY_SPECIALIST_ID => p_cust_acct_site_rec.secondary_specialist_id,
2386 X_TERRITORY_ID => p_cust_acct_site_rec.territory_id,
2387 X_TERRITORY => p_cust_acct_site_rec.territory,
2388 X_TRANSLATED_CUSTOMER_NAME =>p_cust_acct_site_rec.translated_customer_name,
2389 X_OBJECT_VERSION_NUMBER => 1,
2390 X_CREATED_BY_MODULE => p_cust_acct_site_rec.created_by_module,
2391 X_APPLICATION_ID => p_cust_acct_site_rec.application_id,
2392 X_ORG_ID => p_org_id
2393 );
2394 /*
2395 per HLD,mosr record should not be created for copy case, since old osr is still active
2396 hz_orig_system_ref_pvt.create_mosr_for_merge(
2397 FND_API.G_FALSE,
2398 'HZ_CUST_ACCT_SITES_ALL',
2399 p_cust_acct_site_rec.cust_acct_site_id,
2400 x_return_status,
2401 l_msg_count,
2402 l_msg_data);
2403 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2404 RAISE FND_API.G_EXC_ERROR;
2405 END IF;
2406 */
2407 x_cust_acct_site_id := p_cust_acct_site_rec.cust_acct_site_id;
2408
2409 END do_create_cust_acct_site;
2410
2411
2412 PROCEDURE do_create_cust_site_use (
2413 p_cust_site_use_rec IN OUT NOCOPY HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE,
2414 p_customer_profile_rec IN OUT NOCOPY HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE,
2415 p_create_profile IN VARCHAR2 := FND_API.G_TRUE,
2416 p_create_profile_amt IN VARCHAR2 := FND_API.G_TRUE,
2417 p_org_id IN NUMBER DEFAULT null,
2418 x_site_use_id OUT NOCOPY NUMBER,
2419 x_return_status IN OUT NOCOPY VARCHAR2
2420 ) IS
2421
2422
2423 l_dummy VARCHAR2(1);
2424 l_message_count NUMBER;
2425 l_msg_count NUMBER;
2426 l_msg_data VARCHAR2(2000);
2427 l_flag VARCHAR2(1);
2428
2429 l_party_site_use_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_REC_TYPE;
2430 l_party_site_id NUMBER;
2431 l_party_site_use_id NUMBER;
2432 l_cust_account_profile_id NUMBER;
2433 l_bill_to_flag HZ_CUST_ACCT_SITES_ALL.bill_to_flag%TYPE;
2434 l_ship_to_flag HZ_CUST_ACCT_SITES_ALL.ship_to_flag%TYPE;
2435 l_market_flag HZ_CUST_ACCT_SITES_ALL.market_flag%TYPE;
2436
2437 BEGIN
2438
2439 p_cust_site_use_rec.created_by_module := 'HZ_TCA_CUSTOMER_MERGE';
2440 p_cust_site_use_rec.site_use_id := null;
2441 p_cust_site_use_rec.orig_system_reference := null;
2442
2443
2444 -- Call table-handler.
2445
2446 HZ_CUST_SITE_USES_PKG.Insert_Row (
2447 X_SITE_USE_ID => p_cust_site_use_rec.site_use_id,
2448 X_CUST_ACCT_SITE_ID => p_cust_site_use_rec.cust_acct_site_id,
2449 X_SITE_USE_CODE => p_cust_site_use_rec.site_use_code,
2450 X_PRIMARY_FLAG => p_cust_site_use_rec.primary_flag,
2451 X_STATUS => p_cust_site_use_rec.status,
2452 X_LOCATION => p_cust_site_use_rec.location,
2453 X_CONTACT_ID => p_cust_site_use_rec.contact_id,
2454 X_BILL_TO_SITE_USE_ID => p_cust_site_use_rec.bill_to_site_use_id,
2455 X_ORIG_SYSTEM_REFERENCE => p_cust_site_use_rec.orig_system_reference,
2456 X_SIC_CODE => p_cust_site_use_rec.sic_code,
2457 X_PAYMENT_TERM_ID => p_cust_site_use_rec.payment_term_id,
2458 X_GSA_INDICATOR => p_cust_site_use_rec.gsa_indicator,
2459 X_SHIP_PARTIAL => p_cust_site_use_rec.ship_partial,
2460 X_SHIP_VIA => p_cust_site_use_rec.ship_via,
2461 X_FOB_POINT => p_cust_site_use_rec.fob_point,
2462 X_ORDER_TYPE_ID => p_cust_site_use_rec.order_type_id,
2463 X_PRICE_LIST_ID => p_cust_site_use_rec.price_list_id,
2464 X_FREIGHT_TERM => p_cust_site_use_rec.freight_term,
2465 X_WAREHOUSE_ID => p_cust_site_use_rec.warehouse_id,
2466 X_TERRITORY_ID => p_cust_site_use_rec.territory_id,
2467 X_ATTRIBUTE_CATEGORY => p_cust_site_use_rec.attribute_category,
2468 X_ATTRIBUTE1 => p_cust_site_use_rec.attribute1,
2469 X_ATTRIBUTE2 => p_cust_site_use_rec.attribute2,
2470 X_ATTRIBUTE3 => p_cust_site_use_rec.attribute3,
2471 X_ATTRIBUTE4 => p_cust_site_use_rec.attribute4,
2472 X_ATTRIBUTE5 => p_cust_site_use_rec.attribute5,
2473 X_ATTRIBUTE6 => p_cust_site_use_rec.attribute6,
2474 X_ATTRIBUTE7 => p_cust_site_use_rec.attribute7,
2475 X_ATTRIBUTE8 => p_cust_site_use_rec.attribute8,
2476 X_ATTRIBUTE9 => p_cust_site_use_rec.attribute9,
2477 X_ATTRIBUTE10 => p_cust_site_use_rec.attribute10,
2478 X_TAX_REFERENCE => p_cust_site_use_rec.tax_reference,
2479 X_SORT_PRIORITY => p_cust_site_use_rec.sort_priority,
2480 X_TAX_CODE => p_cust_site_use_rec.tax_code,
2481 X_ATTRIBUTE11 => p_cust_site_use_rec.attribute11,
2482 X_ATTRIBUTE12 => p_cust_site_use_rec.attribute12,
2483 X_ATTRIBUTE13 => p_cust_site_use_rec.attribute13,
2484 X_ATTRIBUTE14 => p_cust_site_use_rec.attribute14,
2485 X_ATTRIBUTE15 => p_cust_site_use_rec.attribute15,
2486 X_ATTRIBUTE16 => p_cust_site_use_rec.attribute16,
2487 X_ATTRIBUTE17 => p_cust_site_use_rec.attribute17,
2488 X_ATTRIBUTE18 => p_cust_site_use_rec.attribute18,
2489 X_ATTRIBUTE19 => p_cust_site_use_rec.attribute19,
2490 X_ATTRIBUTE20 => p_cust_site_use_rec.attribute20,
2491 X_ATTRIBUTE21 => p_cust_site_use_rec.attribute21,
2492 X_ATTRIBUTE22 => p_cust_site_use_rec.attribute22,
2493 X_ATTRIBUTE23 => p_cust_site_use_rec.attribute23,
2494 X_ATTRIBUTE24 => p_cust_site_use_rec.attribute24,
2495 X_ATTRIBUTE25 => p_cust_site_use_rec.attribute25,
2496 X_DEMAND_CLASS_CODE => p_cust_site_use_rec.demand_class_code,
2497 X_TAX_HEADER_LEVEL_FLAG => p_cust_site_use_rec.tax_header_level_flag,
2498 X_TAX_ROUNDING_RULE => p_cust_site_use_rec.tax_rounding_rule,
2499 X_GLOBAL_ATTRIBUTE1 => p_cust_site_use_rec.global_attribute1,
2500 X_GLOBAL_ATTRIBUTE2 => p_cust_site_use_rec.global_attribute2,
2501 X_GLOBAL_ATTRIBUTE3 => p_cust_site_use_rec.global_attribute3,
2502 X_GLOBAL_ATTRIBUTE4 => p_cust_site_use_rec.global_attribute4,
2503 X_GLOBAL_ATTRIBUTE5 => p_cust_site_use_rec.global_attribute5,
2504 X_GLOBAL_ATTRIBUTE6 => p_cust_site_use_rec.global_attribute6,
2505 X_GLOBAL_ATTRIBUTE7 => p_cust_site_use_rec.global_attribute7,
2506 X_GLOBAL_ATTRIBUTE8 => p_cust_site_use_rec.global_attribute8,
2507 X_GLOBAL_ATTRIBUTE9 => p_cust_site_use_rec.global_attribute9,
2508 X_GLOBAL_ATTRIBUTE10 =>p_cust_site_use_rec.global_attribute10,
2509 X_GLOBAL_ATTRIBUTE11 => p_cust_site_use_rec.global_attribute11,
2510 X_GLOBAL_ATTRIBUTE12 => p_cust_site_use_rec.global_attribute12,
2511 X_GLOBAL_ATTRIBUTE13 => p_cust_site_use_rec.global_attribute13,
2512 X_GLOBAL_ATTRIBUTE14 => p_cust_site_use_rec.global_attribute14,
2513 X_GLOBAL_ATTRIBUTE15 => p_cust_site_use_rec.global_attribute15,
2514 X_GLOBAL_ATTRIBUTE16 => p_cust_site_use_rec.global_attribute16,
2515 X_GLOBAL_ATTRIBUTE17 => p_cust_site_use_rec.global_attribute17,
2516 X_GLOBAL_ATTRIBUTE18 => p_cust_site_use_rec.global_attribute18,
2517 X_GLOBAL_ATTRIBUTE19 => p_cust_site_use_rec.global_attribute19,
2518 X_GLOBAL_ATTRIBUTE20 => p_cust_site_use_rec.global_attribute20,
2519 X_GLOBAL_ATTRIBUTE_CATEGORY=>p_cust_site_use_rec.global_attribute_category,
2520 X_PRIMARY_SALESREP_ID => p_cust_site_use_rec.primary_salesrep_id,
2521 X_FINCHRG_RECEIVABLES_TRX_ID=>p_cust_site_use_rec.finchrg_receivables_trx_id,
2522 X_DATES_NEGATIVE_TOLERANCE=> p_cust_site_use_rec.dates_negative_tolerance,
2523 X_DATES_POSITIVE_TOLERANCE=> p_cust_site_use_rec.dates_positive_tolerance,
2524 X_DATE_TYPE_PREFERENCE => p_cust_site_use_rec.date_type_preference,
2525 X_OVER_SHIPMENT_TOLERANCE => p_cust_site_use_rec.over_shipment_tolerance,
2526 X_UNDER_SHIPMENT_TOLERANCE=> p_cust_site_use_rec.under_shipment_tolerance,
2527 X_ITEM_CROSS_REF_PREF => p_cust_site_use_rec.item_cross_ref_pref,
2528 X_OVER_RETURN_TOLERANCE => p_cust_site_use_rec.over_return_tolerance,
2529 X_UNDER_RETURN_TOLERANCE => p_cust_site_use_rec.under_return_tolerance,
2530 X_SHIP_SETS_INCLUDE_LINES_FLAG=>p_cust_site_use_rec.ship_sets_include_lines_flag,
2531 X_ARRIVALSETS_INCLUDE_LINES_FG=> p_cust_site_use_rec.arrivalsets_include_lines_flag,
2532 X_SCHED_DATE_PUSH_FLAG => p_cust_site_use_rec.sched_date_push_flag,
2533 X_INVOICE_QUANTITY_RULE => p_cust_site_use_rec.invoice_quantity_rule,
2534 X_PRICING_EVENT => p_cust_site_use_rec.pricing_event,
2535 X_GL_ID_REC => p_cust_site_use_rec.gl_id_rec,
2536 X_GL_ID_REV => p_cust_site_use_rec.gl_id_rev,
2537 X_GL_ID_TAX => p_cust_site_use_rec.gl_id_tax,
2538 X_GL_ID_FREIGHT => p_cust_site_use_rec.gl_id_freight,
2539 X_GL_ID_CLEARING => p_cust_site_use_rec.gl_id_clearing,
2540 X_GL_ID_UNBILLED => p_cust_site_use_rec.gl_id_unbilled,
2541 X_GL_ID_UNEARNED => p_cust_site_use_rec.gl_id_unearned,
2542 X_GL_ID_UNPAID_REC => p_cust_site_use_rec.gl_id_unpaid_rec,
2543 X_GL_ID_REMITTANCE => p_cust_site_use_rec.gl_id_remittance,
2544 X_GL_ID_FACTOR => p_cust_site_use_rec.gl_id_factor,
2545 X_TAX_CLASSIFICATION => p_cust_site_use_rec.tax_classification,
2546 X_OBJECT_VERSION_NUMBER => 1,
2547 X_CREATED_BY_MODULE => p_cust_site_use_rec.created_by_module,
2548 X_APPLICATION_ID => p_cust_site_use_rec.application_id,
2549 X_ORG_ID => p_org_id
2550 );
2551
2552 -- If this is a active bill_to or ship_to or market,
2553 -- set the appropriate denormalized flag in hz_cust_acct_sites_all.
2554
2555 IF p_cust_site_use_rec.site_use_code IN ('BILL_TO', 'SHIP_TO', 'MARKET' ) THEN
2556 ----Bug No.5211233
2557 IF p_cust_site_use_rec.primary_flag = 'Y' THEN
2558 l_flag := 'P';
2559 ----Bug No. 5211233
2560
2561 ELSIF p_cust_site_use_rec.status = 'A' OR
2562 p_cust_site_use_rec.status IS NULL OR
2563 p_cust_site_use_rec.status = FND_API.G_MISS_CHAR
2564 THEN
2565 l_flag := 'Y';
2566 ELSE
2567 l_flag := NULL;
2568 END IF;
2569
2570 denormalize_site_use_flag (
2571 p_cust_site_use_rec.cust_acct_site_id,
2572 p_cust_site_use_rec.site_use_code,
2573 l_flag );
2574
2575 END IF;
2576
2577 IF p_create_profile = FND_API.G_TRUE THEN
2578
2579 -- Create the profile for the site use
2580
2581 p_customer_profile_rec.site_use_id := p_cust_site_use_rec.site_use_id;
2582 p_customer_profile_rec.created_by_module := p_cust_site_use_rec.created_by_module;
2583 p_customer_profile_rec.application_id := p_cust_site_use_rec.application_id;
2584
2585 SELECT CUST_ACCOUNT_ID INTO p_customer_profile_rec.cust_account_id
2586 FROM HZ_CUST_ACCT_SITES_ALL
2587 WHERE CUST_ACCT_SITE_ID = p_cust_site_use_rec.cust_acct_site_id;
2588
2589 create_customer_profile (
2590 p_customer_profile_rec => p_customer_profile_rec,
2591 p_create_profile_amt => p_create_profile_amt,
2592 x_return_status => x_return_status,
2593 x_msg_count => l_msg_count,
2594 x_msg_data => l_msg_data,
2595 x_cust_account_profile_id => l_cust_account_profile_id );
2596
2597 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2598 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2599 RAISE FND_API.G_EXC_ERROR;
2600 ELSE
2601 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2602 END IF;
2603 END IF;
2604
2605 END IF;
2606 /*
2607 per HLD,mosr record should not be created for copy case, since old osr is still active
2608 hz_orig_system_ref_pvt.create_mosr_for_merge(
2609 FND_API.G_FALSE,
2610 'HZ_CUST_SITE_USES_ALL',
2611 p_cust_site_use_rec.site_use_id,
2612 x_return_status,
2613 l_msg_count,
2614 l_msg_data);
2615 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2616 RAISE FND_API.G_EXC_ERROR;
2617 END IF;
2618 */
2619 x_site_use_id := p_cust_site_use_rec.site_use_id;
2620
2621 END do_create_cust_site_use;
2622
2623
2624 PROCEDURE denormalize_site_use_flag (
2625 p_cust_acct_site_id IN NUMBER,
2626 p_site_use_code IN VARCHAR2,
2627 p_flag IN VARCHAR2
2628 ) IS
2629
2630 l_debug_prefix VARCHAR2(30) := ''; --'denormalize_site_use_flag'
2631
2632 BEGIN
2633
2634
2635 IF p_site_use_code = 'BILL_TO' THEN
2636 UPDATE HZ_CUST_ACCT_SITES_ALL
2637 SET BILL_TO_FLAG = p_flag
2638 WHERE CUST_ACCT_SITE_ID = p_cust_acct_site_id;
2639 ELSIF p_site_use_code = 'SHIP_TO' THEN
2640 UPDATE HZ_CUST_ACCT_SITES_ALL
2641 SET SHIP_TO_FLAG = p_flag
2642 WHERE CUST_ACCT_SITE_ID = p_cust_acct_site_id;
2643 ELSIF p_site_use_code = 'MARKET' THEN
2644 UPDATE HZ_CUST_ACCT_SITES_ALL
2645 SET MARKET_FLAG = p_flag
2646 WHERE CUST_ACCT_SITE_ID = p_cust_acct_site_id;
2647 END IF;
2648
2649
2650 END denormalize_site_use_flag;
2651
2652 --------------------------------------
2653 -- public procedures and functions
2654 --------------------------------------
2655
2656 PROCEDURE create_cust_acct_site (
2657 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2658 p_cust_acct_site_rec IN HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE,
2659 p_org_id IN NUMBER ,
2660 x_cust_acct_site_id OUT NOCOPY NUMBER,
2661 x_return_status OUT NOCOPY VARCHAR2,
2662 x_msg_count OUT NOCOPY NUMBER,
2663 x_msg_data OUT NOCOPY VARCHAR2
2664 ) IS
2665
2666 l_cust_acct_site_rec HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE :=
2667 p_cust_acct_site_rec;
2668
2669 BEGIN
2670
2671 -- Standard start of API savepoint
2672 SAVEPOINT create_cust_acct_site;
2673
2674
2675 -- Initialize message list if p_init_msg_list is set to TRUE.
2676 IF FND_API.to_Boolean(p_init_msg_list) THEN
2677 FND_MSG_PUB.initialize;
2678 END IF;
2679
2680 -- Initialize API return status to success.
2681 x_return_status := FND_API.G_RET_STS_SUCCESS;
2682
2683 -- Call to business logic.
2684 do_create_cust_acct_site (
2685 l_cust_acct_site_rec,
2686 p_org_id,
2687 x_cust_acct_site_id,
2688 x_return_status );
2689
2690 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2691 -- Invoke business event system.
2692 HZ_BUSINESS_EVENT_V2PVT.create_cust_acct_site_event (
2693 l_cust_acct_site_rec );
2694 END IF;
2695
2696 -- Standard call to get message count and if count is 1, get message info.
2697 FND_MSG_PUB.Count_And_Get(
2698 p_encoded => FND_API.G_FALSE,
2699 p_count => x_msg_count,
2700 p_data => x_msg_data );
2701
2702
2703 EXCEPTION
2704 WHEN FND_API.G_EXC_ERROR THEN
2705 ROLLBACK TO create_cust_acct_site;
2706 x_return_status := FND_API.G_RET_STS_ERROR;
2707
2708 FND_MSG_PUB.Count_And_Get(
2709 p_encoded => FND_API.G_FALSE,
2710 p_count => x_msg_count,
2711 p_data => x_msg_data );
2712
2713
2714 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2715 ROLLBACK TO create_cust_acct_site;
2716 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2717
2718 FND_MSG_PUB.Count_And_Get(
2719 p_encoded => FND_API.G_FALSE,
2720 p_count => x_msg_count,
2721 p_data => x_msg_data );
2722
2723 WHEN OTHERS THEN
2724 ROLLBACK TO create_cust_acct_site;
2725 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2726
2727 fnd_message.set_name( 'AR', 'HZ_API_OTHERS_EXCEP' );
2728 fnd_message.set_token( 'ERROR' ,SQLERRM );
2729 fnd_msg_pub.add;
2730
2731 FND_MSG_PUB.Count_And_Get(
2732 p_encoded => FND_API.G_FALSE,
2733 p_count => x_msg_count,
2734 p_data => x_msg_data );
2735
2736
2737 END create_cust_acct_site;
2738
2739 PROCEDURE create_cust_site_use (
2740 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2741 p_cust_site_use_rec IN HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE,
2742 p_customer_profile_rec IN HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE,
2743 p_create_profile IN VARCHAR2 := FND_API.G_TRUE,
2744 p_create_profile_amt IN VARCHAR2 := FND_API.G_TRUE,
2745 p_org_id IN NUMBER ,
2746 x_site_use_id OUT NOCOPY NUMBER,
2747 x_return_status OUT NOCOPY VARCHAR2,
2748 x_msg_count OUT NOCOPY NUMBER,
2749 x_msg_data OUT NOCOPY VARCHAR2
2750 ) IS
2751
2752 l_cust_site_use_rec HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE
2753 := p_cust_site_use_rec;
2754 l_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE
2755 := p_customer_profile_rec;
2756
2757 BEGIN
2758
2759 -- Standard start of API savepoint
2760 SAVEPOINT create_cust_site_use;
2761
2762 -- Initialize message list if p_init_msg_list is set to TRUE.
2763 IF FND_API.to_Boolean(p_init_msg_list) THEN
2764 FND_MSG_PUB.initialize;
2765 END IF;
2766
2767 -- Initialize API return status to success.
2768 x_return_status := FND_API.G_RET_STS_SUCCESS;
2769
2770 -- Call to business logic.
2771 do_create_cust_site_use (
2772 l_cust_site_use_rec,
2773 l_customer_profile_rec,
2774 p_create_profile,
2775 p_create_profile_amt,
2776 p_org_id,
2777 x_site_use_id,
2778 x_return_status );
2779
2780 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2781 -- Invoke business event system.
2782 HZ_BUSINESS_EVENT_V2PVT.create_cust_site_use_event (
2783 l_cust_site_use_rec,
2784 l_customer_profile_rec,
2785 p_create_profile,
2786 p_create_profile_amt );
2787 END IF;
2788
2789 -- Standard call to get message count and if count is 1, get message info.
2790 FND_MSG_PUB.Count_And_Get(
2791 p_encoded => FND_API.G_FALSE,
2792 p_count => x_msg_count,
2793 p_data => x_msg_data );
2794
2795 EXCEPTION
2796 WHEN FND_API.G_EXC_ERROR THEN
2797 ROLLBACK TO create_cust_site_use;
2798 x_return_status := FND_API.G_RET_STS_ERROR;
2799
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 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2806 ROLLBACK TO create_cust_site_use;
2807 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2808
2809 FND_MSG_PUB.Count_And_Get(
2810 p_encoded => FND_API.G_FALSE,
2811 p_count => x_msg_count,
2812 p_data => x_msg_data );
2813
2814
2815 WHEN OTHERS THEN
2816 ROLLBACK TO create_cust_site_use;
2817 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2818
2819 fnd_message.set_name( 'AR', 'HZ_API_OTHERS_EXCEP' );
2820 fnd_message.set_token( 'ERROR' ,SQLERRM );
2821 fnd_msg_pub.add;
2822
2823 FND_MSG_PUB.Count_And_Get(
2824 p_encoded => FND_API.G_FALSE,
2825 p_count => x_msg_count,
2826 p_data => x_msg_data );
2827
2828 END create_cust_site_use;
2829
2830 ----------------CUST_PROFILE-----------------------------------------------------
2831
2832 ----Private procedures
2833 PROCEDURE do_create_customer_profile (
2834 p_customer_profile_rec IN OUT NOCOPY HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE,
2835 p_create_profile_amt IN VARCHAR2,
2836 x_cust_account_profile_id OUT NOCOPY NUMBER,
2837 x_return_status IN OUT NOCOPY VARCHAR2
2838 );
2839
2840
2841 PROCEDURE do_create_cust_profile_amt (
2842 p_check_foreign_key IN VARCHAR2,
2843 p_cust_profile_amt_rec IN OUT NOCOPY HZ_CUSTOMER_PROFILE_V2PUB.CUST_PROFILE_AMT_REC_TYPE,
2844 x_cust_acct_profile_amt_id OUT NOCOPY NUMBER,
2845 x_return_status IN OUT NOCOPY VARCHAR2
2846 );
2847
2848 ---Definition
2849
2850 PROCEDURE do_create_customer_profile (
2851 p_customer_profile_rec IN OUT NOCOPY HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE,
2852 p_create_profile_amt IN VARCHAR2,
2853 x_cust_account_profile_id OUT NOCOPY NUMBER,
2854 x_return_status IN OUT NOCOPY VARCHAR2
2855 ) IS
2856
2857 l_is_first BOOLEAN := TRUE;
2858 l_msg_count NUMBER;
2859 l_msg_data VARCHAR2(2000);
2860
2861 l_status HZ_CUST_PROFILE_CLASSES.status%TYPE;
2862 l_profile_class_name HZ_CUST_PROFILE_CLASSES.name%TYPE;
2863 l_profile_class_id NUMBER;
2864
2865
2866 BEGIN
2867
2868 p_customer_profile_rec.cust_account_profile_id := null;
2869 p_customer_profile_rec.created_by_module := 'HZ_TCA_CUSTOMER_MERGE';
2870
2871 -- Call table-handler.
2872 -- Table_handler is taking care of default customer profile to profile class.
2873 -- HYU modification of Table Handler add column review_cycle, last_credit_review_date, party_id
2874
2875 HZ_CUSTOMER_PROFILES_PKG.Insert_Row (
2876 X_CUST_ACCOUNT_PROFILE_ID=> p_customer_profile_rec.cust_account_profile_id,
2877 X_CUST_ACCOUNT_ID => p_customer_profile_rec.cust_account_id,
2878 X_STATUS => p_customer_profile_rec.status,
2879 X_COLLECTOR_ID => p_customer_profile_rec.collector_id,
2880 X_CREDIT_ANALYST_ID => p_customer_profile_rec.credit_analyst_id,
2881 X_CREDIT_CHECKING => p_customer_profile_rec.credit_checking,
2882 X_NEXT_CREDIT_REVIEW_DATE=> p_customer_profile_rec.next_credit_review_date,
2883 X_TOLERANCE => p_customer_profile_rec.tolerance,
2884 X_DISCOUNT_TERMS => p_customer_profile_rec.discount_terms,
2885 X_DUNNING_LETTERS => p_customer_profile_rec.dunning_letters,
2886 X_INTEREST_CHARGES => p_customer_profile_rec.interest_charges,
2887 X_SEND_STATEMENTS => p_customer_profile_rec.send_statements,
2888 X_CREDIT_BALANCE_STATEMENTS=> p_customer_profile_rec.credit_balance_statements,
2889 X_CREDIT_HOLD => p_customer_profile_rec.credit_hold,
2890 X_PROFILE_CLASS_ID => p_customer_profile_rec.profile_class_id,
2891 X_SITE_USE_ID => p_customer_profile_rec.site_use_id,
2892 X_CREDIT_RATING => p_customer_profile_rec.credit_rating,
2893 X_RISK_CODE => p_customer_profile_rec.risk_code,
2894 X_STANDARD_TERMS => p_customer_profile_rec.standard_terms,
2895 X_OVERRIDE_TERMS => p_customer_profile_rec.override_terms,
2896 X_DUNNING_LETTER_SET_ID => p_customer_profile_rec.dunning_letter_set_id,
2897 X_INTEREST_PERIOD_DAYS => p_customer_profile_rec.interest_period_days,
2898 X_PAYMENT_GRACE_DAYS => p_customer_profile_rec.payment_grace_days,
2899 X_DISCOUNT_GRACE_DAYS => p_customer_profile_rec.discount_grace_days,
2900 X_STATEMENT_CYCLE_ID => p_customer_profile_rec.statement_cycle_id,
2901 X_ACCOUNT_STATUS => p_customer_profile_rec.account_status,
2902 X_PERCENT_COLLECTABLE => p_customer_profile_rec.percent_collectable,
2903 X_AUTOCASH_HIERARCHY_ID => p_customer_profile_rec.autocash_hierarchy_id,
2904 X_ATTRIBUTE_CATEGORY => p_customer_profile_rec.attribute_category,
2905 X_ATTRIBUTE1 => p_customer_profile_rec.attribute1,
2906 X_ATTRIBUTE2 => p_customer_profile_rec.attribute2,
2907 X_ATTRIBUTE3 => p_customer_profile_rec.attribute3,
2908 X_ATTRIBUTE4 => p_customer_profile_rec.attribute4,
2909 X_ATTRIBUTE5 => p_customer_profile_rec.attribute5,
2910 X_ATTRIBUTE6 => p_customer_profile_rec.attribute6,
2911 X_ATTRIBUTE7 => p_customer_profile_rec.attribute7,
2912 X_ATTRIBUTE8 => p_customer_profile_rec.attribute8,
2913 X_ATTRIBUTE9 => p_customer_profile_rec.attribute9,
2914 X_ATTRIBUTE10 => p_customer_profile_rec.attribute10,
2915 X_ATTRIBUTE11 => p_customer_profile_rec.attribute11,
2916 X_ATTRIBUTE12 => p_customer_profile_rec.attribute12,
2917 X_ATTRIBUTE13 => p_customer_profile_rec.attribute13,
2918 X_ATTRIBUTE14 => p_customer_profile_rec.attribute14,
2919 X_ATTRIBUTE15 => p_customer_profile_rec.attribute15,
2920 X_AUTO_REC_INCL_DISPUTED_FLAG => p_customer_profile_rec.auto_rec_incl_disputed_flag,
2921 X_TAX_PRINTING_OPTION => p_customer_profile_rec.tax_printing_option,
2922 X_CHARGE_ON_FINANCE_CHARGE_FG => p_customer_profile_rec.charge_on_finance_charge_flag,
2923 X_GROUPING_RULE_ID => p_customer_profile_rec.grouping_rule_id,
2924 X_CLEARING_DAYS => p_customer_profile_rec.clearing_days,
2925 X_JGZZ_ATTRIBUTE_CATEGORY=> p_customer_profile_rec.jgzz_attribute_category,
2926 X_JGZZ_ATTRIBUTE1 => p_customer_profile_rec.jgzz_attribute1,
2927 X_JGZZ_ATTRIBUTE2 => p_customer_profile_rec.jgzz_attribute2,
2928 X_JGZZ_ATTRIBUTE3 => p_customer_profile_rec.jgzz_attribute3,
2929 X_JGZZ_ATTRIBUTE4 => p_customer_profile_rec.jgzz_attribute4,
2930 X_JGZZ_ATTRIBUTE5 => p_customer_profile_rec.jgzz_attribute5,
2931 X_JGZZ_ATTRIBUTE6 => p_customer_profile_rec.jgzz_attribute6,
2932 X_JGZZ_ATTRIBUTE7 => p_customer_profile_rec.jgzz_attribute7,
2933 X_JGZZ_ATTRIBUTE8 => p_customer_profile_rec.jgzz_attribute8,
2934 X_JGZZ_ATTRIBUTE9 => p_customer_profile_rec.jgzz_attribute9,
2935 X_JGZZ_ATTRIBUTE10 => p_customer_profile_rec.jgzz_attribute10,
2936 X_JGZZ_ATTRIBUTE11 => p_customer_profile_rec.jgzz_attribute11,
2937 X_JGZZ_ATTRIBUTE12 => p_customer_profile_rec.jgzz_attribute12,
2938 X_JGZZ_ATTRIBUTE13 => p_customer_profile_rec.jgzz_attribute13,
2939 X_JGZZ_ATTRIBUTE14 => p_customer_profile_rec.jgzz_attribute14,
2940 X_JGZZ_ATTRIBUTE15 => p_customer_profile_rec.jgzz_attribute15,
2941 X_GLOBAL_ATTRIBUTE1 => p_customer_profile_rec.global_attribute1,
2942 X_GLOBAL_ATTRIBUTE2 => p_customer_profile_rec.global_attribute2,
2943 X_GLOBAL_ATTRIBUTE3 => p_customer_profile_rec.global_attribute3,
2944 X_GLOBAL_ATTRIBUTE4 => p_customer_profile_rec.global_attribute4,
2945 X_GLOBAL_ATTRIBUTE5 => p_customer_profile_rec.global_attribute5,
2946 X_GLOBAL_ATTRIBUTE6 => p_customer_profile_rec.global_attribute6,
2947 X_GLOBAL_ATTRIBUTE7 => p_customer_profile_rec.global_attribute7,
2948 X_GLOBAL_ATTRIBUTE8 => p_customer_profile_rec.global_attribute8,
2949 X_GLOBAL_ATTRIBUTE9 => p_customer_profile_rec.global_attribute9,
2950 X_GLOBAL_ATTRIBUTE10 => p_customer_profile_rec.global_attribute10,
2951 X_GLOBAL_ATTRIBUTE11 => p_customer_profile_rec.global_attribute11,
2952 X_GLOBAL_ATTRIBUTE12 => p_customer_profile_rec.global_attribute12,
2953 X_GLOBAL_ATTRIBUTE13 => p_customer_profile_rec.global_attribute13,
2954 X_GLOBAL_ATTRIBUTE14 => p_customer_profile_rec.global_attribute14,
2955 X_GLOBAL_ATTRIBUTE15 => p_customer_profile_rec.global_attribute15,
2956 X_GLOBAL_ATTRIBUTE16 => p_customer_profile_rec.global_attribute16,
2957 X_GLOBAL_ATTRIBUTE17 => p_customer_profile_rec.global_attribute17,
2958 X_GLOBAL_ATTRIBUTE18 => p_customer_profile_rec.global_attribute18,
2959 X_GLOBAL_ATTRIBUTE19 => p_customer_profile_rec.global_attribute19,
2960 X_GLOBAL_ATTRIBUTE20 => p_customer_profile_rec.global_attribute20,
2961 X_GLOBAL_ATTRIBUTE_CATEGORY=> p_customer_profile_rec.global_attribute_category,
2962 X_CONS_INV_FLAG => p_customer_profile_rec.cons_inv_flag,
2963 X_CONS_INV_TYPE => p_customer_profile_rec.cons_inv_type,
2964 X_AUTOCASH_HIERARCHY_ID_ADR => p_customer_profile_rec.autocash_hierarchy_id_for_adr,
2965 X_LOCKBOX_MATCHING_OPTION=> p_customer_profile_rec.lockbox_matching_option,
2966 X_OBJECT_VERSION_NUMBER => 1,
2967 X_CREATED_BY_MODULE => p_customer_profile_rec.created_by_module,
2968 X_APPLICATION_ID => p_customer_profile_rec.application_id,
2969 X_REVIEW_CYCLE => p_customer_profile_rec.review_cycle,
2970 X_LAST_CREDIT_REVIEW_DATE=> p_customer_profile_rec.last_credit_review_date,
2971 X_PARTY_ID => p_customer_profile_rec.party_id,
2972 X_CREDIT_CLASSIFICATION => p_customer_profile_rec.credit_classification,
2973 X_CONS_BILL_LEVEL => p_customer_profile_rec.cons_bill_level,
2974 X_LATE_CHARGE_CALCULATION_TRX => p_customer_profile_rec.late_charge_calculation_trx,
2975 X_CREDIT_ITEMS_FLAG => p_customer_profile_rec.credit_items_flag,
2976 X_DISPUTED_TRANSACTIONS_FLAG => p_customer_profile_rec.disputed_transactions_flag,
2977 X_LATE_CHARGE_TYPE => p_customer_profile_rec.late_charge_type,
2978 X_LATE_CHARGE_TERM_ID => p_customer_profile_rec.late_charge_term_id,
2979 X_INTEREST_CALCULATION_PERIOD => p_customer_profile_rec.interest_calculation_period,
2980 X_HOLD_CHARGED_INVOICES_FLAG => p_customer_profile_rec.hold_charged_invoices_flag,
2981 X_MESSAGE_TEXT_ID => p_customer_profile_rec.message_text_id,
2982 X_MULTIPLE_INTEREST_RATES_FLAG => p_customer_profile_rec.multiple_interest_rates_flag,
2983 X_CHARGE_BEGIN_DATE => p_customer_profile_rec.charge_begin_date
2984 );
2985
2986 x_cust_account_profile_id := p_customer_profile_rec.cust_account_profile_id;
2987
2988 -- No need to create profile amt as this logic is present in
2989 -- p_create_profile_amt is TRUE. Otherwise, simply return.
2990
2991 END do_create_customer_profile;
2992
2993
2994 PROCEDURE do_create_cust_profile_amt (
2995 p_check_foreign_key IN VARCHAR2,
2996 p_cust_profile_amt_rec IN OUT NOCOPY HZ_CUSTOMER_PROFILE_V2PUB.CUST_PROFILE_AMT_REC_TYPE,
2997 x_cust_acct_profile_amt_id OUT NOCOPY NUMBER,
2998 x_return_status IN OUT NOCOPY VARCHAR2
2999 ) IS
3000
3001
3002 BEGIN
3003
3004 p_cust_profile_amt_rec.created_by_module := 'HZ_TCA_CUSTOMER_MERGE';
3005 p_cust_profile_amt_rec.cust_acct_profile_amt_id := null;
3006
3007
3008 -- Call table-handler.
3009 HZ_CUST_PROFILE_AMTS_PKG.Insert_Row (
3010 X_CUST_ACCT_PROFILE_AMT_ID=>p_cust_profile_amt_rec.cust_acct_profile_amt_id,
3011 X_CUST_ACCOUNT_PROFILE_ID => p_cust_profile_amt_rec.cust_account_profile_id,
3012 X_CURRENCY_CODE => p_cust_profile_amt_rec.currency_code,
3013 X_TRX_CREDIT_LIMIT => p_cust_profile_amt_rec.trx_credit_limit,
3014 X_OVERALL_CREDIT_LIMIT => p_cust_profile_amt_rec.overall_credit_limit,
3015 X_MIN_DUNNING_AMOUNT => p_cust_profile_amt_rec.min_dunning_amount,
3016 X_MIN_DUNNING_INVOICE_AMOUNT=> p_cust_profile_amt_rec.min_dunning_invoice_amount,
3017 X_MAX_INTEREST_CHARGE => p_cust_profile_amt_rec.max_interest_charge,
3018 X_MIN_STATEMENT_AMOUNT => p_cust_profile_amt_rec.min_statement_amount,
3019 X_AUTO_REC_MIN_RECEIPT_AMOUNT => p_cust_profile_amt_rec.auto_rec_min_receipt_amount,
3020 X_INTEREST_RATE => p_cust_profile_amt_rec.interest_rate,
3021 X_ATTRIBUTE_CATEGORY => p_cust_profile_amt_rec.attribute_category,
3022 X_ATTRIBUTE1 => p_cust_profile_amt_rec.attribute1,
3023 X_ATTRIBUTE2 => p_cust_profile_amt_rec.attribute2,
3024 X_ATTRIBUTE3 => p_cust_profile_amt_rec.attribute3,
3025 X_ATTRIBUTE4 => p_cust_profile_amt_rec.attribute4,
3026 X_ATTRIBUTE5 => p_cust_profile_amt_rec.attribute5,
3027 X_ATTRIBUTE6 => p_cust_profile_amt_rec.attribute6,
3028 X_ATTRIBUTE7 => p_cust_profile_amt_rec.attribute7,
3029 X_ATTRIBUTE8 => p_cust_profile_amt_rec.attribute8,
3030 X_ATTRIBUTE9 => p_cust_profile_amt_rec.attribute9,
3031 X_ATTRIBUTE10 => p_cust_profile_amt_rec.attribute10,
3032 X_ATTRIBUTE11 => p_cust_profile_amt_rec.attribute11,
3033 X_ATTRIBUTE12 => p_cust_profile_amt_rec.attribute12,
3034 X_ATTRIBUTE13 => p_cust_profile_amt_rec.attribute13,
3035 X_ATTRIBUTE14 => p_cust_profile_amt_rec.attribute14,
3036 X_ATTRIBUTE15 => p_cust_profile_amt_rec.attribute15,
3037 X_MIN_FC_BALANCE_AMOUNT => p_cust_profile_amt_rec.min_fc_balance_amount,
3038 X_MIN_FC_INVOICE_AMOUNT => p_cust_profile_amt_rec.min_fc_invoice_amount,
3039 X_CUST_ACCOUNT_ID => p_cust_profile_amt_rec.cust_account_id,
3040 X_SITE_USE_ID => p_cust_profile_amt_rec.site_use_id,
3041 X_EXPIRATION_DATE => p_cust_profile_amt_rec.expiration_date,
3042 X_JGZZ_ATTRIBUTE_CATEGORY=> p_cust_profile_amt_rec.jgzz_attribute_category,
3043 X_JGZZ_ATTRIBUTE1 => p_cust_profile_amt_rec.jgzz_attribute1,
3044 X_JGZZ_ATTRIBUTE2 => p_cust_profile_amt_rec.jgzz_attribute2,
3045 X_JGZZ_ATTRIBUTE3 => p_cust_profile_amt_rec.jgzz_attribute3,
3046 X_JGZZ_ATTRIBUTE4 => p_cust_profile_amt_rec.jgzz_attribute4,
3047 X_JGZZ_ATTRIBUTE5 => p_cust_profile_amt_rec.jgzz_attribute5,
3048 X_JGZZ_ATTRIBUTE6 => p_cust_profile_amt_rec.jgzz_attribute6,
3049 X_JGZZ_ATTRIBUTE7 => p_cust_profile_amt_rec.jgzz_attribute7,
3050 X_JGZZ_ATTRIBUTE8 => p_cust_profile_amt_rec.jgzz_attribute8,
3051 X_JGZZ_ATTRIBUTE9 => p_cust_profile_amt_rec.jgzz_attribute9,
3052 X_JGZZ_ATTRIBUTE10 => p_cust_profile_amt_rec.jgzz_attribute10,
3053 X_JGZZ_ATTRIBUTE11 => p_cust_profile_amt_rec.jgzz_attribute11,
3054 X_JGZZ_ATTRIBUTE12 => p_cust_profile_amt_rec.jgzz_attribute12,
3055 X_JGZZ_ATTRIBUTE13 => p_cust_profile_amt_rec.jgzz_attribute13,
3056 X_JGZZ_ATTRIBUTE14 => p_cust_profile_amt_rec.jgzz_attribute14,
3057 X_JGZZ_ATTRIBUTE15 => p_cust_profile_amt_rec.jgzz_attribute15,
3058 X_GLOBAL_ATTRIBUTE1 => p_cust_profile_amt_rec.global_attribute1,
3059 X_GLOBAL_ATTRIBUTE2 => p_cust_profile_amt_rec.global_attribute2,
3060 X_GLOBAL_ATTRIBUTE3 => p_cust_profile_amt_rec.global_attribute3,
3061 X_GLOBAL_ATTRIBUTE4 => p_cust_profile_amt_rec.global_attribute4,
3062 X_GLOBAL_ATTRIBUTE5 => p_cust_profile_amt_rec.global_attribute5,
3063 X_GLOBAL_ATTRIBUTE6 => p_cust_profile_amt_rec.global_attribute6,
3064 X_GLOBAL_ATTRIBUTE7 => p_cust_profile_amt_rec.global_attribute7,
3065 X_GLOBAL_ATTRIBUTE8 => p_cust_profile_amt_rec.global_attribute8,
3066 X_GLOBAL_ATTRIBUTE9 => p_cust_profile_amt_rec.global_attribute9,
3067 X_GLOBAL_ATTRIBUTE10 => p_cust_profile_amt_rec.global_attribute10,
3068 X_GLOBAL_ATTRIBUTE11 => p_cust_profile_amt_rec.global_attribute11,
3069 X_GLOBAL_ATTRIBUTE12 => p_cust_profile_amt_rec.global_attribute12,
3070 X_GLOBAL_ATTRIBUTE13 => p_cust_profile_amt_rec.global_attribute13,
3071 X_GLOBAL_ATTRIBUTE14 => p_cust_profile_amt_rec.global_attribute14,
3072 X_GLOBAL_ATTRIBUTE15 => p_cust_profile_amt_rec.global_attribute15,
3073 X_GLOBAL_ATTRIBUTE16 => p_cust_profile_amt_rec.global_attribute16,
3074 X_GLOBAL_ATTRIBUTE17 => p_cust_profile_amt_rec.global_attribute17,
3075 X_GLOBAL_ATTRIBUTE18 => p_cust_profile_amt_rec.global_attribute18,
3076 X_GLOBAL_ATTRIBUTE19 => p_cust_profile_amt_rec.global_attribute19,
3077 X_GLOBAL_ATTRIBUTE20 => p_cust_profile_amt_rec.global_attribute20,
3078 X_GLOBAL_ATTRIBUTE_CATEGORY=> p_cust_profile_amt_rec.global_attribute_category,
3079 X_OBJECT_VERSION_NUMBER => 1,
3080 X_CREATED_BY_MODULE => p_cust_profile_amt_rec.created_by_module,
3081 X_APPLICATION_ID => p_cust_profile_amt_rec.application_id,
3082 X_EXCHANGE_RATE_TYPE => p_cust_profile_amt_rec.exchange_rate_type,
3083 X_MIN_FC_INVOICE_OVERDUE_TYPE => p_cust_profile_amt_rec.min_fc_invoice_overdue_type,
3084 X_MIN_FC_INVOICE_PERCENT => p_cust_profile_amt_rec.min_fc_invoice_percent,
3085 X_MIN_FC_BALANCE_OVERDUE_TYPE => p_cust_profile_amt_rec.min_fc_balance_overdue_type,
3086 X_MIN_FC_BALANCE_PERCENT => p_cust_profile_amt_rec.min_fc_balance_percent,
3087 X_INTEREST_TYPE => p_cust_profile_amt_rec.interest_type,
3088 X_INTEREST_FIXED_AMOUNT => p_cust_profile_amt_rec.interest_fixed_amount,
3089 X_INTEREST_SCHEDULE_ID => p_cust_profile_amt_rec.interest_schedule_id,
3090 X_PENALTY_TYPE => p_cust_profile_amt_rec.penalty_type,
3091 X_PENALTY_RATE => p_cust_profile_amt_rec.penalty_rate,
3092 X_MIN_INTEREST_CHARGE => p_cust_profile_amt_rec.min_interest_charge,
3093 X_PENALTY_FIXED_AMOUNT => p_cust_profile_amt_rec.penalty_fixed_amount,
3094 X_PENALTY_SCHEDULE_ID => p_cust_profile_amt_rec.penalty_schedule_id
3095 );
3096
3097 x_cust_acct_profile_amt_id := p_cust_profile_amt_rec.cust_acct_profile_amt_id;
3098
3099
3100 END do_create_cust_profile_amt;
3101
3102
3103
3104 --------------------------------------
3105 -- public procedures and functions
3106 --------------------------------------
3107 PROCEDURE create_customer_profile (
3108 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3109 p_customer_profile_rec IN HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE,
3110 p_create_profile_amt IN VARCHAR2 := FND_API.G_TRUE,
3111 x_cust_account_profile_id OUT NOCOPY NUMBER,
3112 x_return_status OUT NOCOPY VARCHAR2,
3113 x_msg_count OUT NOCOPY NUMBER,
3114 x_msg_data OUT NOCOPY VARCHAR2
3115 ) IS
3116
3117 l_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE
3118 := p_customer_profile_rec;
3119
3120 BEGIN
3121
3122 -- Standard start of API savepoint
3123 SAVEPOINT create_customer_profile;
3124
3125
3126 -- Initialize message list if p_init_msg_list is set to TRUE.
3127 IF FND_API.to_Boolean(p_init_msg_list) THEN
3128 FND_MSG_PUB.initialize;
3129 END IF;
3130
3131 -- Initialize API return status to success.
3132 x_return_status := FND_API.G_RET_STS_SUCCESS;
3133
3134 -- Call to business logic.
3135 do_create_customer_profile (
3136 l_customer_profile_rec,
3137 p_create_profile_amt,
3138 x_cust_account_profile_id,
3139 x_return_status );
3140
3141 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3142 -- Invoke business event system.
3143 HZ_BUSINESS_EVENT_V2PVT.create_customer_profile_event (
3144 l_customer_profile_rec,
3145 p_create_profile_amt );
3146 END IF;
3147
3148 -- Standard call to get message count and if count is 1, get message info.
3149 FND_MSG_PUB.Count_And_Get(
3150 p_encoded => FND_API.G_FALSE,
3151 p_count => x_msg_count,
3152 p_data => x_msg_data );
3153
3154
3155 EXCEPTION
3156 WHEN FND_API.G_EXC_ERROR THEN
3157 ROLLBACK TO create_customer_profile;
3158 x_return_status := FND_API.G_RET_STS_ERROR;
3159
3160 FND_MSG_PUB.Count_And_Get(
3161 p_encoded => FND_API.G_FALSE,
3162 p_count => x_msg_count,
3163 p_data => x_msg_data );
3164
3165
3166 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3167 ROLLBACK TO create_customer_profile;
3168 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3169
3170 FND_MSG_PUB.Count_And_Get(
3171 p_encoded => FND_API.G_FALSE,
3172 p_count => x_msg_count,
3173 p_data => x_msg_data );
3174
3175
3176 WHEN OTHERS THEN
3177 ROLLBACK TO create_customer_profile;
3178 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3179
3180 fnd_message.set_name( 'AR', 'HZ_API_OTHERS_EXCEP' );
3181 fnd_message.set_token( 'ERROR' ,SQLERRM );
3182 fnd_msg_pub.add;
3183
3184 FND_MSG_PUB.Count_And_Get(
3185 p_encoded => FND_API.G_FALSE,
3186 p_count => x_msg_count,
3187 p_data => x_msg_data );
3188
3189
3190 END create_customer_profile;
3191
3192
3193
3194 PROCEDURE create_cust_profile_amt (
3195 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3196 p_check_foreign_key IN VARCHAR2 := FND_API.G_TRUE,
3197 p_cust_profile_amt_rec IN HZ_CUSTOMER_PROFILE_V2PUB.CUST_PROFILE_AMT_REC_TYPE,
3198 x_cust_acct_profile_amt_id OUT NOCOPY NUMBER,
3199 x_return_status OUT NOCOPY VARCHAR2,
3200 x_msg_count OUT NOCOPY NUMBER,
3201 x_msg_data OUT NOCOPY VARCHAR2
3202 ) IS
3203
3204 l_cust_profile_amt_rec HZ_CUSTOMER_PROFILE_V2PUB.CUST_PROFILE_AMT_REC_TYPE :=
3205 p_cust_profile_amt_rec;
3206
3207 BEGIN
3208
3209 -- Standard start of API savepoint
3210 SAVEPOINT create_cust_profile_amt;
3211
3212 -- Initialize message list if p_init_msg_list is set to TRUE.
3213 IF FND_API.to_Boolean(p_init_msg_list) THEN
3214 FND_MSG_PUB.initialize;
3215 END IF;
3216
3217 -- Initialize API return status to success.
3218 x_return_status := FND_API.G_RET_STS_SUCCESS;
3219
3220 -- Call to business logic.
3221 do_create_cust_profile_amt (
3222 p_check_foreign_key,
3223 l_cust_profile_amt_rec,
3224 x_cust_acct_profile_amt_id,
3225 x_return_status );
3226
3227 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3228 -- Invoke business event system.
3229 HZ_BUSINESS_EVENT_V2PVT.create_cust_profile_amt_event (
3230 l_cust_profile_amt_rec );
3231 END IF;
3232
3233 -- Standard call to get message count and if count is 1, get message info.
3234 FND_MSG_PUB.Count_And_Get(
3235 p_encoded => FND_API.G_FALSE,
3236 p_count => x_msg_count,
3237 p_data => x_msg_data );
3238
3239
3240 EXCEPTION
3241 WHEN FND_API.G_EXC_ERROR THEN
3242 ROLLBACK TO create_cust_profile_amt;
3243 x_return_status := FND_API.G_RET_STS_ERROR;
3244
3245 FND_MSG_PUB.Count_And_Get(
3246 p_encoded => FND_API.G_FALSE,
3247 p_count => x_msg_count,
3248 p_data => x_msg_data );
3249
3250 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3251 ROLLBACK TO create_cust_profile_amt;
3252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3253
3254 FND_MSG_PUB.Count_And_Get(
3255 p_encoded => FND_API.G_FALSE,
3256 p_count => x_msg_count,
3257 p_data => x_msg_data );
3258
3259 WHEN OTHERS THEN
3260 ROLLBACK TO create_cust_profile_amt;
3261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3262
3263 fnd_message.set_name( 'AR', 'HZ_API_OTHERS_EXCEP' );
3264 fnd_message.set_token( 'ERROR' ,SQLERRM );
3265 fnd_msg_pub.add;
3266
3267 FND_MSG_PUB.Count_And_Get(
3268 p_encoded => FND_API.G_FALSE,
3269 p_count => x_msg_count,
3270 p_data => x_msg_data );
3271
3272 END create_cust_profile_amt;
3273
3274 ------------------------CONTACT_POINTS------------------------
3275
3276 --------------------------------------
3277 -- declaration of private procedures and functions
3278 --------------------------------------
3279
3280 PROCEDURE do_create_contact_point (
3281 p_contact_point_rec IN OUT NOCOPY hz_contact_point_v2pub.contact_point_rec_type,
3282 p_edi_rec IN OUT NOCOPY hz_contact_point_v2pub.edi_rec_type,
3283 p_eft_rec IN OUT NOCOPY hz_contact_point_v2pub.eft_rec_type,
3284 p_email_rec IN OUT NOCOPY hz_contact_point_v2pub.email_rec_type,
3285 p_phone_rec IN OUT NOCOPY hz_contact_point_v2pub.phone_rec_type,
3286 p_telex_rec IN OUT NOCOPY hz_contact_point_v2pub.telex_rec_type,
3287 p_web_rec IN OUT NOCOPY hz_contact_point_v2pub.web_rec_type,
3288 x_contact_point_id OUT NOCOPY NUMBER,
3289 x_return_status IN OUT NOCOPY VARCHAR2
3290 );
3291
3292 PROCEDURE do_denormalize_contact_point (
3293 p_party_id IN NUMBER,
3294 p_contact_point_type IN VARCHAR2,
3295 p_url IN VARCHAR2,
3296 p_email_address IN VARCHAR2
3297 );
3298
3299 PROCEDURE do_unset_prim_contact_point (
3300 p_owner_table_name IN VARCHAR2,
3301 p_owner_table_id IN NUMBER,
3302 p_contact_point_type IN VARCHAR2,
3303 p_contact_point_id IN NUMBER
3304 );
3305
3306 PROCEDURE do_unset_primary_by_purpose (
3307 p_owner_table_name IN VARCHAR2,
3308 p_owner_table_id IN NUMBER,
3309 p_contact_point_type IN VARCHAR2,
3310 p_contact_point_purpose IN VARCHAR2,
3311 p_contact_point_id IN NUMBER
3312 );
3313
3314 --------------------------------------
3315 -- body of private procedures
3316 --------------------------------------
3317
3318
3319 PROCEDURE do_create_contact_point (
3320 p_contact_point_rec IN OUT NOCOPY hz_contact_point_v2pub.contact_point_rec_type,
3321 p_edi_rec IN OUT NOCOPY hz_contact_point_v2pub.edi_rec_type,
3322 p_eft_rec IN OUT NOCOPY hz_contact_point_v2pub.eft_rec_type,
3323 p_email_rec IN OUT NOCOPY hz_contact_point_v2pub.email_rec_type,
3324 p_phone_rec IN OUT NOCOPY hz_contact_point_v2pub.phone_rec_type,
3325 p_telex_rec IN OUT NOCOPY hz_contact_point_v2pub.telex_rec_type,
3326 p_web_rec IN OUT NOCOPY hz_contact_point_v2pub.web_rec_type,
3327 x_contact_point_id OUT NOCOPY NUMBER,
3328 x_return_status IN OUT NOCOPY VARCHAR2
3329 ) IS
3330
3331 l_dummy VARCHAR2(1);
3332 l_message_count NUMBER;
3333 l_msg_count NUMBER;
3334 l_msg_data VARCHAR2(2000);
3335
3336 l_formatted_phone_number VARCHAR2(100);
3337 l_country_code hz_locations.country%TYPE;
3338 l_transposed_phone_number hz_contact_points.transposed_phone_number%TYPE;
3339
3340 l_edi_rec hz_contact_point_v2pub.edi_rec_type;
3341 l_eft_rec hz_contact_point_v2pub.eft_rec_type;
3342 l_email_rec hz_contact_point_v2pub.email_rec_type;
3343 l_phone_rec hz_contact_point_v2pub.phone_rec_type;
3344 l_telex_rec hz_contact_point_v2pub.telex_rec_type;
3345 l_web_rec hz_contact_point_v2pub.web_rec_type;
3346 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
3347
3348 -- Bug 2197181: added for mix-n-match project: the contact point
3349 -- must be visible.
3350
3351 -- SSM SST Integration and Extension
3352 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
3353 -- There is no need to check if the data-source is selected.
3354
3355 CURSOR c_cp (p_owner_table_name IN VARCHAR2,
3356 p_owner_table_id IN NUMBER,
3357 p_contact_point_type IN VARCHAR2) IS
3358 SELECT 'Y'
3359 FROM hz_contact_points
3360 WHERE owner_table_name = p_owner_table_name
3361 AND owner_table_id = p_owner_table_id
3362 AND contact_point_type = p_contact_point_type
3363 /* AND HZ_MIXNM_UTILITY.isDataSourceSelected (
3364 g_cpt_selected_datasources, actual_content_source ) = 'Y'*/
3365 AND status = 'A'
3366 AND rownum = 1;
3367
3368
3369 BEGIN
3370
3371 p_contact_point_rec.created_by_module := 'HZ_TCA_CUSTOMER_MERGE';
3372 p_contact_point_rec.contact_point_id := NULL;
3373 p_contact_point_rec.orig_system_reference := NULL;
3374
3375 IF p_contact_point_rec.contact_point_type = 'EDI' THEN
3376 l_edi_rec := p_edi_rec;
3377 ELSIF p_contact_point_rec.contact_point_type = 'EFT' THEN
3378 l_eft_rec := p_eft_rec;
3379 ELSIF p_contact_point_rec.contact_point_type = 'EMAIL' THEN
3380 l_email_rec := p_email_rec;
3381 ELSIF p_contact_point_rec.contact_point_type = 'PHONE' THEN
3382 l_phone_rec := p_phone_rec;
3383 ELSIF p_contact_point_rec.contact_point_type = 'TLX' THEN
3384 l_telex_rec := p_telex_rec;
3385 ELSIF p_contact_point_rec.contact_point_type = 'WEB' THEN
3386 l_web_rec := p_web_rec;
3387 ELSE
3388 l_edi_rec := p_edi_rec;
3389 l_eft_rec := p_eft_rec;
3390 l_email_rec := p_email_rec;
3391 l_phone_rec := p_phone_rec;
3392 l_telex_rec := p_telex_rec;
3393 l_web_rec := p_web_rec;
3394 END IF;
3395
3396
3397 -- If this is the first active contact point for the combination of
3398 -- owner_table_name, owner_table_id, contact_point_type, we need to
3399 -- mark it as primary no matter the value of primary_flag,
3400 -- If primary_flag = 'Y', we need to unmark the previous primary.
3401 -- Please note, if status is NULL or MISSING, we treat it as 'A'
3402 -- and in validation part, we already checked that primary_flag = 'Y'
3403 -- and status = 'I' can not both be set.
3404
3405 -- Bug 2197181: added for mix-n-match project: the primary flag
3406 -- can be set to 'Y' only if the contact point will be visible. If
3407 -- it is not visible, the flag must be reset to 'N'.
3408
3409
3410 IF p_contact_point_rec.status IS NULL OR
3411 p_contact_point_rec.status = fnd_api.g_miss_char OR
3412 p_contact_point_rec.status = 'A'
3413 THEN
3414 IF p_contact_point_rec.primary_flag = 'Y' THEN
3415 -- Bug 2197181: added for mix-n-match project
3416
3417 -- SSM SST Integration and Extension
3418 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
3419 -- There is no need to check if the data-source is selected.
3420
3421 -- IF g_cpt_is_datasource_selected = 'Y' THEN
3422 -- Unmark previous primary contact point.
3423 do_unset_prim_contact_point(p_contact_point_rec.owner_table_name,
3424 p_contact_point_rec.owner_table_id,
3425 p_contact_point_rec.contact_point_type,
3426 p_contact_point_rec.contact_point_id);
3427 -- ELSE
3428 -- p_contact_point_rec.primary_flag := 'N';
3429 -- END IF;
3430 ELSE
3431 -- Bug 2117973: modified to conform to Applications PL/SQL standards.
3432 OPEN c_cp (p_contact_point_rec.owner_table_name,
3433 p_contact_point_rec.owner_table_id,
3434 p_contact_point_rec.contact_point_type);
3435 FETCH c_cp INTO l_dummy;
3436
3437 IF c_cp%NOTFOUND /*AND
3438 -- Bug 2197181: added for mix-n-match project
3439 g_cpt_is_datasource_selected = 'Y'*/
3440 THEN
3441 -- First active and visible contact point per type for this entity
3442 p_contact_point_rec.primary_flag := 'Y';
3443 ELSE
3444 p_contact_point_rec.primary_flag := 'N';
3445 END IF;
3446 CLOSE c_cp;
3447 END IF;
3448
3449 -- De-normalize primary contact point to hz_parties.
3450 -- url is mandatory if contact_point_type = 'WEB'.
3451 -- email_address is mandatory if contact_point_type = 'EMAIL'.
3452
3453 IF p_contact_point_rec.primary_flag = 'Y' AND
3454 p_contact_point_rec.owner_table_name = 'HZ_PARTIES' AND
3455 (p_contact_point_rec.contact_point_type = 'WEB' OR
3456 p_contact_point_rec.contact_point_type = 'EMAIL')
3457 THEN
3458 do_denormalize_contact_point(p_contact_point_rec.owner_table_id,
3459 p_contact_point_rec.contact_point_type,
3460 l_web_rec.url,
3461 l_email_rec.email_address);
3462 END IF;
3463 END IF;
3464
3465 -- There is only one primary per purpose contact point exist for
3466 -- the combination of owner_table_name, owner_table_id, contact_point_type
3467 -- and contact_point_purpose. If primary_by_purpose is set to 'Y',
3468 -- we need to unset the previous primary per purpose contact point to
3469 -- non-primary. Since setting primary_by_purpose is only making
3470 -- sense when contact_point_purpose has some value, we ignore
3471 -- the primary_by_purpose (setting it to 'N') if contact_point_purpose
3472 -- is NULL.
3473
3474 -- Bug 2197181: added for mix-n-match project: the primary by purpose
3475 -- flag can be set to 'Y' only if the contact point will be visible.
3476 -- If it is not visible, the flag must be reset to 'N'.
3477
3478 IF p_contact_point_rec.contact_point_purpose IS NOT NULL AND
3479 p_contact_point_rec.contact_point_purpose <> fnd_api.g_miss_char
3480 THEN
3481 IF p_contact_point_rec.primary_by_purpose = 'Y' THEN
3482 -- Bug 2197181: added for mix-n-match project
3483
3484 -- SSM SST Integration and Extension
3485 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
3486 -- There is no need to check if the data-source is selected.
3487
3488 -- IF g_cpt_is_datasource_selected = 'Y' THEN
3489 do_unset_primary_by_purpose (p_contact_point_rec.owner_table_name,
3490 p_contact_point_rec.owner_table_id,
3491 p_contact_point_rec.contact_point_type,
3492 p_contact_point_rec.contact_point_purpose,
3493 p_contact_point_rec.contact_point_id);
3494 -- ELSE
3495 -- p_contact_point_rec.primary_by_purpose := 'N';
3496 -- END IF;
3497 END IF;
3498 ELSE
3499 p_contact_point_rec.primary_by_purpose := 'N';
3500 END IF;
3501
3502 -- Call table-handler.
3503 hz_contact_points_pkg.insert_row (
3504 x_contact_point_id => p_contact_point_rec.contact_point_id,
3505 x_contact_point_type => p_contact_point_rec.contact_point_type,
3506 x_status => p_contact_point_rec.status,
3507 x_owner_table_name => p_contact_point_rec.owner_table_name,
3508 x_owner_table_id => p_contact_point_rec.owner_table_id,
3509 x_primary_flag => p_contact_point_rec.primary_flag,
3510 x_orig_system_reference => p_contact_point_rec.orig_system_reference,
3511 x_attribute_category => p_contact_point_rec.attribute_category,
3512 x_attribute1 => p_contact_point_rec.attribute1,
3513 x_attribute2 => p_contact_point_rec.attribute2,
3514 x_attribute3 => p_contact_point_rec.attribute3,
3515 x_attribute4 => p_contact_point_rec.attribute4,
3516 x_attribute5 => p_contact_point_rec.attribute5,
3517 x_attribute6 => p_contact_point_rec.attribute6,
3518 x_attribute7 => p_contact_point_rec.attribute7,
3519 x_attribute8 => p_contact_point_rec.attribute8,
3520 x_attribute9 => p_contact_point_rec.attribute9,
3521 x_attribute10 => p_contact_point_rec.attribute10,
3522 x_attribute11 => p_contact_point_rec.attribute11,
3523 x_attribute12 => p_contact_point_rec.attribute12,
3524 x_attribute13 => p_contact_point_rec.attribute13,
3525 x_attribute14 => p_contact_point_rec.attribute14,
3526 x_attribute15 => p_contact_point_rec.attribute15,
3527 x_attribute16 => p_contact_point_rec.attribute16,
3528 x_attribute17 => p_contact_point_rec.attribute17,
3529 x_attribute18 => p_contact_point_rec.attribute18,
3530 x_attribute19 => p_contact_point_rec.attribute19,
3531 x_attribute20 => p_contact_point_rec.attribute20,
3532 x_edi_transaction_handling => l_edi_rec.edi_transaction_handling,
3533 x_edi_id_number => l_edi_rec.edi_id_number,
3534 x_edi_payment_method => l_edi_rec.edi_payment_method,
3535 x_edi_payment_format => l_edi_rec.edi_payment_format,
3536 x_edi_remittance_method => l_edi_rec.edi_remittance_method,
3537 x_edi_remittance_instruction => l_edi_rec.edi_remittance_instruction,
3538 x_edi_tp_header_id => l_edi_rec.edi_tp_header_id,
3539 x_edi_ece_tp_location_code => l_edi_rec.edi_ece_tp_location_code,
3540 x_eft_transmission_program_id => l_eft_rec.eft_transmission_program_id,
3541 x_eft_printing_program_id => l_eft_rec.eft_printing_program_id,
3542 x_eft_user_number => l_eft_rec.eft_user_number,
3543 x_eft_swift_code => l_eft_rec.eft_swift_code,
3544 x_email_format => l_email_rec.email_format,
3545 x_email_address => l_email_rec.email_address,
3546 x_phone_calling_calendar => l_phone_rec.phone_calling_calendar,
3547 x_last_contact_dt_time => l_phone_rec.last_contact_dt_time,
3548 x_timezone_id => l_phone_rec.timezone_id,
3549 x_phone_area_code => l_phone_rec.phone_area_code,
3550 x_phone_country_code => l_phone_rec.phone_country_code,
3551 x_phone_number => l_phone_rec.phone_number,
3552 x_phone_extension => l_phone_rec.phone_extension,
3553 x_phone_line_type => l_phone_rec.phone_line_type,
3554 x_telex_number => l_telex_rec.telex_number,
3555 x_web_type => l_web_rec.web_type,
3556 x_url => l_web_rec.url,
3557 x_content_source_type => p_contact_point_rec.content_source_type,
3558 x_raw_phone_number => l_phone_rec.raw_phone_number,
3559 x_object_version_number => 1,
3560 x_contact_point_purpose => p_contact_point_rec.contact_point_purpose,
3561 x_primary_by_purpose => p_contact_point_rec.primary_by_purpose,
3562 x_created_by_module => p_contact_point_rec.created_by_module,
3563 x_application_id => p_contact_point_rec.application_id,
3564 x_transposed_phone_number => l_transposed_phone_number,
3565 x_actual_content_source => p_contact_point_rec.actual_content_source
3566 );
3567
3568 x_contact_point_id := p_contact_point_rec.contact_point_id;
3569 /*
3570 per HLD,mosr record should not be created for copy case, since old osr is still active
3571 hz_orig_system_ref_pvt.create_mosr_for_merge(
3572 FND_API.G_FALSE,
3573 'HZ_CONTACT_POINTS',
3574 p_contact_point_rec.contact_point_id,
3575 x_return_status,
3576 l_msg_count,
3577 l_msg_data);
3578
3579 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3580 RAISE FND_API.G_EXC_ERROR;
3581 END IF;
3582 */
3583 END do_create_contact_point;
3584
3585 PROCEDURE do_unset_prim_contact_point (
3586 p_owner_table_name IN VARCHAR2,
3587 p_owner_table_id IN NUMBER,
3588 p_contact_point_type IN VARCHAR2,
3589 p_contact_point_id IN NUMBER
3590 ) IS
3591
3592 l_contact_point_id NUMBER;
3593
3594 BEGIN
3595
3596
3597 -- Check during insert.
3598 IF p_contact_point_id IS NULL THEN
3599 l_contact_point_id := fnd_api.g_miss_num;
3600 ELSE
3601 l_contact_point_id := p_contact_point_id;
3602 END IF;
3603
3604 UPDATE hz_contact_points
3605 SET primary_flag = 'N'
3606 WHERE owner_table_name = p_owner_table_name
3607 AND owner_table_id = p_owner_table_id
3608 AND contact_point_type = p_contact_point_type
3609 AND contact_point_id <> l_contact_point_id
3610 -- AND content_source_type = hz_party_v2pub.g_miss_content_source_type
3611 AND primary_flag = 'Y';
3612
3613
3614 END do_unset_prim_contact_point;
3615
3616
3617 PROCEDURE do_denormalize_contact_point (
3618 p_party_id IN NUMBER,
3619 p_contact_point_type IN VARCHAR2,
3620 p_url IN VARCHAR2,
3621 p_email_address IN VARCHAR2
3622 ) IS
3623 BEGIN
3624 IF p_contact_point_type = 'WEB' THEN
3625 UPDATE hz_parties
3626 SET url = p_url
3627 WHERE party_id = p_party_id;
3628 ELSIF p_contact_point_type = 'EMAIL' THEN
3629 UPDATE hz_parties
3630 SET email_address = p_email_address
3631 WHERE party_id = p_party_id;
3632 END IF;
3633 END do_denormalize_contact_point;
3634
3635 PROCEDURE do_unset_primary_by_purpose (
3636 p_owner_table_name IN VARCHAR2,
3637 p_owner_table_id IN NUMBER,
3638 p_contact_point_type IN VARCHAR2,
3639 p_contact_point_purpose IN VARCHAR2,
3640 p_contact_point_id IN NUMBER
3641 ) IS
3642
3643 l_contact_point_id NUMBER;
3644
3645 BEGIN
3646
3647
3648 -- Check during insert.
3649 IF p_contact_point_id IS NULL THEN
3650 l_contact_point_id := FND_API.G_MISS_NUM;
3651 ELSE
3652 l_contact_point_id := p_contact_point_id;
3653 END IF;
3654
3655 UPDATE hz_contact_points
3656 SET primary_by_purpose = 'N'
3657 WHERE owner_table_name = p_owner_table_name
3658 AND owner_table_id = p_owner_table_id
3659 AND contact_point_type = p_contact_point_type
3660 AND contact_point_purpose = p_contact_point_purpose
3661 AND contact_point_id <> l_contact_point_id
3662 -- AND content_source_type = hz_party_v2pub.g_miss_content_source_type
3663 AND primary_by_purpose = 'Y';
3664
3665 END do_unset_primary_by_purpose;
3666
3667 --------------------------------------
3668 -- public procedures and functions
3669 --------------------------------------
3670
3671
3672 PROCEDURE create_contact_point (
3673 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3674 p_contact_point_rec IN hz_contact_point_v2pub.contact_point_rec_type,
3675 p_edi_rec IN hz_contact_point_v2pub.edi_rec_type := g_miss_edi_rec,
3676 p_eft_rec IN hz_contact_point_v2pub.eft_rec_type := g_miss_eft_rec,
3677 p_email_rec IN hz_contact_point_v2pub.email_rec_type := g_miss_email_rec,
3678 p_phone_rec IN hz_contact_point_v2pub.phone_rec_type := g_miss_phone_rec,
3679 p_telex_rec IN hz_contact_point_v2pub.telex_rec_type := g_miss_telex_rec,
3680 p_web_rec IN hz_contact_point_v2pub.web_rec_type := g_miss_web_rec,
3681 x_contact_point_id OUT NOCOPY NUMBER,
3682 x_return_status OUT NOCOPY VARCHAR2,
3683 x_msg_count OUT NOCOPY NUMBER,
3684 x_msg_data OUT NOCOPY VARCHAR2
3685 ) IS
3686
3687 l_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type :=
3688 p_contact_point_rec;
3689 l_edi_rec hz_contact_point_v2pub.edi_rec_type := p_edi_rec;
3690 l_eft_rec hz_contact_point_v2pub.eft_rec_type := p_eft_rec;
3691 l_email_rec hz_contact_point_v2pub.email_rec_type := p_email_rec;
3692 l_phone_rec hz_contact_point_v2pub.phone_rec_type := p_phone_rec;
3693 l_telex_rec hz_contact_point_v2pub.telex_rec_type := p_telex_rec;
3694 l_web_rec hz_contact_point_v2pub.web_rec_type := p_web_rec;
3695
3696 BEGIN
3697
3698 -- Standard start of API savepoint
3699 SAVEPOINT create_contact_point;
3700
3701 -- Initialize API return status to success.
3702 x_return_status := fnd_api.g_ret_sts_success;
3703
3704 /* SSM SST Integration and Extension
3705 * For non-profile entities, the concept of select/de-select data-sources is obsoleted.
3706
3707 IF g_cpt_mixnmatch_enabled IS NULL THEN
3708 HZ_MIXNM_UTILITY.LoadDataSources(
3709 p_entity_name => 'HZ_CONTACT_POINTS',
3710 p_entity_attr_id => g_cpt_entity_attr_id,
3711 p_mixnmatch_enabled => g_cpt_mixnmatch_enabled,
3712 p_selected_datasources => g_cpt_selected_datasources );
3713 END IF;
3714 */
3715
3716 HZ_MIXNM_UTILITY.AssignDataSourceDuringCreation (
3717 p_entity_name => 'HZ_CONTACT_POINTS',
3718 p_entity_attr_id => g_cpt_entity_attr_id,
3719 p_mixnmatch_enabled => g_cpt_mixnmatch_enabled,
3720 p_selected_datasources => g_cpt_selected_datasources,
3721 p_content_source_type => l_contact_point_rec.content_source_type,
3722 p_actual_content_source => l_contact_point_rec.actual_content_source,
3723 x_is_datasource_selected => g_cpt_is_datasource_selected,
3724 x_return_status => x_return_status );
3725
3726
3727
3728 -- Call to business logic.
3729 do_create_contact_point(l_contact_point_rec,
3730 l_edi_rec,
3731 l_eft_rec,
3732 l_email_rec,
3733 l_phone_rec,
3734 l_telex_rec,
3735 l_web_rec,
3736 x_contact_point_id,
3737 x_return_status);
3738
3739 IF x_return_status = fnd_api.g_ret_sts_success THEN
3740 -- Invoke business event system.
3741 hz_business_event_v2pvt.create_contact_point_event(
3742 l_contact_point_rec,
3743 l_edi_rec,
3744 l_eft_rec,
3745 l_email_rec,
3746 l_phone_rec,
3747 l_telex_rec,
3748 l_web_rec);
3749 END IF;
3750
3751 -- Call to indicate contact point creation to DQM
3752 hz_dqm_sync.sync_contact_point(l_contact_point_rec.contact_point_id, 'C');
3753
3754 -- Standard call to get message count and if count is 1, get message info.
3755 fnd_msg_pub.count_and_get(
3756 p_encoded => fnd_api.g_false,
3757 p_count => x_msg_count,
3758 p_data => x_msg_data);
3759
3760
3761 EXCEPTION
3762 WHEN fnd_api.g_exc_error THEN
3763 ROLLBACK TO create_contact_point;
3764 x_return_status := fnd_api.g_ret_sts_error;
3765
3766 fnd_msg_pub.count_and_get(
3767 p_encoded => fnd_api.g_false,
3768 p_count => x_msg_count,
3769 p_data => x_msg_data);
3770
3771 WHEN fnd_api.g_exc_unexpected_error THEN
3772 ROLLBACK TO create_contact_point;
3773 x_return_status := fnd_api.g_ret_sts_unexp_error;
3774
3775 fnd_msg_pub.count_and_get(
3776 p_encoded => fnd_api.g_false,
3777 p_count => x_msg_count,
3778 p_data => x_msg_data);
3779
3780 WHEN OTHERS THEN
3781 ROLLBACK TO create_contact_point;
3782 x_return_status := fnd_api.g_ret_sts_unexp_error;
3783
3784 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
3785 fnd_message.set_token('ERROR' ,SQLERRM);
3786 fnd_msg_pub.add;
3787
3788 fnd_msg_pub.count_and_get(
3789 p_encoded => fnd_api.g_false,
3790 p_count => x_msg_count,
3791 p_data => x_msg_data);
3792
3793 END create_contact_point;
3794
3795
3796 PROCEDURE get_contact_point_rec (
3797 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3798 p_contact_point_id IN NUMBER,
3799 x_contact_point_rec OUT NOCOPY hz_contact_point_v2pub.contact_point_rec_type,
3800 x_edi_rec OUT NOCOPY hz_contact_point_v2pub.edi_rec_type,
3801 x_eft_rec OUT NOCOPY hz_contact_point_v2pub.eft_rec_type,
3802 x_email_rec OUT NOCOPY hz_contact_point_v2pub.email_rec_type,
3803 x_phone_rec OUT NOCOPY hz_contact_point_v2pub.phone_rec_type,
3804 x_telex_rec OUT NOCOPY hz_contact_point_v2pub.telex_rec_type,
3805 x_web_rec OUT NOCOPY hz_contact_point_v2pub.web_rec_type,
3806 x_return_status OUT NOCOPY VARCHAR2,
3807 x_msg_count OUT NOCOPY NUMBER,
3808 x_msg_data OUT NOCOPY VARCHAR2
3809 ) IS
3810
3811 l_transposed_phone_number hz_contact_points.transposed_phone_number%TYPE;
3812
3813 BEGIN
3814
3815 -- Initialize API return status to success.
3816 x_return_status := fnd_api.g_ret_sts_success;
3817
3818 -- Check whether primary key has been passed in.
3819 IF p_contact_point_id IS NULL OR
3820 p_contact_point_id = FND_API.G_MISS_NUM THEN
3821 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
3822 fnd_message.set_token('COLUMN', 'contact_point_id');
3823 fnd_msg_pub.add;
3824 RAISE fnd_api.g_exc_error;
3825 END IF;
3826
3827 x_contact_point_rec.contact_point_id := p_contact_point_id;
3828
3829 -- Call table-handler
3830 hz_contact_points_pkg.select_row(
3831 x_contact_point_id => x_contact_point_rec.contact_point_id,
3832 x_contact_point_type => x_contact_point_rec.contact_point_type,
3833 x_status => x_contact_point_rec.status,
3834 x_owner_table_name => x_contact_point_rec.owner_table_name,
3835 x_owner_table_id => x_contact_point_rec.owner_table_id,
3836 x_primary_flag => x_contact_point_rec.primary_flag,
3837 x_orig_system_reference => x_contact_point_rec.orig_system_reference,
3838 x_attribute_category => x_contact_point_rec.attribute_category,
3839 x_attribute1 => x_contact_point_rec.attribute1,
3840 x_attribute2 => x_contact_point_rec.attribute2,
3841 x_attribute3 => x_contact_point_rec.attribute3,
3842 x_attribute4 => x_contact_point_rec.attribute4,
3843 x_attribute5 => x_contact_point_rec.attribute5,
3844 x_attribute6 => x_contact_point_rec.attribute6,
3845 x_attribute7 => x_contact_point_rec.attribute7,
3846 x_attribute8 => x_contact_point_rec.attribute8,
3847 x_attribute9 => x_contact_point_rec.attribute9,
3848 x_attribute10 => x_contact_point_rec.attribute10,
3849 x_attribute11 => x_contact_point_rec.attribute11,
3850 x_attribute12 => x_contact_point_rec.attribute12,
3851 x_attribute13 => x_contact_point_rec.attribute13,
3852 x_attribute14 => x_contact_point_rec.attribute14,
3853 x_attribute15 => x_contact_point_rec.attribute15,
3854 x_attribute16 => x_contact_point_rec.attribute16,
3855 x_attribute17 => x_contact_point_rec.attribute17,
3856 x_attribute18 => x_contact_point_rec.attribute18,
3857 x_attribute19 => x_contact_point_rec.attribute19,
3858 x_attribute20 => x_contact_point_rec.attribute20,
3859 x_edi_transaction_handling => x_edi_rec.edi_transaction_handling,
3860 x_edi_id_number => x_edi_rec.edi_id_number,
3861 x_edi_payment_method => x_edi_rec.edi_payment_method,
3862 x_edi_payment_format => x_edi_rec.edi_payment_format,
3863 x_edi_remittance_method => x_edi_rec.edi_remittance_method,
3864 x_edi_remittance_instruction => x_edi_rec.edi_remittance_instruction,
3865 x_edi_tp_header_id => x_edi_rec.edi_tp_header_id,
3866 x_edi_ece_tp_location_code => x_edi_rec.edi_ece_tp_location_code,
3867 x_eft_transmission_program_id => x_eft_rec.eft_transmission_program_id,
3868 x_eft_printing_program_id => x_eft_rec.eft_printing_program_id,
3869 x_eft_user_number => x_eft_rec.eft_user_number,
3870 x_eft_swift_code => x_eft_rec.eft_swift_code,
3871 x_email_format => x_email_rec.email_format,
3872 x_email_address => x_email_rec.email_address,
3873 x_phone_calling_calendar => x_phone_rec.phone_calling_calendar,
3874 x_last_contact_dt_time => x_phone_rec.last_contact_dt_time,
3875 x_timezone_id => x_phone_rec.timezone_id,
3876 x_phone_area_code => x_phone_rec.phone_area_code,
3877 x_phone_country_code => x_phone_rec.phone_country_code,
3878 x_phone_number => x_phone_rec.phone_number,
3879 x_phone_extension => x_phone_rec.phone_extension,
3880 x_phone_line_type => x_phone_rec.phone_line_type,
3881 x_telex_number => x_telex_rec.telex_number,
3882 x_web_type => x_web_rec.web_type,
3883 x_url => x_web_rec.url,
3884 x_content_source_type => x_contact_point_rec.content_source_type,
3885 x_raw_phone_number => x_phone_rec.raw_phone_number,
3886 x_contact_point_purpose => x_contact_point_rec.contact_point_purpose,
3887 x_primary_by_purpose => x_contact_point_rec.primary_by_purpose,
3888 x_created_by_module => x_contact_point_rec.created_by_module,
3889 x_application_id => x_contact_point_rec.application_id,
3890 x_transposed_phone_number => l_transposed_phone_number,
3891 x_actual_content_source => x_contact_point_rec.actual_content_source
3892 );
3893
3894
3895 -- Standard call to get message count and if count is 1, get message info.
3896 fnd_msg_pub.count_and_get(
3897 p_encoded => fnd_api.g_false,
3898 p_count => x_msg_count,
3899 p_data => x_msg_data);
3900
3901 EXCEPTION
3902 WHEN fnd_api.g_exc_error THEN
3903 x_return_status := fnd_api.g_ret_sts_error;
3904
3905 fnd_msg_pub.count_and_get(
3906 p_encoded => fnd_api.g_false,
3907 p_count => x_msg_count,
3908 p_data => x_msg_data);
3909
3910 WHEN fnd_api.g_exc_unexpected_error THEN
3911 x_return_status := fnd_api.g_ret_sts_unexp_error;
3912
3913 fnd_msg_pub.count_and_get(
3914 p_encoded => fnd_api.g_false,
3915 p_count => x_msg_count,
3916 p_data => x_msg_data);
3917
3918 WHEN OTHERS THEN
3919 x_return_status := fnd_api.g_ret_sts_unexp_error;
3920
3921 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
3922 fnd_message.set_token('ERROR' ,SQLERRM);
3923 fnd_msg_pub.add;
3924
3925 fnd_msg_pub.count_and_get(
3926 p_encoded => fnd_api.g_false,
3927 p_count => x_msg_count,
3928 p_data => x_msg_data);
3929
3930 END get_contact_point_rec;
3931
3932 ------------------ PARTY_SITE_USE -------------------------------------
3933
3934
3935
3936 /*===========================================================================+
3937 | PROCEDURE
3938 | get_party_site_use_rec
3939 |
3940 | DESCRIPTION
3941 | Gets current record.
3942 |
3943 | SCOPE - PRIVATE
3944 |
3945 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3946 |
3947 | ARGUMENTS : IN:
3948 | p_init_msg_list
3949 | p_party_site_id
3950 | OUT:
3951 | x_party_site_rec
3952 | x_return_status
3953 | x_msg_count
3954 | x_msg_data
3955 | IN/ OUT:
3956 |
3957 | RETURNS : NONE
3958 |
3959 | NOTES
3960 |
3961 | MODIFICATION HISTORY
3962 |
3963 +===========================================================================*/
3964
3965 PROCEDURE get_party_site_use_rec (
3966 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3967 p_party_site_use_id IN NUMBER,
3968 x_party_site_use_rec OUT NOCOPY HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_REC_TYPE,
3969 x_return_status OUT NOCOPY VARCHAR2,
3970 x_msg_count OUT NOCOPY NUMBER,
3971 x_msg_data OUT NOCOPY VARCHAR2
3972 ) IS
3973
3974 l_api_name CONSTANT VARCHAR2(30) := 'get_party_site_rec';
3975
3976 BEGIN
3977
3978 --Initialize message list if p_init_msg_list is set to TRUE.
3979 IF FND_API.to_Boolean(p_init_msg_list) THEN
3980 FND_MSG_PUB.initialize;
3981 END IF;
3982
3983 --Initialize API return status to success.
3984 x_return_status := FND_API.G_RET_STS_SUCCESS;
3985
3986 --Check whether primary key has been passed in.
3987 IF p_party_site_use_id IS NULL OR
3988 p_party_site_use_id = FND_API.G_MISS_NUM THEN
3989 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
3990 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'p_party_site_use_id' );
3991 FND_MSG_PUB.ADD;
3992 RAISE FND_API.G_EXC_ERROR;
3993 END IF;
3994
3995 x_party_site_use_rec.party_site_use_id := p_party_site_use_id;
3996
3997 HZ_PARTY_SITE_USES_PKG.Select_Row (
3998 X_PARTY_SITE_USE_ID => x_party_site_use_rec.party_site_use_id,
3999 X_COMMENTS => x_party_site_use_rec.comments,
4000 X_PARTY_SITE_ID => x_party_site_use_rec.party_site_id,
4001 X_SITE_USE_TYPE => x_party_site_use_rec.site_use_type,
4002 X_PRIMARY_PER_TYPE => x_party_site_use_rec.primary_per_type,
4003 X_STATUS => x_party_site_use_rec.status,
4004 X_CREATED_BY_MODULE => x_party_site_use_rec.created_by_module,
4005 X_APPLICATION_ID => x_party_site_use_rec.application_id
4006 );
4007
4008 --Standard call to get message count and if count is 1, get message info.
4009 FND_MSG_PUB.Count_And_Get(
4010 p_encoded => FND_API.G_FALSE,
4011 p_count => x_msg_count,
4012 p_data => x_msg_data );
4013
4014 EXCEPTION
4015 WHEN FND_API.G_EXC_ERROR THEN
4016 x_return_status := FND_API.G_RET_STS_ERROR;
4017
4018 FND_MSG_PUB.Count_And_Get(
4019 p_encoded => FND_API.G_FALSE,
4020 p_count => x_msg_count,
4021 p_data => x_msg_data );
4022
4023 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4024 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4025
4026 FND_MSG_PUB.Count_And_Get(
4027 p_encoded => FND_API.G_FALSE,
4028 p_count => x_msg_count,
4029 p_data => x_msg_data );
4030
4031 WHEN OTHERS THEN
4032 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4033 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
4034 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
4035 FND_MSG_PUB.ADD;
4036 FND_MSG_PUB.Count_And_Get(
4037 p_encoded => FND_API.G_FALSE,
4038 p_count => x_msg_count,
4039 p_data => x_msg_data );
4040
4041 END get_party_site_use_rec;
4042
4043
4044 /*===========================================================================+
4045 | PROCEDURE
4046 | do_unmark_primary_per_type
4047 |
4048 | DESCRIPTION
4049 | unmark the primary_per_type in hz_party_site_uses
4050 | for those site uses that are not primary for
4051 | each party.
4052 |
4053 | SCOPE - PRIVATE
4054 |
4055 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
4056 |
4057 | ARGUMENTS : IN:
4058 | p_party_id
4059 | p_party_site_id
4060 | p_site_use_type
4061 | OUT:
4062 | IN/ OUT:
4063 |
4064 | RETURNS : NONE
4065 |
4066 | NOTES
4067 |
4068 | MODIFICATION HISTORY
4069 | 20-May-2004 Ramesh Ch Created.
4070 |
4071 +===========================================================================*/
4072
4073 PROCEDURE do_unmark_primary_per_type(
4074 p_party_id IN NUMBER,
4075 p_party_site_id IN NUMBER,
4076 p_site_use_type IN VARCHAR2
4077 ) IS
4078
4079 CURSOR c_party_site_uses IS
4080 SELECT ROWID
4081 FROM HZ_PARTY_SITE_USES SU
4082 WHERE SU.PARTY_SITE_ID IN (
4083 SELECT PS.PARTY_SITE_ID
4084 FROM HZ_PARTY_SITES PS
4085 WHERE PARTY_ID = p_party_id )
4086 AND SU.PARTY_SITE_ID <> p_party_site_id
4087 AND SU.SITE_USE_TYPE = p_site_use_type
4088 AND SU.PRIMARY_PER_TYPE = 'Y'
4089 AND ROWNUM = 1
4090 FOR UPDATE NOWAIT;
4091
4092 l_rowid VARCHAR2(100);
4093
4094 BEGIN
4095
4096 -- check if party site use record is locked by any one else.
4097 -- notice the combination of party_site_id and site_use_type
4098 -- is unique.
4099
4100 BEGIN
4101 OPEN c_party_site_uses;
4102 FETCH c_party_site_uses INTO l_rowid;
4103 CLOSE c_party_site_uses;
4104 EXCEPTION
4105 WHEN OTHERS THEN
4106 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
4107 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_PARTY_SITE_USES');
4108 FND_MSG_PUB.ADD;
4109 RAISE FND_API.G_EXC_ERROR;
4110 END;
4111
4112 IF l_rowid IS NOT NULL THEN
4113 UPDATE HZ_PARTY_SITE_USES
4114 SET PRIMARY_PER_TYPE = 'N',
4115 last_update_date = hz_utility_v2pub.last_update_date,
4116 last_updated_by = hz_utility_v2pub.last_updated_by,
4117 last_update_login = hz_utility_v2pub.last_update_login,
4118 request_id = hz_utility_v2pub.request_id,
4119 program_id = hz_utility_v2pub.program_id,
4120 program_application_id = hz_utility_v2pub.program_application_id,
4121 program_update_date = hz_utility_v2pub.program_update_date
4122 WHERE ROWID = l_rowid;
4123 END IF;
4124
4125 END do_unmark_primary_per_type;
4126
4127
4128 /*===========================================================================+
4129 | PROCEDURE
4130 | do_create_party_site_use
4131 |
4132 | DESCRIPTION
4133 | Creates party_site_use.
4134 |
4135 | SCOPE - PRIVATE
4136 |
4137 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
4138 |
4139 | ARGUMENTS : IN:
4140 | OUT:
4141 | x_party_site_use_id
4142 | IN/ OUT:
4143 | p_party_site_use_rec
4144 | x_return_status
4145 |
4146 | RETURNS : NONE
4147 |
4148 | NOTES
4149 |
4150 | MODIFICATION HISTORY
4151 |
4152 +===========================================================================*/
4153
4154 PROCEDURE do_create_party_site_use(
4155 p_party_site_use_rec IN OUT NOCOPY HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_REC_TYPE,
4156 x_party_site_use_id OUT NOCOPY NUMBER,
4157 x_return_status IN OUT NOCOPY VARCHAR2
4158 ) IS
4159
4160 l_party_site_use_id NUMBER := p_party_site_use_rec.party_site_use_id;
4161 l_rowid ROWID := NULL;
4162 l_count NUMBER;
4163 l_exist VARCHAR2(1) := 'N';
4164 l_party_id NUMBER;
4165 l_primary_per_type VARCHAR2(1) := p_party_site_use_rec.primary_per_type;
4166 l_msg_count NUMBER;
4167 l_msg_data VARCHAR2(2000);
4168 l_dummy VARCHAR2(1);
4169 l_debug_prefix VARCHAR2(30) := '';
4170
4171 BEGIN
4172
4173 -- if this is the first party site use per type,,
4174 -- we need to mark it with primary_per_type = 'Y'.
4175 SELECT PARTY_ID
4176 INTO l_party_id
4177 FROM HZ_PARTY_SITES
4178 WHERE PARTY_SITE_ID = p_party_site_use_rec.party_site_id;
4179
4180 IF p_party_site_use_rec.primary_per_type = 'Y' THEN --Bug No:3560167
4181 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
4182 ELSE
4183 l_primary_per_type := 'N';
4184 BEGIN
4185 SELECT 'Y'
4186 INTO l_exist
4187 FROM HZ_PARTY_SITE_USES SU
4188 WHERE PARTY_SITE_ID IN (
4189 SELECT PARTY_SITE_ID
4190 FROM HZ_PARTY_SITES PS
4191 WHERE PS.PARTY_ID = l_party_id )
4192 AND SU.SITE_USE_TYPE = p_party_site_use_rec.site_use_type
4193 AND ROWNUM = 1;
4194
4195 EXCEPTION
4196
4197 --this is a new site use type
4198 WHEN NO_DATA_FOUND THEN
4199 l_primary_per_type := 'Y';
4200 END;
4201 p_party_site_use_rec.primary_per_type := l_primary_per_type;
4202 END IF;
4203 -- call table-handler.
4204 HZ_PARTY_SITE_USES_PKG.Insert_Row (
4205 X_PARTY_SITE_USE_ID => p_party_site_use_rec.party_site_use_id,
4206 X_COMMENTS => p_party_site_use_rec.comments,
4207 X_PARTY_SITE_ID => p_party_site_use_rec.party_site_id,
4208 X_SITE_USE_TYPE => p_party_site_use_rec.site_use_type,
4209 X_PRIMARY_PER_TYPE => p_party_site_use_rec.primary_per_type,
4210 X_STATUS => p_party_site_use_rec.status,
4211 X_OBJECT_VERSION_NUMBER => 1,
4212 X_CREATED_BY_MODULE => p_party_site_use_rec.created_by_module,
4213 X_APPLICATION_ID => p_party_site_use_rec.application_id
4214 );
4215
4216 x_party_site_use_id := p_party_site_use_rec.party_site_use_id;
4217
4218 END do_create_party_site_use;
4219
4220
4221 /*===========================================================================+
4222 | PROCEDURE
4223 | create_party_site_use
4224 |
4225 | DESCRIPTION
4226 | Creates party_site_use.
4227 |
4228 | SCOPE - PUBLIC
4229 |
4230 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
4231 |
4232 | ARGUMENTS : IN:
4233 | p_init_msg_list
4234 | p_party_site_use_rec
4235 | OUT:
4236 | x_return_status
4237 | x_msg_count
4238 | x_msg_data
4239 | x_party_site_use_id
4240 | IN/ OUT:
4241 |
4242 | RETURNS : NONE
4243 |
4244 | NOTES
4245 |
4246 | MODIFICATION HISTORY
4247 | Rashmi Goyal 31-AUG-99 Created
4248 |
4249 +===========================================================================*/
4250
4251 PROCEDURE create_party_site_use (
4252 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4253 p_party_site_use_rec IN HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_REC_TYPE,
4254 x_party_site_use_id OUT NOCOPY NUMBER,
4255 x_return_status OUT NOCOPY VARCHAR2,
4256 x_msg_count OUT NOCOPY NUMBER,
4257 x_msg_data OUT NOCOPY VARCHAR2
4258 ) IS
4259
4260 l_api_name CONSTANT VARCHAR2(30) := 'create_party_site_use';
4261 l_api_version CONSTANT NUMBER := 1.0;
4262 l_party_site_use_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_REC_TYPE := p_party_site_use_rec;
4263
4264 BEGIN
4265
4266 -- standard start of API savepoint
4267 SAVEPOINT create_party_site_use;
4268
4269 -- initialize message list if p_init_msg_list is set to TRUE.
4270 IF FND_API.to_Boolean(p_init_msg_list) THEN
4271 FND_MSG_PUB.initialize;
4272 END IF;
4273
4274 -- initialize API return status to success.
4275 x_return_status := FND_API.G_RET_STS_SUCCESS;
4276
4277 -- call to business logic.
4278 do_create_party_site_use(
4279 l_party_site_use_rec,
4280 x_party_site_use_id,
4281 x_return_status
4282 );
4283
4284 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
4285 -- Invoke business event system.
4286 HZ_BUSINESS_EVENT_V2PVT.create_party_site_use_event (
4287 l_party_site_use_rec );
4288 END IF;
4289
4290 -- standard call to get message count and if count is 1, get message info.
4291 FND_MSG_PUB.Count_And_Get(
4292 p_encoded => FND_API.G_FALSE,
4293 p_count => x_msg_count,
4294 p_data => x_msg_data);
4295
4296
4297 EXCEPTION
4298 WHEN FND_API.G_EXC_ERROR THEN
4299 ROLLBACK TO create_party_site_use;
4300 x_return_status := FND_API.G_RET_STS_ERROR;
4301 FND_MSG_PUB.Count_And_Get(
4302 p_encoded => FND_API.G_FALSE,
4303 p_count => x_msg_count,
4304 p_data => x_msg_data);
4305
4306 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4307 ROLLBACK TO create_party_site_use;
4308 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4309 FND_MSG_PUB.Count_And_Get(
4310 p_encoded => FND_API.G_FALSE,
4311 p_count => x_msg_count,
4312 p_data => x_msg_data);
4313
4314 WHEN OTHERS THEN
4315 ROLLBACK TO create_party_site_use;
4316 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4317 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
4318 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
4319 FND_MSG_PUB.ADD;
4320 FND_MSG_PUB.Count_And_Get(
4321 p_encoded => FND_API.G_FALSE,
4322 p_count => x_msg_count,
4323 p_data => x_msg_data);
4324
4325
4326 END create_party_site_use;
4327
4328 END hz_cust_account_merge_v2pvt;