[Home] [Help]
PACKAGE BODY: APPS.CSI_INSTANCE_PARTIES_VLD_PVT
Source
1 PACKAGE BODY CSI_Instance_parties_vld_pvt AS
2 /* $Header: csivipvb.pls 120.1 2005/07/15 16:43:09 srramakr noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'CSI_INSTANCE_PARTIES_VLD_PVT';
5 /*-----------------------------------------------------------*/
6 /* Procedure name: Check_Reqd_Param */
7 /* Description : To Check if the reqd parameter is passed */
8 /*-----------------------------------------------------------*/
9
10 PROCEDURE Check_Reqd_Param_num
11 (
12 p_number IN NUMBER,
13 p_param_name IN VARCHAR2,
14 p_api_name IN VARCHAR2
15 ) IS
16 BEGIN
17 IF (NVL(p_number,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) THEN
18 FND_MESSAGE.SET_NAME('CSI','CSI_API_REQD_PARAM_MISSING');
19 FND_MESSAGE.SET_TOKEN('API_NAME',p_api_name);
20 FND_MESSAGE.SET_TOKEN('MISSING_PARAM',p_param_name);
21 FND_MSG_PUB.Add;
22 RAISE FND_API.G_EXC_ERROR;
23 END IF;
24 END Check_Reqd_Param_num;
25
26 /*-----------------------------------------------------------*/
27 /* Procedure name: Check_Reqd_Param */
28 /* Description : To Check if the reqd parameter is passed */
29 /*-----------------------------------------------------------*/
30
31 PROCEDURE Check_Reqd_Param_char
32 (
33 p_variable IN VARCHAR2,
34 p_param_name IN VARCHAR2,
35 p_api_name IN VARCHAR2
36 ) IS
37 BEGIN
38 IF (NVL(p_variable,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR) THEN
39 FND_MESSAGE.SET_NAME('CSI','CSI_API_REQD_PARAM_MISSING');
40 FND_MESSAGE.SET_TOKEN('API_NAME',p_api_name);
41 FND_MESSAGE.SET_TOKEN('MISSING_PARAM',p_param_name);
42 FND_MSG_PUB.Add;
43 RAISE FND_API.G_EXC_ERROR;
44 END IF;
45 END Check_Reqd_Param_char;
46
47 /*-----------------------------------------------------------*/
48 /* Procedure name: Check_Reqd_Param */
49 /* Description : To Check if the reqd parameter is passed */
50 /*-----------------------------------------------------------*/
51
52 PROCEDURE Check_Reqd_Param_date
53 (
54 p_date IN DATE,
55 p_param_name IN VARCHAR2,
56 p_api_name IN VARCHAR2
57 ) IS
58 BEGIN
59 IF (NVL(p_date,FND_API.G_MISS_DATE) = FND_API.G_MISS_DATE) THEN
60 FND_MESSAGE.SET_NAME('CSI','CSI_API_REQD_PARAM_MISSING');
61 FND_MESSAGE.SET_TOKEN('API_NAME',p_api_name);
62 FND_MESSAGE.SET_TOKEN('MISSING_PARAM',p_param_name);
63 FND_MSG_PUB.Add;
64 RAISE FND_API.G_EXC_ERROR;
65 END IF;
66 END Check_Reqd_Param_date;
67
68 /*-----------------------------------------------------------*/
69 /* Procedure name: Is_Party_Rel_Comb_Exists */
70 /* Description : Check if the Party relationship combination */
71 /* exists already */
72 /*-----------------------------------------------------------*/
73
74 FUNCTION Is_Party_Rel_Comb_Exists
75 (
76 p_instance_id IN NUMBER ,
77 p_party_source_table IN VARCHAR2 ,
78 p_party_id IN NUMBER ,
79 p_relationship_type IN VARCHAR2 ,
80 p_contact_flag IN VARCHAR2 ,
81 p_contact_ip_id IN NUMBER ,
82 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
83 ) RETURN BOOLEAN IS
84
85 l_inst_party_id NUMBER;
86 l_return_value BOOLEAN := TRUE;
87
88 BEGIN
89 SELECT instance_party_id
90 INTO l_inst_party_id
91 FROM csi_i_parties
92 WHERE instance_id = p_instance_id
93 and party_source_table = p_party_source_table
94 and party_id = p_party_id
95 and relationship_type_code = p_relationship_type
96 and contact_flag = p_contact_flag
97 and NVL(contact_ip_id,fnd_api.g_miss_num) = nvl(p_contact_ip_id,fnd_api.g_miss_num)
98 and ((active_end_date is null) OR (active_end_date >= sysdate));
99
100 IF ( p_stack_err_msg = TRUE ) THEN
101 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_PARTY_REL_COMB');
102 FND_MESSAGE.SET_TOKEN('PARTY_REL_COMB',to_char(p_instance_id) ||','||p_party_source_table||','||to_char(p_party_id)||','||p_relationship_type||','||to_char(p_contact_ip_id));
103 FND_MSG_PUB.Add;
104 END IF;
105 RETURN l_return_value;
106 EXCEPTION
107 WHEN NO_DATA_FOUND THEN
108 l_return_value := FALSE;
109 RETURN l_return_value;
110 WHEN TOO_MANY_ROWS THEN
111 l_return_value := TRUE;
112 IF ( p_stack_err_msg = TRUE ) THEN
113 FND_MESSAGE.SET_NAME('CSI','CSI_API_MANY_PTY_REL_COM_EXIST');
114 FND_MESSAGE.SET_TOKEN('PARTY_REL_COMB',to_char(p_instance_id) ||','||p_party_source_table||','|| to_char(p_party_id)||','||p_relationship_type||','|| to_char(p_contact_ip_id));
115 FND_MSG_PUB.Add;
116 END IF;
117 RETURN l_return_value;
118 END Is_Party_Rel_Comb_Exists;
119
120 /*-----------------------------------------------------------*/
121 /* Procedure name: Is_Inst_PartyID_exists */
122 /* Description : Check if the Instance Party Id exists */
123 /*-----------------------------------------------------------*/
124
125 FUNCTION Is_Inst_PartyID_exists
126 ( p_Instance_party_id IN NUMBER,
127 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
128 ) RETURN BOOLEAN IS
129
130 l_dummy VARCHAR2(1);
131 l_return_value BOOLEAN := TRUE;
132 BEGIN
133 SELECT 'x'
134 INTO l_dummy
135 FROM csi_i_parties
136 WHERE instance_party_id = p_Instance_party_id;
137 IF ( p_stack_err_msg = TRUE ) THEN
138 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INST_PARTY_ID');
139 FND_MESSAGE.SET_TOKEN('INSTANCE_PARTY_ID',p_instance_party_id);
140 FND_MSG_PUB.Add;
141 END IF;
142 RETURN l_return_value;
143 EXCEPTION
144 WHEN NO_DATA_FOUND THEN
145 l_return_value := FALSE;
146 RETURN l_return_value;
147 END Is_Inst_PartyID_exists;
148
149 /*-----------------------------------------------------------*/
150 /* Procedure name: Is_InstanceID_Valid */
151 /* Description : Check if the Instance Id exists */
152 /*-----------------------------------------------------------*/
153
154 FUNCTION Is_InstanceID_Valid
155 ( p_instance_id IN NUMBER,
156 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
157 ) RETURN BOOLEAN IS
158
159 l_dummy VARCHAR2(1);
160 l_return_value BOOLEAN := TRUE;
161
162 CURSOR c1 IS
163 SELECT 'x'
164 FROM csi_item_instances
165 WHERE instance_id = p_instance_id
166 and ((active_end_date is null) OR (active_end_date >= sysdate));
167 BEGIN
168 OPEN c1;
169 FETCH c1 INTO l_dummy;
170 IF c1%NOTFOUND THEN
171 l_return_value := FALSE;
172 IF ( p_stack_err_msg = TRUE ) THEN
173 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INSTANCE_ID');
174 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_instance_id);
175 FND_MSG_PUB.Add;
176 END IF;
177 END IF;
178 CLOSE c1;
179 RETURN l_return_value;
180
181 END Is_InstanceID_Valid;
182
183 /*-----------------------------------------------------------*/
184 /* Procedure name: Is_Pty_Source_tab_Valid */
185 /* Description : Check if the Party Source Table is */
186 /* defined in CSI_LOOKUPS */
187 /*-----------------------------------------------------------*/
188
189 FUNCTION Is_Pty_Source_tab_Valid
190 (
191 p_party_source_table IN VARCHAR2,
192 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
193 ) RETURN BOOLEAN IS
194
195 l_dummy VARCHAR2(1);
196 l_return_value BOOLEAN := TRUE;
197 l_pty_lookup_type VARCHAR2(30) := 'CSI_PARTY_SOURCE_TABLE';
198
199 CURSOR c1 IS
200 SELECT 'x'
201 FROM csi_lookups
202 WHERE lookup_code = UPPER(p_party_source_table)
203 AND lookup_type = l_pty_lookup_type;
204 BEGIN
205 OPEN c1;
206 FETCH c1 INTO l_dummy;
207 IF c1%NOTFOUND THEN
208 l_return_value := FALSE;
209 IF ( p_stack_err_msg = TRUE ) THEN
210 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_PARTY_SOURCE');
211 FND_MESSAGE.SET_TOKEN('PARTY_SOURCE_TABLE',p_party_source_table);
212 FND_MSG_PUB.Add;
213 END IF;
214 END IF;
215 CLOSE c1;
216 RETURN l_return_value;
217
218 END Is_Pty_Source_tab_Valid;
219
220 /*-----------------------------------------------------------*/
221 /* Procedure name: Is_Party_Valid */
222 /* Description : Check if the Party Id exists in hz_parties */
223 /* po_vendors , employee depending on party_source_table */
224 /* value */
225 /*-----------------------------------------------------------*/
226
227 FUNCTION Is_Party_Valid
228 ( p_party_source_table IN VARCHAR2,
229 p_party_id IN NUMBER ,
230 p_contact_flag IN VARCHAR2,
231 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
232 ) RETURN BOOLEAN IS
233
234 l_dummy VARCHAR2(1);
235 l_return_value BOOLEAN := TRUE;
236 BEGIN
237 IF p_party_source_table = 'HZ_PARTIES' THEN
238
239 SELECT 'x'
240 INTO l_dummy
241 FROM hz_parties
242 WHERE party_id = p_party_id;
243
244 ELSIF p_party_source_table = 'PO_VENDORS' THEN
245 IF p_contact_flag = 'Y' THEN
246 SELECT 'x'
247 INTO l_dummy
248 FROM po_vendor_contacts
249 WHERE vendor_contact_id = p_party_id;
250 ELSE
251 SELECT 'x'
252 INTO l_dummy
253 FROM po_vendors
254 WHERE vendor_id = p_party_id
255 AND ((end_date_active is null) OR (end_date_active >= sysdate));
256 END IF;
257
258 ELSIF p_party_source_table = 'EMPLOYEE' THEN
259 SELECT 'x'
260 INTO l_dummy
261 FROM per_all_people_f
262 WHERE person_id = p_party_id
263 AND ((effective_end_date is null) OR (effective_end_date >= sysdate))
264 AND rownum < 2;
265
266 ELSIF p_party_source_table = 'TEAM' THEN
267 SELECT 'x'
268 INTO l_dummy
269 FROM jtf_rs_teams_vl
270 WHERE team_id = p_party_id
271 AND ((end_date_active is null) OR (end_date_active >= sysdate));
272
273 ELSIF p_party_source_table = 'GROUP' THEN
274 SELECT 'x'
275 INTO l_dummy
276 FROM jtf_rs_groups_vl
277 WHERE group_id = p_party_id
278 AND ((end_date_active is null) OR (end_date_active >= sysdate));
279 ELSE
280 l_return_value := FALSE;
281 IF ( p_stack_err_msg = TRUE ) THEN
282 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_PARTY_SOURCE');
283 FND_MESSAGE.SET_TOKEN('PARTY_SOURCE_TABLE',p_party_source_table );
284 FND_MSG_PUB.Add;
285 END IF;
286 END IF;
287 RETURN l_return_value;
288 EXCEPTION
289 WHEN NO_DATA_FOUND THEN
290 l_return_value := FALSE;
291 IF ( p_stack_err_msg = TRUE ) THEN
292 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_PARTY_ID');
293 FND_MESSAGE.SET_TOKEN('PARTY_ID',p_party_id);
294 FND_MSG_PUB.Add;
295 END IF;
296 RETURN l_return_value;
297 END Is_Party_Valid;
298
299 /*-----------------------------------------------------------*/
300 /* Procedure name: Is_Party_Rel_type_code_Valid */
301 /* Description : Check if the Party relationship type code */
302 /* exists in CSI_LOOKUPS table */
303 /*-----------------------------------------------------------*/
304
305 FUNCTION Is_Pty_Rel_type_Valid
306 ( p_party_rel_type_code IN VARCHAR2,
307 p_contact_flag IN VARCHAR2,
308 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
309 ) RETURN BOOLEAN IS
310
311 l_dummy VARCHAR2(1);
312 l_return_value BOOLEAN := TRUE;
313
314
315 BEGIN
316 IF p_contact_flag = 'P' THEN
317 select 'x'
318 into l_dummy
319 from csi_ipa_relation_types
320 where ipa_relation_type_code = UPPER(p_party_rel_type_code)
321 and party_use_flag = 'Y';
322
323 ELSIF p_contact_flag = 'A' THEN
324 select 'x'
325 into l_dummy
326 from csi_ipa_relation_types
327 where ipa_relation_type_code = UPPER(p_party_rel_type_code)
328 and account_use_flag = 'Y';
329
330 ELSIF p_contact_flag = 'C' THEN
331 select 'x'
332 into l_dummy
333 from csi_ipa_relation_types
334 where ipa_relation_type_code = UPPER(p_party_rel_type_code)
335 and contact_use_flag = 'Y';
336 END IF;
337 RETURN l_return_value;
338 EXCEPTION
339 WHEN NO_DATA_FOUND THEN
340 l_return_value := FALSE;
341 IF ( p_stack_err_msg = TRUE ) THEN
342 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_PARTY_TYPE_CODE');
343 FND_MESSAGE.SET_TOKEN('RELATIONSHIP_TYPE_CODE',p_party_rel_type_code);
344 FND_MSG_PUB.Add;
345 END IF;
346 RETURN l_return_value;
347 END Is_Pty_Rel_type_Valid ;
348
349 /*-----------------------------------------------------------*/
350 /* Procedure name: Is_Contact_Valid */
351 /* Description : Check if it is defined as a contact for */
352 /* party_id in hz_party_relationships */
353 /*-----------------------------------------------------------*/
354
355 FUNCTION Is_Contact_Valid
356 ( p_contact_party_id IN NUMBER,
357 p_contact_source_table IN VARCHAR2,
358 p_ip_contact_id IN NUMBER,
359 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
360 ) RETURN BOOLEAN IS
361
362 l_party_id NUMBER ;
363 l_party_source_table VARCHAR2(30) ;
364 l_dummy VARCHAR2(1);
365 l_return_value BOOLEAN := TRUE;
366 l_org_id NUMBER;
367
368 CURSOR C1( i_inst_party_id IN NUMBER)IS
369 SELECT cip.party_id,
370 cip.party_source_table
371 FROM csi_i_parties cip,
372 csi_item_instances cii
373 WHERE cip.instance_party_id = i_inst_party_id
374 AND cip.instance_id = cii.instance_id
375 AND ((cip.active_end_date IS NULL) OR (cip.active_end_date >= SYSDATE));
376
377 -- The following code has been modified by sguthiva for bug 2428675
378 CURSOR C2 (i_contact_id IN NUMBER ,
379 i_party_id IN NUMBER ) IS
380 SELECT 'x'
381 FROM hz_relationships r
382 , hz_org_contacts c
383 , ar_lookups l
384 WHERE (r.object_id = i_party_id
385 OR
386 r.object_id IN (SELECT DISTINCT ha.party_id
387 FROM hz_cust_accounts ha
388 ,hz_cust_acct_relate_all rel
389 ,hz_cust_accounts hz
390 WHERE ha.cust_account_id=rel.related_cust_account_id
391 AND rel.cust_account_id=hz.cust_account_id
392 AND rel.status='A'
393 AND hz.party_id=i_party_id)
394 )
395 AND r.relationship_id = c.party_relationship_id
396 AND r.subject_id = i_contact_id
397 AND r.directional_flag = 'F'
398 AND r.relationship_code = l.lookup_code
399 AND l.lookup_type = 'PARTY_RELATIONS_TYPE';
400 -- End of modification by sguthiva for bug 2428675
401 BEGIN
402
403 -- Fetch contact id and its source table
404 OPEN C1(p_ip_contact_id);
405 FETCH C1 INTO l_party_id, l_party_source_table;
406 IF C1%NOTFOUND THEN
407 l_return_value := FALSE;
408 IF ( p_stack_err_msg = TRUE ) THEN
409 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_CONTACT_INFO');
410 FND_MESSAGE.SET_TOKEN('CONTACT_PARTY_ID',p_contact_party_id);
411 FND_MESSAGE.SET_TOKEN('CONTACT_SOURCE_TABLE',p_contact_source_table);
412 FND_MSG_PUB.Add;
413 END IF;
414 CLOSE C1;
415 RETURN l_return_value;
416 END IF;
417
418 IF (p_contact_source_table = 'HZ_PARTIES')
419 AND (l_party_source_table = 'HZ_PARTIES') THEN
420
421 OPEN C2(p_contact_party_id,l_party_id);
422 FETCH C2 INTO l_dummy;
423 IF C2%NOTFOUND THEN
424 l_return_value := FALSE;
425 IF ( p_stack_err_msg = TRUE ) THEN
426 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_CONTACT_INFO');
427 FND_MESSAGE.SET_TOKEN('CONTACT_PARTY_ID',p_contact_party_id);
428 FND_MESSAGE.SET_TOKEN('CONTACT_SOURCE_TABLE',p_contact_source_table);
429 FND_MSG_PUB.Add;
430 END IF;
431 CLOSE C2;
432 END IF;
433 ELSE
434 l_return_value := TRUE;
435 END IF;
436
437 RETURN l_return_value;
438 END Is_Contact_Valid;
439
440 /*-----------------------------------------------------------*/
441 /* Procedure name: Is_StartDate_Valid */
442 /* Description : Check if party relationship active start */
443 /* date is valid */
444 /*-----------------------------------------------------------*/
445
446 FUNCTION Is_StartDate_Valid
447 ( p_start_date IN DATE,
448 p_end_date IN DATE,
449 p_instance_id IN NUMBER,
450 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
451 ) RETURN BOOLEAN IS
452
453 l_instance_start_date DATE;
454 l_instance_end_date DATE;
455 l_return_value BOOLEAN := TRUE;
456
457 CURSOR c1 IS
458 SELECT active_start_date,
459 active_end_date
460 FROM csi_item_instances
461 WHERE instance_id = p_instance_id
462 and ((active_end_date is null) OR (active_end_date >= sysdate));
463 BEGIN
464 IF ((p_end_date is NOT NULL) AND (p_end_date <> FND_API.G_MISS_DATE))THEN
465 IF p_start_date > p_end_date THEN
466 l_return_value := FALSE;
467 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_PTY_START_DATE');
468 FND_MESSAGE.SET_TOKEN('ACTIVE_START_DATE',p_start_date);
469 FND_MSG_PUB.Add;
470 RETURN l_return_value;
471 END IF;
472 END IF;
473
474 OPEN c1;
475 FETCH c1 INTO l_instance_start_date,l_instance_end_date;
476 IF c1%NOTFOUND THEN
477 l_return_value := FALSE;
478 IF ( p_stack_err_msg = TRUE ) THEN
479 FND_MESSAGE.SET_NAME('CSI','CSI_API_INST_STARTDATE_NOT_EXT');
480 FND_MSG_PUB.Add;
481 END IF;
482 CLOSE c1;
483 RETURN l_return_value;
484 END IF;
485
486 IF ((p_start_date < l_instance_start_date)
487 OR ((l_instance_end_date IS NOT NULL) AND (p_start_date > l_instance_end_date))
488 OR (p_start_date > SYSDATE)) THEN
489 l_return_value := FALSE;
490 IF ( p_stack_err_msg = TRUE ) THEN
491 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_PTY_START_DATE');
492 FND_MESSAGE.SET_TOKEN('ACTIVE_START_DATE',p_start_date);
493 FND_MSG_PUB.Add;
494 END IF;
495 END IF;
496 RETURN l_return_value;
497 END Is_StartDate_Valid;
498
499 /*-----------------------------------------------------------*/
500 /* Procedure name: Is_EndDate_Valid */
501 /* Description : Check if party relationship active end */
502 /* date is valid */
503 /*-----------------------------------------------------------*/
504
505 /*
506 FUNCTION Is_EndDate_Valid
507 (
508 p_start_date IN DATE,
509 p_end_date IN DATE,
510 p_instance_id IN NUMBER,
511 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
512 ) RETURN BOOLEAN IS
513
514 l_instance_end_date DATE;
515 l_instance_start_date DATE;
516 l_return_value BOOLEAN := TRUE;
517
518 CURSOR c1 IS
519 SELECT active_end_date,
520 active_start_date
521 FROM csi_item_instances
522 WHERE instance_id = p_instance_id
523 and ((active_end_date is null) OR (active_end_date >= sysdate));
524 BEGIN
525 IF p_end_date is NOT NULL THEN
526 IF p_end_date < sysdate THEN
527 l_return_value := FALSE;
528 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_PTY_END_DATE');
529 FND_MESSAGE.SET_TOKEN('ACTIVE_END_DATE',p_end_date);
530 FND_MSG_PUB.Add;
531 RETURN l_return_value;
532 END IF;
533 END IF;
534
535 OPEN c1;
536 FETCH c1 INTO l_instance_end_date ,l_instance_start_date;
537
538 IF l_instance_end_date is NOT NULL THEN
539 IF ((p_end_date > l_instance_end_date) OR
540 (p_end_date < l_instance_start_date))THEN
541 l_return_value := FALSE;
542 IF ( p_stack_err_msg = TRUE ) THEN
543 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_PTY_END_DATE');
544 FND_MESSAGE.SET_TOKEN('ACTIVE_END_DATE',p_end_date);
545 FND_MSG_PUB.Add;
546 END IF;
547 END IF;
548 END IF;
549 CLOSE c1;
550 RETURN l_return_value;
551
552 END Is_EndDate_Valid;
553 */
554
555 /*-----------------------------------------------------------*/
556 /* Procedure name: Is_EndDate_Valid */
557 /* Description : Check if party relationship active end */
558 /* date is valid */
559 /*-----------------------------------------------------------*/
560
561 FUNCTION Is_EndDate_Valid
562 (
563 p_start_date IN DATE,
564 p_end_date IN DATE,
565 p_instance_id IN NUMBER,
566 p_instance_party_id IN NUMBER,
567 p_txn_id IN NUMBER,
568 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
569 ) RETURN BOOLEAN IS
570
571 l_instance_end_date DATE;
572 l_instance_start_date DATE;
573 l_return_value BOOLEAN := TRUE;
574
575 CURSOR c1 IS
576 SELECT active_end_date,
577 active_start_date
578 FROM csi_item_instances
579 WHERE instance_id = p_instance_id
580 and ((active_end_date is null) OR (active_end_date >= sysdate));
581 BEGIN
582
583 IF ((p_instance_party_id is null) or (p_instance_party_id = fnd_api.g_miss_num)) then
584
585 IF ((p_end_date is NOT NULL) and (p_end_date <> fnd_api.g_miss_date)) THEN
586 IF p_end_date < sysdate THEN
587 l_return_value := FALSE;
588 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_PTY_END_DATE');
589 FND_MESSAGE.SET_TOKEN('ACTIVE_END_DATE',p_end_date);
590 FND_MSG_PUB.Add;
591 RETURN l_return_value;
592 END IF;
593 END IF;
594
595 ELSE
596 IF p_end_date < sysdate THEN
597
598 IF NOT (GET_PARTIES
599 (
600 p_start_date => p_start_date,
601 p_end_date => p_end_date,
602 p_instance_party_id => p_instance_party_id,
603 p_txn_id => p_txn_id
604 )) THEN
605 l_return_value := FALSE;
606 RETURN l_return_value;
607 END IF;
608 END IF;
609
610 IF ((p_end_date is NOT NULL) and (p_end_date <> fnd_api.g_miss_date)) THEN
611 OPEN c1;
612 FETCH c1 INTO l_instance_end_date ,l_instance_start_date;
613
614 IF l_instance_end_date is NOT NULL THEN
615 IF ((p_end_date > l_instance_end_date) OR
616 (p_end_date < l_instance_start_date))THEN
617 l_return_value := FALSE;
618 IF ( p_stack_err_msg = TRUE ) THEN
619 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_PTY_END_DATE');
620 FND_MESSAGE.SET_TOKEN('ACTIVE_END_DATE',p_end_date);
621 FND_MSG_PUB.Add;
622 END IF;
623 END IF;
624 END IF;
625 CLOSE c1;
626 END IF;
627 END IF;
628 RETURN l_return_value;
629
630 END Is_EndDate_Valid;
631
632 --Added by rtalluri for end_date validation 02/19/02
633 FUNCTION get_parties
634 (
635 p_start_date IN DATE,
636 p_end_date IN DATE,
637 p_instance_party_id IN NUMBER,
638 p_txn_id IN NUMBER
639 )
640 RETURN BOOLEAN IS
641
642 l_transaction_date date;
643 l_party_temp number;
644 l_contact_temp number;
645 l_account_temp number;
646 l_return_value BOOLEAN := TRUE;
647
648 CURSOR C1 is
649 SELECT instance_party_id
650 FROM csi_i_parties
651 WHERE contact_ip_id = p_instance_party_id
652 AND ((active_end_date is null) or (active_end_date > sysdate));
653
654 CURSOR C2 is
655 SELECT ip_account_id
656 FROM csi_ip_accounts
657 WHERE instance_party_id = p_instance_party_id
658 AND ((active_end_date is null) or (active_end_date > sysdate));
659
660 BEGIN
661
662 SELECT MAX(t.source_transaction_date) -- Changed from Transaction_date to Source_transaction_date
663 INTO l_transaction_date
664 FROM csi_i_parties_h s,
665 csi_transactions t
666 WHERE s.instance_party_id=p_instance_party_id
667 AND s.transaction_id=t.transaction_id
668 AND t.transaction_id <>nvl(p_txn_id, -99999);
669
670 IF l_transaction_date > p_end_date
671 THEN
672 fnd_message.set_name('CSI','CSI_HAS_TXNS');
673 fnd_message.set_token('END_DATE_ACTIVE',p_end_date);
674 fnd_msg_pub.add;
675 l_return_value := FALSE;
676 RETURN l_return_value;
677 END IF;
678
679 OPEN C1;
680 FETCH C1 into l_contact_temp;
681 IF C1%FOUND then
682 SELECT MAX(t.source_transaction_date) -- Changed from Transaction_date to Source_transaction_date
683 INTO l_transaction_date
684 FROM csi_i_parties_h s,
685 csi_transactions t
686 WHERE s.instance_party_id=l_contact_temp
687 AND s.transaction_id=t.transaction_id
688 AND t.transaction_id <> nvl(p_txn_id, -99999);
689
690 IF l_transaction_date > p_end_date
691 THEN
692 fnd_message.set_name('CSI','CSI_HAS_TXNS');
693 fnd_message.set_token('END_DATE_ACTIVE',p_end_date);
694 fnd_msg_pub.add;
695 l_return_value := FALSE;
696 RETURN l_return_value;
697 END IF;
698 END IF;
699 CLOSE C1;
700
701 OPEN C2;
702 FETCH C2 into l_account_temp;
703 IF C2%FOUND then
704 SELECT MAX(t.source_transaction_date) -- Changed from Transaction_date to Source_transaction_date
705 INTO l_transaction_date
706 FROM csi_ip_accounts_h s,
707 csi_transactions t
708 WHERE s.ip_account_id =l_account_temp
709 AND s.transaction_id=t.transaction_id
710 AND t.transaction_id <> nvl(p_txn_id, -99999);
711
712 IF l_transaction_date > p_end_date
713 THEN
714 fnd_message.set_name('CSI','CSI_HAS_TXNS');
715 fnd_message.set_token('END_DATE_ACTIVE',p_end_date);
716 fnd_msg_pub.add;
717 l_return_value := FALSE;
718 RETURN l_return_value;
719 END IF;
720 END IF;
721 CLOSE C2;
722 RETURN l_return_value;
723 END get_parties;
724 --End of addition
725
726
727 /*-----------------------------------------------------------*/
728 /* Procedure name: Is_Inst_Owner_exists */
729 /* Description : Check if owner exists for instance_id */
730 /*-----------------------------------------------------------*/
731
732 FUNCTION Is_Inst_Owner_exists
733 ( p_Instance_id IN NUMBER,
734 p_instance_party_id IN NUMBER,
735 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
736 ) RETURN BOOLEAN IS
737
738 l_dummy VARCHAR(1);
739 l_return_value BOOLEAN := TRUE;
740 l_inst_party_id NUMBER;
741
742 CURSOR C1 IS
743 SELECT 'x'
744 FROM csi_i_parties
745 WHERE instance_id = p_Instance_id
746 AND instance_party_id <> l_inst_party_id -- Ignore the current instance_party_id
747 AND relationship_type_code = 'OWNER'
748 and ((active_end_date is null) OR (active_end_date >= sysdate));
749 BEGIN
750 IF p_instance_party_id IS NULL OR
751 p_instance_party_id = FND_API.G_MISS_NUM THEN
752 l_inst_party_id := -99999;
753 ELSE
754 l_inst_party_id := p_instance_party_id;
755 END IF;
756 OPEN C1;
757 FETCH C1 INTO l_dummy;
758 IF C1%FOUND THEN
759 IF ( p_stack_err_msg = TRUE ) THEN
760 FND_MESSAGE.SET_NAME('CSI','CSI_API_OWNER_ALREADY_EXISTS');
761 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_instance_id);
762 FND_MSG_PUB.Add;
763 END IF;
764 ELSE
765 l_return_value := FALSE;
766 END IF;
767 CLOSE C1;
768 RETURN l_return_value;
769 EXCEPTION
770 WHEN OTHERS THEN
771 l_return_value := FALSE;
772 RETURN l_return_value;
773 END Is_Inst_Owner_exists;
774
775 /*-----------------------------------------------------------*/
776 /* Procedure name: gen_inst_party_id */
777 /* Description : Generate instance_party_id from the sequence*/
778 /*-----------------------------------------------------------*/
779
780 FUNCTION gen_inst_party_id
781 RETURN NUMBER IS
782
783 l_inst_party_id NUMBER;
784
785 BEGIN
786 SELECT CSI_I_PARTIES_S.nextval
787 INTO l_inst_party_id
788 FROM sys.dual;
789 RETURN l_inst_party_id;
790 END gen_inst_party_id;
791
792 /*-----------------------------------------------------------*/
793 /* Procedure name: gen_inst_party_hist_id */
794 /* Description : Generate instance_party_history_id */
795 /* from the sequence */
796 /*-----------------------------------------------------------*/
797
798 FUNCTION gen_inst_party_hist_id
799 RETURN NUMBER IS
800 l_inst_party_his_id NUMBER;
801 BEGIN
802 SELECT CSI_I_PARTIES_H_S.nextval
803 INTO l_inst_party_his_id
804 FROM sys.dual;
805 RETURN l_inst_party_his_id ;
806 END gen_inst_party_hist_id;
807
808 /*-----------------------------------------------------------*/
809 /* Procedure name: Is_Instance_creation_complete */
810 /* Description : Check if the instance creation is */
811 /* complete */
812 /*-----------------------------------------------------------*/
813
814 FUNCTION Is_Inst_creation_complete
815 ( p_instance_id IN NUMBER,
816 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
817 ) RETURN BOOLEAN IS
818
819 l_dummy VARCHAR2(1);
820 l_return_value BOOLEAN := TRUE;
821 BEGIN
822 SELECT 'x'
823 INTO l_dummy
824 FROM csi_item_instances
825 WHERE instance_id = p_Instance_id
826 AND ((active_end_date is null) OR (active_end_date >= sysdate))
827 AND creation_complete_flag = 'Y';
828 RETURN l_return_value;
829 EXCEPTION
830 WHEN NO_DATA_FOUND THEN
831 l_return_value := FALSE;
832 RETURN l_return_value;
833 END Is_Inst_creation_complete;
834
835 /*-----------------------------------------------------------*/
836 /* Procedure name: Is_Pty_Acct_Comb_Exists */
837 /* Description : Check if the party account combination */
838 /* exists in csi_ip_accounts */
839 /*-----------------------------------------------------------*/
840
841 FUNCTION Is_Pty_Acct_Comb_Exists
842 (
843 p_instance_party_id IN NUMBER ,
844 p_party_account_id IN NUMBER ,
845 p_relationship_type IN VARCHAR2,
846 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
847 ) RETURN BOOLEAN IS
848
849 l_dummy VARCHAR2(1);
850 l_return_value BOOLEAN := TRUE;
851
852 BEGIN
853
854 SELECT 'x'
855 INTO l_dummy
856 FROM csi_ip_accounts
857 WHERE instance_party_id = p_instance_party_id
858 and party_account_id = p_party_account_id
859 and relationship_type_code = p_relationship_type
860 and ((active_end_date is null) OR (active_end_date >= sysdate));
861 IF ( p_stack_err_msg = TRUE ) THEN
862 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_PARTY_ACCT_COM');
863 FND_MESSAGE.SET_TOKEN('ACCT_COMBINATION',p_instance_party_id ||', '
864 ||p_party_account_id ||', '||p_relationship_type);
865 FND_MSG_PUB.Add;
866 END IF;
867 RETURN l_return_value;
868 EXCEPTION
869 WHEN NO_DATA_FOUND THEN
870 l_return_value := FALSE;
871 RETURN l_return_value;
872 END Is_Pty_Acct_Comb_Exists;
873
874 /*-----------------------------------------------------------*/
875 /* Procedure name: Is_IP_account_Exists */
876 /* Description : Check if the IP_account_id */
877 /* exists in csi_ip_accounts */
878 /*-----------------------------------------------------------*/
879
880 FUNCTION Is_IP_account_Exists
881 ( p_ip_account_id IN NUMBER,
882 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
883 ) RETURN BOOLEAN IS
884 l_dummy VARCHAR2(1);
885 l_return_value BOOLEAN := TRUE;
886 BEGIN
887 SELECT 'x'
888 INTO l_dummy
889 FROM csi_ip_accounts
890 WHERE ip_account_id = p_ip_account_id
891 and ((active_end_date is null) OR (active_end_date >= sysdate));
892 IF ( p_stack_err_msg = TRUE ) THEN
893 FND_MESSAGE.SET_NAME('CSI','CSI_API_IP_ACCOUNT_ID');
894 FND_MESSAGE.SET_TOKEN('IP_ACCOUNT_ID',p_ip_account_id);
895 FND_MSG_PUB.Add;
896 END IF;
897 RETURN l_return_value;
898 EXCEPTION
899 WHEN NO_DATA_FOUND THEN
900 l_return_value := FALSE;
901 RETURN l_return_value;
902 END Is_IP_account_Exists ;
903
904
905 /*-----------------------------------------------------------*/
906 /* Procedure name: Is_Inst_partyID_Expired */
907 /* Description : Check if the instance_party_id */
908 /* is expired */
909 /*-----------------------------------------------------------*/
910
911 FUNCTION Is_Inst_partyID_Expired
912 (
913 p_Instance_party_id IN NUMBER,
914 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
915 ) RETURN BOOLEAN IS
916
917 l_inst_party_id NUMBER;
918 l_return_value BOOLEAN := TRUE;
919
920 BEGIN
921 SELECT instance_party_id
922 INTO l_inst_party_id
923 FROM csi_i_parties
924 WHERE instance_party_id = p_Instance_party_id;
925 RETURN l_return_value;
926 EXCEPTION
927 WHEN NO_DATA_FOUND THEN
928 l_return_value := FALSE;
929 RETURN l_return_value;
930 END Is_Inst_partyID_Expired;
931
932 /*-----------------------------------------------------------*/
933 /* Procedure name: Is_Inst_partyID_Valid */
934 /* Description : Check if the instance_party_id */
935 /* exists in csi_i_parties */
936 /*-----------------------------------------------------------*/
937
938 FUNCTION Is_Inst_partyID_Valid
939 (
940 p_Instance_party_id IN NUMBER,
941 p_txn_type_id IN NUMBER, -- Added for bug 3550541
942 p_mode IN VARCHAR2, -- Added for bug 3550541
943 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
944
945 ) RETURN BOOLEAN IS
946
947 l_instance_id NUMBER;
948 l_return_value BOOLEAN := TRUE;
949
950 BEGIN
951 SELECT instance_id
952 INTO l_instance_id
953 FROM csi_i_parties
954 WHERE instance_party_id = p_Instance_party_id
955 AND ((active_end_date is null) OR (active_end_date >= sysdate));
956 --
957 -- srramakr Instance ID validation added for bug # 2477417.
958 IF NOT(CSI_Instance_parties_vld_pvt.Is_InstanceID_Valid(l_instance_id)) THEN
959 l_return_value := FALSE;
960 END IF;
961 RETURN l_return_value;
962 EXCEPTION
963 WHEN NO_DATA_FOUND THEN
964 -- Added for bug 3550541
965 IF p_txn_type_id=7 AND
966 p_mode='U'
967 THEN
968 -- Check for the existence of instance_party_id in the database.
969 BEGIN
970 SELECT instance_id
971 INTO l_instance_id
972 FROM csi_i_parties
973 WHERE instance_party_id = p_Instance_party_id;
974 EXCEPTION
975 WHEN NO_DATA_FOUND THEN
976 l_return_value := FALSE;
977 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INST_PARTY_ID');
978 FND_MESSAGE.SET_TOKEN('INSTANCE_PARTY_ID',p_INSTANCE_PARTY_ID);
979 FND_MSG_PUB.Add;
980 END;
981 -- End addition for bug 3550541
982 ELSE
983 l_return_value := FALSE;
984 IF p_stack_err_msg = TRUE THEN
985 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INST_PARTY_ID');
986 FND_MESSAGE.SET_TOKEN('INSTANCE_PARTY_ID',p_INSTANCE_PARTY_ID);
987 FND_MSG_PUB.Add;
988 END IF;
989 END IF;
990 RETURN l_return_value;
991 END Is_Inst_partyID_Valid;
992
993 /*-----------------------------------------------------------*/
994 /* Procedure name: Is_Pty_accountID_Valid */
995 /* Description : Check if the party account_id */
996 /* exists in hz_cust_accounts */
997 /*-----------------------------------------------------------*/
998
999 FUNCTION Is_Pty_accountID_Valid
1000 ( p_party_account_id IN NUMBER,
1001 p_instance_party_id IN NUMBER,
1002 p_relationship_type_code IN VARCHAR2,
1003 p_txn_type_id IN NUMBER, -- Added for bug 3550541
1004 p_mode IN VARCHAR2, -- Added for bug 3550541
1005 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
1006 ) RETURN BOOLEAN IS
1007 l_dummy VARCHAR2(1);
1008 l_return_value BOOLEAN := TRUE;
1009 l_party_id NUMBER;
1010 l_party_source_table VARCHAR2(30);
1011
1012 CURSOR C1(i_party_account_id NUMBER) IS
1013 SELECT 'x'
1014 FROM hz_cust_accounts
1015 WHERE cust_account_id = i_party_account_id;
1016
1017 CURSOR C2(i_party_account_id NUMBER,
1018 i_party_id NUMBER) IS
1019 SELECT 'x'
1020 FROM hz_cust_accounts hzca,
1021 hz_parties hzp
1022 WHERE hzca.cust_account_id = i_party_account_id
1023 AND hzca.party_id = i_party_id
1024 AND hzca.party_id = hzp.party_id;
1025
1026 CURSOR C3(i_inst_party_id NUMBER) IS
1027 SELECT party_id,
1028 party_source_table
1029 FROM csi_i_parties
1030 WHERE instance_party_id = i_inst_party_id
1031 AND ((active_end_date is null) OR (active_end_date >= sysdate));
1032
1033
1034 BEGIN
1035 -- Fetch partty or contact id and its source table
1036 OPEN C3(p_instance_party_id);
1037 FETCH C3 INTO l_party_id, l_party_source_table;
1038 IF C3%NOTFOUND THEN
1039 -- Added for bug 3550541
1040 IF p_txn_type_id = 7 AND
1041 p_mode='U'
1042 THEN
1043 BEGIN
1044 -- Check for the existence of instance_party_id in the database.
1045 SELECT party_id,
1046 party_source_table
1047 INTO l_party_id,
1048 l_party_source_table
1049 FROM csi_i_parties
1050 WHERE instance_party_id = p_instance_party_id;
1051 EXCEPTION
1052 WHEN NO_DATA_FOUND THEN
1053 l_return_value := FALSE;
1054 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INST_PARTY_ID');
1055 FND_MESSAGE.SET_TOKEN('INSTANCE_PARTY_ID',p_instance_party_id);
1056 FND_MSG_PUB.Add;
1057 -- Errored, No need to proceed further
1058 RETURN l_return_value;
1059 END;
1060 -- End addition for bug 3550541
1061 ELSE
1062 l_return_value := FALSE;
1063 IF ( p_stack_err_msg = TRUE ) THEN
1064 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INST_PARTY_ID');
1065 FND_MESSAGE.SET_TOKEN('INSTANCE_PARTY_ID',p_instance_party_id);
1066 FND_MSG_PUB.Add;
1067 END IF;
1068 CLOSE C3;
1069 -- Errored, No need to proceed further
1070 RETURN l_return_value;
1071 END IF;
1072 END IF;
1073
1074 IF (--(p_relationship_type_code = 'OWNER') AND -- Need to check for all rel.
1075 (l_party_source_table = 'HZ_PARTIES'))
1076 THEN
1077 OPEN C2(p_party_account_id, l_party_id);
1078 FETCH C2 INTO l_dummy;
1079 IF c2%NOTFOUND
1080 THEN
1081 l_return_value := FALSE;
1082 IF ( p_stack_err_msg = TRUE )
1083 THEN
1084 FND_MESSAGE.SET_NAME('CSI','CSI_API_PTY_ACCT_HZ_PTY');
1085 FND_MESSAGE.SET_TOKEN('PARTY_ACCOUNT_ID',p_party_account_id);
1086 FND_MESSAGE.SET_TOKEN('PARTY_ID',l_party_id);
1087 FND_MSG_PUB.Add;
1088 END IF;
1089 END IF;
1090 CLOSE c2;
1091 ELSE
1092 OPEN C1(p_party_account_id);
1093 FETCH C1 INTO l_dummy;
1094 IF c1%NOTFOUND
1095 THEN
1096 l_return_value := FALSE;
1097 IF ( p_stack_err_msg = TRUE )
1098 THEN
1099 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_PTY_ACCT_ID');
1100 FND_MESSAGE.SET_TOKEN('PARTY_ACCOUNT_ID',p_party_account_id);
1101 FND_MSG_PUB.Add;
1102 END IF;
1103 END IF;
1104 CLOSE c1;
1105 END IF;
1106 RETURN l_return_value;
1107 END Is_pty_accountID_Valid;
1108
1109
1110 /*-----------------------------------------------------------*/
1111 /* Procedure name: Is_Acct_Rel_type_Valid */
1112 /* Description : Check if the Party account relationship */
1113 /* type code exists in CSI_LOOKUPS */
1114 /*-----------------------------------------------------------*/
1115
1116 FUNCTION Is_Acct_Rel_type_Valid
1117 ( p_acct_rel_type_code IN VARCHAR2,
1118 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
1119 ) RETURN BOOLEAN IS
1120
1121 l_dummy VARCHAR2(1);
1122 l_return_value BOOLEAN := TRUE;
1123
1124 CURSOR c1 IS
1125 select 'x'
1126 from csi_ipa_relation_types
1127 where ipa_relation_type_code = UPPER(p_acct_rel_type_code)
1128 and account_use_flag = 'Y';
1129
1130 BEGIN
1131 OPEN c1;
1132 FETCH c1 INTO l_dummy;
1133 IF c1%NOTFOUND THEN
1134 l_return_value := FALSE;
1135 IF ( p_stack_err_msg = TRUE ) THEN
1136 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ACCOUNT_TYPE');
1137 FND_MESSAGE.SET_TOKEN('IP_RELATIONSHIP_TYPE_CODE',p_acct_rel_type_code);
1138 FND_MSG_PUB.Add;
1139 END IF;
1140 END IF;
1141 CLOSE c1;
1142 RETURN l_return_value;
1143 END Is_Acct_Rel_type_Valid;
1144
1145 /*-----------------------------------------------------------*/
1146 /* Procedure name: Is_Acct_StartDate_Valid */
1147 /* Description : Check if the Account active Start date */
1148 /* is valid */
1149 /*-----------------------------------------------------------*/
1150
1151 FUNCTION Is_Acct_StartDate_Valid
1152 ( p_start_date IN DATE,
1153 p_end_date IN DATE,
1154 p_instance_party_id IN NUMBER,
1155 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
1156 ) RETURN BOOLEAN IS
1157
1158 l_inst_party_start_date DATE;
1159 l_return_value BOOLEAN := TRUE;
1160
1161 CURSOR c1 IS
1162 SELECT active_start_date
1163 FROM csi_i_parties
1164 WHERE instance_party_id = p_instance_party_id
1165 and ((active_end_date is null) OR (active_end_date >= sysdate));
1166 BEGIN
1167 IF (p_end_date is NOT NULL) AND (p_end_date <> FND_API.G_MISS_DATE) THEN
1168
1169 IF p_start_date > p_end_date THEN
1170 l_return_value := FALSE;
1171 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_ACCT_START_DATE');
1172 FND_MESSAGE.SET_TOKEN('ACTIVE_START_DATE',p_start_date);
1173 FND_MSG_PUB.Add;
1174 RETURN l_return_value;
1175 END IF;
1176 END IF;
1177
1178 OPEN c1;
1179 FETCH c1 INTO l_inst_party_start_date;
1180 IF c1%NOTFOUND THEN
1181 l_return_value := FALSE;
1182 IF ( p_stack_err_msg = TRUE ) THEN
1183 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_PTY_START_DATE');
1184 FND_MESSAGE.SET_TOKEN('ACTIVE_START_DATE',p_start_date);
1185 FND_MSG_PUB.Add;
1186 END IF;
1187 CLOSE c1;
1188 RETURN l_return_value;
1189 END IF;
1190
1191 -- dbms_output.put_line('p_start-date :'||to_char(p_start_date));
1192 -- dbms_output.put_line('l_inst_party_start_date :'||to_char(l_inst_party_start_date));
1193
1194 IF (p_start_date < l_inst_party_start_date)
1195 OR (p_start_date > SYSDATE) THEN
1196 l_return_value := FALSE;
1197 IF ( p_stack_err_msg = TRUE ) THEN
1198 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_ACCT_START_DATE');
1199 FND_MESSAGE.SET_TOKEN('ACTIVE_START_DATE',p_start_date);
1200 FND_MSG_PUB.Add;
1201 END IF;
1202 END IF;
1203 RETURN l_return_value;
1204
1205 END Is_Acct_StartDate_Valid;
1206
1207 /*-----------------------------------------------------------*/
1208 /* Procedure name: Is_Acct_EndDate_Valid */
1209 /* Description : Check if the Account active End date */
1210 /* is valid */
1211 /*-----------------------------------------------------------*/
1212
1213 /*
1214 FUNCTION Is_Acct_EndDate_Valid
1215 (
1216 p_start_date IN DATE,
1217 p_end_date IN DATE,
1218 p_inst_party_id IN NUMBER,
1219 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
1220 ) RETURN BOOLEAN IS
1221
1222 l_instance_end_date DATE;
1223 l_return_value BOOLEAN := TRUE;
1224
1225 CURSOR c1 IS
1226 SELECT active_end_date
1227 FROM csi_i_parties
1228 WHERE instance_party_id = p_inst_party_id
1229 and ((active_end_date is null) OR (active_end_date >= sysdate));
1230 BEGIN
1231 IF p_end_date is NOT NULL THEN
1232 IF p_end_date < sysdate THEN
1233 l_return_value := FALSE;
1234 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_ACCT_END_DATE');
1235 FND_MESSAGE.SET_TOKEN('ACTIVE_END_DATE',p_end_date);
1236 FND_MSG_PUB.Add;
1237 RETURN l_return_value;
1238 END IF;
1239 END IF;
1240
1241 OPEN c1;
1242 FETCH c1 INTO l_instance_end_date;
1243
1244 IF l_instance_end_date is NOT NULL THEN
1245 IF p_end_date > l_instance_end_date THEN
1246 l_return_value := FALSE;
1247 IF ( p_stack_err_msg = TRUE ) THEN
1248 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_ACCT_END_DATE');
1249 FND_MESSAGE.SET_TOKEN('ACTIVE_END_DATE',p_end_date);
1250 FND_MSG_PUB.Add;
1251 END IF;
1252 END IF;
1253 END IF;
1254 CLOSE c1;
1255 RETURN l_return_value;
1256 END Is_Acct_EndDate_Valid;
1257
1258 */
1259
1260 /*-----------------------------------------------------------*/
1261 /* Procedure name: Is_Acct_EndDate_Valid */
1262 /* Description : Check if the Account active End date */
1263 /* is valid */
1264 /*-----------------------------------------------------------*/
1265
1266 FUNCTION Is_Acct_EndDate_Valid
1267 (
1268 p_start_date IN DATE,
1269 p_end_date IN DATE,
1270 p_inst_party_id IN NUMBER,
1271 p_ip_account_id IN NUMBER,
1272 p_txn_id IN NUMBER,
1273 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
1274 ) RETURN BOOLEAN IS
1275
1276 l_instance_end_date DATE;
1277 l_return_value BOOLEAN := TRUE;
1278 l_transaction_date DATE;
1279
1280 CURSOR c1 IS
1281 SELECT active_end_date
1282 FROM csi_i_parties
1283 WHERE instance_party_id = p_inst_party_id
1284 and ((active_end_date is null) OR (active_end_date >= sysdate));
1285 BEGIN
1286 IF ((p_ip_account_id IS NULL) OR (p_ip_account_id = FND_API.G_MISS_NUM))
1287 THEN
1288 IF ((p_end_date is NOT NULL) and (p_end_date <> fnd_api.g_miss_date))
1289 THEN
1290 IF p_end_date < sysdate
1291 THEN
1292 l_return_value := FALSE;
1293 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_ACCT_END_DATE');
1294 FND_MESSAGE.SET_TOKEN('ACTIVE_END_DATE',p_end_date);
1295 FND_MSG_PUB.Add;
1296 l_return_value := FALSE;
1297 RETURN l_return_value;
1298 END IF;
1299 END IF;
1300 RETURN l_return_value;
1301
1302 ELSE
1303
1304 IF p_end_date < sysdate THEN
1305 SELECT MAX(t.source_transaction_date) -- Changed from Transaction_date to Source_transaction_date
1306 INTO l_transaction_date
1307 FROM csi_ip_accounts_h s,
1308 csi_transactions t
1309 WHERE s.ip_account_id=p_ip_account_id
1310 AND s.transaction_id=t.transaction_id
1311 AND t.transaction_id <> nvl(p_txn_id, -99999);
1312
1313 IF l_transaction_date > p_end_date
1314 THEN
1315 fnd_message.set_name('CSI','CSI_HAS_TXNS');
1316 fnd_message.set_token('END_DATE_ACTIVE',p_end_date);
1317 fnd_msg_pub.add;
1318 l_return_value := FALSE;
1319 RETURN l_return_value;
1320 END IF;
1321 END IF;
1322
1323 IF ((p_end_date is not null) and (p_end_date <> fnd_api.g_miss_date)) then
1324
1325 OPEN c1;
1326 FETCH c1 INTO l_instance_end_date;
1327
1328 IF l_instance_end_date is NOT NULL THEN
1329 IF p_end_date > l_instance_end_date THEN
1330 l_return_value := FALSE;
1331 IF ( p_stack_err_msg = TRUE ) THEN
1332 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_ACCT_END_DATE');
1333 FND_MESSAGE.SET_TOKEN('ACTIVE_END_DATE',p_end_date);
1334 FND_MSG_PUB.Add;
1335 END IF;
1336 END IF;
1337 END IF;
1338 CLOSE c1;
1339 END IF;
1340 END IF;
1341 RETURN l_return_value;
1342 END Is_Acct_EndDate_Valid;
1343
1344
1345
1346 /*-----------------------------------------------------------*/
1347 /* Procedure name: gen_ip_account_id */
1348 /* Description : Generate ip_account_id from the sequence */
1349 /*-----------------------------------------------------------*/
1350
1351 FUNCTION gen_ip_account_id
1352 RETURN NUMBER IS
1353 l_ip_account_id NUMBER;
1354 BEGIN
1355
1356 SELECT CSI_IP_ACCOUNTS_S.nextval
1357 INTO l_ip_account_id
1358 FROM sys.dual;
1359 RETURN l_ip_account_id ;
1360 END gen_ip_account_id;
1361
1362 /*-----------------------------------------------------------*/
1363 /* Procedure name: gen_ip_account_hist_id */
1364 /* Description : Generate ip_account_hist_id from */
1365 /* the sequence */
1366 /*-----------------------------------------------------------*/
1367
1368 FUNCTION gen_ip_account_hist_id
1369 RETURN NUMBER IS
1370 l_ip_account_hist_id NUMBER;
1371 BEGIN
1372 SELECT CSI_IP_ACCOUNTS_H_S.nextval
1373 INTO l_ip_account_hist_id
1374 FROM sys.dual;
1375 RETURN l_ip_account_hist_id ;
1376 END gen_ip_account_hist_id;
1377
1378 /*------------------------------------------------------------*/
1379 /* Procedure name: Is_datetimestamp_Valid */
1380 /* Description : Check if datetimestamp is greater than */
1381 /* start effective date but less than the end effective date */
1382 /*------------------------------------------------------------*/
1383
1384 FUNCTION Is_timestamp_Valid
1385 (
1386 p_datetimestamp IN DATE,
1387 p_instance_id IN NUMBER,
1388 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
1389 ) RETURN BOOLEAN IS
1390
1391 l_instance_start_date DATE;
1392 l_instance_end_date DATE;
1393 l_return_value BOOLEAN := TRUE;
1394
1395 CURSOR c1 IS
1396 SELECT active_start_date,
1397 active_end_date
1398 FROM csi_item_instances
1399 WHERE instance_id = p_instance_id
1400 and ((active_end_date is null) OR (active_end_date >= sysdate));
1401 BEGIN
1402 OPEN c1;
1403 FETCH c1 INTO l_instance_start_date,l_instance_end_date;
1404 IF c1%NOTFOUND THEN
1405 l_return_value := FALSE;
1406 IF ( p_stack_err_msg = TRUE ) THEN
1407 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVAL_INST_START_DATE');
1408 FND_MESSAGE.SET_TOKEN('ENTITY','VERSION LABEL');
1409 FND_MSG_PUB.Add;
1410 END IF;
1411 CLOSE c1;
1412 RETURN l_return_value;
1413 END IF;
1414
1415 IF ((p_datetimestamp < l_instance_start_date) AND
1416 ( p_datetimestamp > l_instance_end_date)) THEN
1417 l_return_value := FALSE;
1418 IF ( p_stack_err_msg = TRUE ) THEN
1419 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_TIMESTAMP');
1420 FND_MSG_PUB.Add;
1421 END IF;
1422 END IF;
1423 RETURN l_return_value;
1424
1425 END Is_timestamp_Valid;
1426
1427 /*-----------------------------------------------------------*/
1428 /* Procedure name: gen_ver_label_id */
1429 /* Description : Generate version_label_id from */
1430 /* the sequence */
1431 /*-----------------------------------------------------------*/
1432
1433 FUNCTION gen_ver_label_id
1434 RETURN NUMBER IS
1435
1436 l_version_label_id NUMBER;
1437
1438 BEGIN
1439 SELECT CSI_I_VERSION_LABELS_S.nextval
1440 INTO l_version_label_id
1441 FROM sys.dual;
1442 RETURN l_version_label_id;
1443 END gen_ver_label_id;
1444
1445 /*-----------------------------------------------------------*/
1446 /* Procedure name: Is_Ver_labelID_exists */
1447 /* Description : Check if the version_label_id */
1448 /* exists in csi_i_version_labels */
1449 /*-----------------------------------------------------------*/
1450
1451 FUNCTION Is_Ver_labelID_exists
1452 ( p_version_label_id IN NUMBER,
1453 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
1454 ) RETURN BOOLEAN IS
1455
1456 l_dummy VARCHAR2(1);
1457 l_return_value BOOLEAN := TRUE;
1458 BEGIN
1459 SELECT 'x'
1460 INTO l_dummy
1461 FROM csi_i_version_labels
1462 WHERE version_label_id = p_version_label_id
1463 and ((active_end_date is null) OR (active_end_date >= sysdate));
1464 IF ( p_stack_err_msg = TRUE ) THEN
1465 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_VER_LABEL_ID');
1466 FND_MESSAGE.SET_TOKEN('VERSION_LABEL_ID',p_version_label_id);
1467 FND_MSG_PUB.Add;
1468 END IF;
1469 RETURN l_return_value;
1470 EXCEPTION
1471 WHEN NO_DATA_FOUND THEN
1472 l_return_value := FALSE;
1473 RETURN l_return_value;
1474 END Is_Ver_labelID_exists;
1475
1476 /*-----------------------------------------------------------*/
1477 /* Procedure name: gen_ver_label_hist_id */
1478 /* Description : Generate version_label_hist_id from */
1479 /* the sequence */
1480 /*-----------------------------------------------------------*/
1481
1482 FUNCTION gen_ver_label_hist_id
1483 RETURN NUMBER IS
1484 l_version_label_hist_id NUMBER;
1485
1486 BEGIN
1487 SELECT CSI_I_VERSION_LABELS_H_S.nextval
1488 INTO l_version_label_hist_id
1489 FROM sys.dual;
1490
1491 RETURN l_version_label_hist_id;
1492
1493 END gen_ver_label_hist_id;
1494
1495 /*-----------------------------------------------------------*/
1496 /* Procedure name: gen_inst_asset_id */
1497 /* Description : Generate instance asset id from */
1498 /* the sequence */
1499 /*-----------------------------------------------------------*/
1500
1501 FUNCTION gen_inst_asset_id
1502 RETURN NUMBER IS
1503
1504 l_inst_asset_id NUMBER;
1505
1506 BEGIN
1507 SELECT CSI_I_ASSETS_S.nextval
1508 INTO l_inst_asset_id
1509 FROM sys.dual;
1510
1511 RETURN l_inst_asset_id;
1512
1513 END gen_inst_asset_id;
1514
1515 /*-----------------------------------------------------------*/
1516 /* Procedure name: Is_Inst_assetID_exists */
1517 /* Description : Check if the instance asset id */
1518 /* exists in csi_i_assets */
1519 /*-----------------------------------------------------------*/
1520
1521 FUNCTION Is_Inst_assetID_exists
1522
1523 ( p_instance_asset_id IN NUMBER,
1524 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
1525 ) RETURN BOOLEAN IS
1526
1527 l_dummy VARCHAR2(1);
1528 l_return_value BOOLEAN := TRUE;
1529 BEGIN
1530 SELECT 'x'
1531 INTO l_dummy
1532 FROM csi_i_assets
1533 WHERE instance_asset_id = p_instance_asset_id ;
1534
1535 IF ( p_stack_err_msg = TRUE ) THEN
1536 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_INST_ASSET_ID');
1537 FND_MESSAGE.SET_TOKEN('INSTANCE_ASSET_ID',p_instance_asset_id);
1538 FND_MSG_PUB.Add;
1539 END IF;
1540 RETURN l_return_value;
1541 EXCEPTION
1542 WHEN NO_DATA_FOUND THEN
1543 l_return_value := FALSE;
1544 RETURN l_return_value;
1545 END Is_Inst_assetID_exists;
1546
1547
1548 /*-----------------------------------------------------------*/
1549 /* Procedure name: Is_Update_Status_Exists */
1550 /* Description : Check if the update status is */
1551 /* defined in CSI_LOOKUPS */
1552 /*-----------------------------------------------------------*/
1553
1554 FUNCTION Is_Update_Status_Exists
1555 (
1556 p_update_status IN VARCHAR2,
1557 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
1558 ) RETURN BOOLEAN IS
1559
1560 l_dummy VARCHAR2(1);
1561 l_return_value BOOLEAN := TRUE;
1562 l_asset_lookup_type VARCHAR2(30) := 'CSI_ASSET_UPDATE_STATUS';
1563
1564 CURSOR c1 IS
1565 SELECT 'x'
1566 FROM CSI_LOOKUPS
1567 WHERE lookup_code = UPPER(p_update_status)
1568 AND lookup_type = l_asset_lookup_type;
1569 BEGIN
1570 OPEN c1;
1571 FETCH c1 INTO l_dummy;
1572 IF c1%NOTFOUND THEN
1573 l_return_value := FALSE;
1574 IF ( p_stack_err_msg = TRUE ) THEN
1575 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_UPDATE_STATUS');
1576 FND_MESSAGE.SET_TOKEN('UPDATE_STATUS',p_update_status);
1577 FND_MSG_PUB.Add;
1578 END IF;
1579 END IF;
1580 CLOSE c1;
1581 RETURN l_return_value;
1582
1583 END Is_Update_Status_Exists;
1584
1585 /*-----------------------------------------------------------*/
1586 /* Procedure name: Is_Quantity_Valid */
1587 /* Description : Check if the asset quantity > 0 */
1588 /*-----------------------------------------------------------*/
1589
1590 FUNCTION Is_Quantity_Valid
1591 (
1592 p_asset_quantity IN NUMBER,
1593 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
1594 ) RETURN BOOLEAN IS
1595
1596 l_return_status BOOLEAN := TRUE;
1597 BEGIN
1598 IF (NVL(p_asset_quantity,-1) <= 0 ) THEN
1599 l_return_status := FALSE;
1600 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_ASSET_QTY');
1601 FND_MESSAGE.SET_TOKEN('QUANTITY',p_asset_quantity);
1602 FND_MSG_PUB.Add;
1603 END IF;
1604
1605 RETURN l_return_status;
1606
1607 END Is_Quantity_Valid;
1608
1609
1610 /*-----------------------------------------------------------*/
1611 /* Procedure name: gen_inst_asset_hist_id */
1612 /* Description : Generate instance asset id from */
1613 /* the sequence */
1614 /*-----------------------------------------------------------*/
1615
1616 FUNCTION gen_inst_asset_hist_id
1617 RETURN NUMBER IS
1618
1619 l_inst_asset_hist_id NUMBER;
1620
1621 BEGIN
1622 SELECT CSI_I_ASSETS_H_S.nextval
1623 INTO l_inst_asset_hist_id
1624 FROM sys.dual;
1625 RETURN l_inst_asset_hist_id;
1626 END gen_inst_asset_hist_id;
1627
1628 /*-----------------------------------------------------------*/
1629 /* Procedure name: Is_Asset_Comb_Valid */
1630 /* Description : Check if the instance asset id and location */
1631 /* id exists in fa_books */
1632 /*-----------------------------------------------------------*/
1633
1634 FUNCTION Is_Asset_Comb_Valid
1635 ( p_asset_id IN NUMBER,
1636 p_book_type_code IN VARCHAR2,
1637 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
1638 ) RETURN BOOLEAN IS
1639 l_dummy VARCHAR2(1);
1640 l_return_value BOOLEAN := TRUE;
1641 BEGIN
1642 SELECT 'x'
1643 INTO l_dummy
1644 FROM fa_books
1645 WHERE asset_id = p_asset_id
1646 and book_type_code = p_book_type_code;
1647
1648 RETURN l_return_value;
1649 EXCEPTION
1650 WHEN NO_DATA_FOUND THEN
1651 l_return_value := FALSE;
1652 IF ( p_stack_err_msg = TRUE ) THEN
1653 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_ASSET_COMB');
1654 FND_MESSAGE.SET_TOKEN('ASSET_COMBINATION',p_asset_id||'-'||p_book_type_code);
1655 FND_MSG_PUB.Add;
1656 END IF;
1657 RETURN l_return_value;
1658 END Is_Asset_Comb_Valid;
1659
1660
1661 /*-----------------------------------------------------------*/
1662 /* Procedure name: Is_Asset_Location_Valid */
1663 /* Description : Check if the instance location id */
1664 /* exists in csi_a_locations */
1665 /*-----------------------------------------------------------*/
1666
1667 FUNCTION Is_Asset_Location_Valid
1668 ( p_location_id IN NUMBER,
1669 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
1670 ) RETURN BOOLEAN IS
1671 l_dummy VARCHAR2(1);
1672 l_return_value BOOLEAN := TRUE;
1673 BEGIN
1674 SELECT 'x'
1675 INTO l_dummy
1676 FROM csi_a_locations
1677 WHERE location_id = p_location_id
1678 and ((active_end_date is null) OR (active_end_date >= sysdate));
1679
1680 RETURN l_return_value;
1681 EXCEPTION
1682 WHEN NO_DATA_FOUND THEN
1683 l_return_value := FALSE;
1684 IF ( p_stack_err_msg = TRUE ) THEN
1685 FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_ASSET_LOCATION');
1686 FND_MESSAGE.SET_TOKEN('ASSET_LOCATION_ID',p_location_id);
1687 FND_MSG_PUB.Add;
1688 END IF;
1689 RETURN l_return_value;
1690 END Is_Asset_Location_Valid;
1691
1692
1693 /*-----------------------------------------------------------*/
1694 /* Procedure name: Is_IP_account_expired */
1695 /* Description : Check if the IP_account_id */
1696 /* is expired */
1697 /*-----------------------------------------------------------*/
1698
1699 FUNCTION Is_IP_account_expired
1700 ( p_ip_account_id IN NUMBER,
1701 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
1702 ) RETURN BOOLEAN IS
1703 l_dummy VARCHAR2(1);
1704 l_return_value BOOLEAN := TRUE;
1705 BEGIN
1706 SELECT 'x'
1707 INTO l_dummy
1708 FROM csi_ip_accounts
1709 WHERE ip_account_id = p_ip_account_id;
1710 RETURN l_return_value;
1711 EXCEPTION
1712 WHEN NO_DATA_FOUND THEN
1713 l_return_value := FALSE;
1714 RETURN l_return_value;
1715 END Is_IP_account_expired ;
1716
1717 /*-----------------------------------------------------------*/
1718 /* Procedure name: Is_IP_account_Valid */
1719 /* Description : Check if the IP_account_id */
1720 /* exists in csi_ip_accounts */
1721 /*-----------------------------------------------------------*/
1722
1723 FUNCTION Is_IP_account_Valid
1724 ( p_ip_account_id IN NUMBER,
1725 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
1726 ) RETURN BOOLEAN IS
1727 l_dummy VARCHAR2(1);
1728 l_return_value BOOLEAN := TRUE;
1729 BEGIN
1730 SELECT 'x'
1731 INTO l_dummy
1732 FROM csi_ip_accounts
1733 WHERE ip_account_id = p_ip_account_id
1734 and ((active_end_date is null) OR (active_end_date >= sysdate));
1735 RETURN l_return_value;
1736 EXCEPTION
1737 WHEN NO_DATA_FOUND THEN
1738 l_return_value := FALSE;
1739 RETURN l_return_value;
1740 END Is_IP_account_Valid ;
1741
1742 /*-----------------------------------------------------------*/
1743 /* Procedure name: Is_bill_to_add_valid */
1744 /* Description : Check if the Bill to address */
1745 /* exists in hz_cust_site_uses */
1746 /*-----------------------------------------------------------*/
1747
1748 FUNCTION Is_bill_to_add_valid
1749 ( p_bill_to_add_id IN NUMBER,
1750 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
1751 ) RETURN BOOLEAN IS
1752 l_dummy VARCHAR2(1);
1753 l_return_value BOOLEAN := TRUE;
1754 BEGIN
1755 SELECT 'x'
1756 INTO l_dummy
1757 FROM hz_cust_site_uses_all
1758 WHERE site_use_id = p_bill_to_add_id
1759 AND site_use_code = 'BILL_TO';
1760 -- and ((active_end_date is null) OR (active_end_date >= sysdate));
1761 RETURN l_return_value;
1762 EXCEPTION
1763 WHEN NO_DATA_FOUND THEN
1764 l_return_value := FALSE;
1765 IF ( p_stack_err_msg = TRUE ) THEN
1766 FND_MESSAGE.SET_NAME('CSI','CSI_INVALID_BILL_TO_ADD_ID');
1767 FND_MESSAGE.SET_TOKEN('BILL_TO_ADD_ID',p_bill_to_add_id );
1768 FND_MSG_PUB.Add;
1769 END IF;
1770 RETURN l_return_value;
1771
1772 END Is_bill_to_add_valid;
1773
1774 /*-----------------------------------------------------------*/
1775 /* Procedure name: Is_ship_to_add_valid */
1776 /* Description : Check if the Ship to address */
1777 /* exists in hz_cust_site_uses */
1778 /*-----------------------------------------------------------*/
1779
1780 FUNCTION Is_ship_to_add_valid
1781 ( p_ship_to_add_id IN NUMBER,
1782 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
1783 ) RETURN BOOLEAN IS
1784 l_dummy VARCHAR2(1);
1785 l_return_value BOOLEAN := TRUE;
1786 BEGIN
1787 SELECT 'x'
1788 INTO l_dummy
1789 FROM hz_cust_site_uses_all
1790 WHERE site_use_id = p_ship_to_add_id
1791 AND site_use_code = 'SHIP_TO';
1792 -- and ((active_end_date is null) OR (active_end_date >= sysdate));
1793 RETURN l_return_value;
1794 EXCEPTION
1795 WHEN NO_DATA_FOUND THEN
1796 l_return_value := FALSE;
1797 IF ( p_stack_err_msg = TRUE ) THEN
1798 FND_MESSAGE.SET_NAME('CSI','CSI_INVALID_SHIP_TO_ADD_ID');
1799 FND_MESSAGE.SET_TOKEN('SHIP_TO_ADD_ID',p_ship_to_add_id );
1800 FND_MSG_PUB.Add;
1801 END IF;
1802 RETURN l_return_value;
1803
1804 END Is_ship_to_add_valid;
1805
1806
1807 /*-----------------------------------------------------------*/
1808 /* Procedure name: Acct_Rules_Check */
1809 /* Description : Check if specific party account */
1810 /* rules are ok */
1811 /*-----------------------------------------------------------*/
1812
1813 FUNCTION Acct_Rules_Check
1814 (
1815 p_instance_party_id IN NUMBER ,
1816 p_relationship_type IN VARCHAR2,
1817 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
1818 ) RETURN BOOLEAN IS
1819
1820 l_dummy VARCHAR2(1);
1821 l_return_value BOOLEAN := TRUE;
1822 l_party_relation VARCHAR2(30);
1823
1824 BEGIN
1825
1826 SELECT 'x'
1827 INTO l_dummy
1828 FROM csi_ip_accounts
1829 WHERE instance_party_id = p_instance_party_id
1830 and relationship_type_code = p_relationship_type
1831 and ((active_end_date is null) OR (active_end_date > sysdate));
1832 IF ( p_stack_err_msg = TRUE ) THEN
1833 FND_MESSAGE.SET_NAME('CSI','CSI_API_DUP_ACCT_TYPE');
1834 FND_MESSAGE.SET_TOKEN('RELATIONSHIP_TYPE_CODE',p_relationship_type);
1835 FND_MSG_PUB.Add;
1836 END IF;
1837
1838 -- Vaidate if owner accounts are created, the party is also an owner
1839 l_party_relation := CSI_Instance_parties_vld_pvt.Get_Party_relation
1840 (p_Instance_party_id);
1841
1842 IF ((p_relationship_type = 'OWNER')
1843 AND (l_party_relation <> 'OWNER')) THEN
1844 IF ( p_stack_err_msg = TRUE ) THEN
1845 FND_MESSAGE.SET_NAME('CSI','CSI_OWNER_ACCT_REQ_OWNER_PTY');
1846 FND_MSG_PUB.Add;
1847 END IF;
1848 END IF;
1849 RETURN l_return_value;
1850 EXCEPTION
1851 WHEN NO_DATA_FOUND THEN
1852 l_return_value := FALSE;
1853 RETURN l_return_value;
1854 END Acct_Rules_Check;
1855
1856 /*-----------------------------------------------------------*/
1857 /* Procedure name: Is_Party_Contact_Exists */
1858 /* Description : Check if the Party Contact */
1859 /* already exists */
1860 /*-----------------------------------------------------------*/
1861
1862 FUNCTION Is_Party_Contact_Exists
1863 ( p_contact_ip_id IN NUMBER ,
1864 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
1865 ) RETURN BOOLEAN IS
1866
1867 l_inst_party_id NUMBER;
1868 l_return_value BOOLEAN := TRUE;
1869
1870 BEGIN
1871 SELECT instance_party_id
1872 INTO l_inst_party_id
1873 FROM csi_i_parties
1874 WHERE contact_ip_id = p_contact_ip_id
1875 AND contact_flag = 'Y';
1876
1877 l_return_value := FALSE;
1878 IF ( p_stack_err_msg = TRUE ) THEN
1879 FND_MESSAGE.SET_NAME('CSI','CSI_API_PTY_CONTACT_EXISTS');
1880 FND_MSG_PUB.Add;
1881 END IF;
1882 RETURN l_return_value;
1883 EXCEPTION
1884 WHEN NO_DATA_FOUND THEN
1885 l_return_value := TRUE;
1886 RETURN l_return_value;
1887 WHEN TOO_MANY_ROWS THEN
1888 l_return_value := FALSE;
1889 IF ( p_stack_err_msg = TRUE ) THEN
1890 FND_MESSAGE.SET_NAME('CSI','CSI_API_PTY_CONTACT_EXISTS');
1891 FND_MSG_PUB.Add;
1892 END IF;
1893 RETURN l_return_value;
1894 END Is_Party_Contact_Exists;
1895
1896
1897 /*-----------------------------------------------------------*/
1898 /* Procedure name: Get_Party_relation */
1899 /* Description : Get the Party relationship type */
1900 /*-----------------------------------------------------------*/
1901
1902 FUNCTION Get_Party_relation
1903 ( p_Instance_party_id IN NUMBER,
1904 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
1905 ) RETURN VARCHAR2 IS
1906
1907 l_rel_type_code VARCHAR2(30);
1908 BEGIN
1909 SELECT relationship_type_code
1910 INTO l_rel_type_code
1911 FROM csi_i_parties
1912 WHERE instance_party_id = p_Instance_party_id;
1913 RETURN l_rel_type_code;
1914 EXCEPTION
1915 WHEN NO_DATA_FOUND THEN
1916 IF ( p_stack_err_msg = TRUE ) THEN
1917 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INST_PARTY_ID');
1918 FND_MESSAGE.SET_TOKEN('INSTANCE_PARTY_ID',p_instance_party_id);
1919 FND_MSG_PUB.Add;
1920 END IF;
1921 RETURN NULL;
1922 END Get_Party_relation;
1923
1924
1925 /*-----------------------------------------------------------*/
1926 /* Procedure name: Get_Party_Record */
1927 /* Description : Get Party Record for the account */
1928 /*-----------------------------------------------------------*/
1929
1930 FUNCTION Get_Party_Record
1931 ( p_Instance_party_id IN NUMBER,
1932 p_party_rec OUT NOCOPY csi_datastructures_pub.party_rec,
1933 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
1934 ) RETURN BOOLEAN IS
1935
1936 BEGIN
1937 SELECT
1938 instance_party_id ,
1939 instance_id ,
1940 party_source_table ,
1941 party_id ,
1942 relationship_type_code ,
1943 contact_flag ,
1944 contact_ip_id ,
1945 active_start_date ,
1946 active_end_date ,
1947 context ,
1948 attribute1 ,
1949 attribute2 ,
1950 attribute3 ,
1951 attribute4 ,
1952 attribute5 ,
1953 attribute6 ,
1954 attribute7 ,
1955 attribute8 ,
1956 attribute9 ,
1957 attribute10 ,
1958 attribute11 ,
1959 attribute12 ,
1960 attribute13 ,
1961 attribute14 ,
1962 attribute15 ,
1963 object_version_number ,
1964 primary_flag ,
1965 preferred_flag
1966 INTO
1967 p_party_rec.instance_party_id ,
1968 p_party_rec.instance_id ,
1969 p_party_rec.party_source_table ,
1970 p_party_rec.party_id ,
1971 p_party_rec.relationship_type_code ,
1972 p_party_rec.contact_flag ,
1973 p_party_rec.contact_ip_id ,
1974 p_party_rec.active_start_date ,
1975 p_party_rec.active_end_date ,
1976 p_party_rec.context ,
1977 p_party_rec.attribute1 ,
1978 p_party_rec.attribute2 ,
1979 p_party_rec.attribute3 ,
1980 p_party_rec.attribute4 ,
1981 p_party_rec.attribute5 ,
1982 p_party_rec.attribute6 ,
1983 p_party_rec.attribute7 ,
1984 p_party_rec.attribute8 ,
1985 p_party_rec.attribute9 ,
1986 p_party_rec.attribute10 ,
1987 p_party_rec.attribute11 ,
1988 p_party_rec.attribute12 ,
1989 p_party_rec.attribute13 ,
1990 p_party_rec.attribute14 ,
1991 p_party_rec.attribute15 ,
1992 p_party_rec.object_version_number ,
1993 p_party_rec.primary_flag ,
1994 p_party_rec.preferred_flag
1995 FROM csi_i_parties
1996 WHERE instance_party_id = p_Instance_party_id;
1997 RETURN TRUE;
1998 EXCEPTION
1999 WHEN NO_DATA_FOUND THEN
2000 IF ( p_stack_err_msg = TRUE ) THEN
2001 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INST_PARTY_ID');
2002 FND_MESSAGE.SET_TOKEN('INSTANCE_PARTY_ID',p_instance_party_id);
2003 FND_MSG_PUB.Add;
2004 END IF;
2005 RETURN FALSE;
2006 END Get_Party_Record;
2007
2008
2009 /*-----------------------------------------------------------*/
2010 /* Procedure name: Is_Account_Expired */
2011 /* Description : Is the account expired */
2012 /*-----------------------------------------------------------*/
2013
2014 FUNCTION Is_Account_Expired
2015 (p_party_account_rec IN csi_datastructures_pub.party_account_rec
2016 ,p_stack_err_msg IN BOOLEAN DEFAULT TRUE
2017 ) RETURN BOOLEAN IS
2018
2019 l_dummy VARCHAR2(1);
2020 l_return_value BOOLEAN := TRUE;
2021
2022 BEGIN
2023
2024 SELECT 'x'
2025 INTO l_dummy
2026 FROM csi_ip_accounts
2027 WHERE instance_party_id = p_party_account_rec.instance_party_id
2028 AND party_account_id = p_party_account_rec.party_account_id
2029 AND relationship_type_code = p_party_account_rec.relationship_type_code
2030 AND active_start_date = p_party_account_rec.active_start_date
2031 AND active_end_date < sysdate
2032 AND decode(p_party_account_rec.bill_to_address,
2033 NULL, NVL(bill_to_address, 1),
2034 bill_to_address) = NVL(p_party_account_rec.bill_to_address, 1)
2035 AND decode(p_party_account_rec.ship_to_address,
2036 NULL, NVL(ship_to_address, 1),
2037 ship_to_address) = NVL(p_party_account_rec.ship_to_address, 1)
2038 AND decode(p_party_account_rec.context,
2039 NULL, NVL(context, '1'),
2040 context) = NVL(p_party_account_rec.context, '1')
2041 AND decode(p_party_account_rec.attribute1,
2042 NULL, NVL(attribute1, '1'),
2043 attribute1) = NVL(p_party_account_rec.attribute1, '1')
2044 AND decode(p_party_account_rec.attribute2,
2045 NULL, NVL(attribute2, '1'),
2046 attribute2) = NVL(p_party_account_rec.attribute2, '1')
2047 AND decode(p_party_account_rec.attribute3,
2048 NULL, NVL(attribute3, '1'),
2049 attribute3) = NVL(p_party_account_rec.attribute3, '1')
2050 AND decode(p_party_account_rec.attribute4,
2051 NULL, NVL(attribute4, '1'),
2052 attribute4) = NVL(p_party_account_rec.attribute4, '1')
2053 AND decode(p_party_account_rec.attribute5,
2054 NULL, NVL(attribute5, '1'),
2055 attribute5) = NVL(p_party_account_rec.attribute5, '1')
2056 AND decode(p_party_account_rec.attribute6,
2057 NULL, NVL(attribute6, '1'),
2058 attribute6) = NVL(p_party_account_rec.attribute6, '1')
2059 AND decode(p_party_account_rec.attribute7,
2060 NULL, NVL(attribute7, '1'),
2061 attribute7) = NVL(p_party_account_rec.attribute7, '1')
2062 AND decode(p_party_account_rec.attribute8,
2063 NULL, NVL(attribute8, '1'),
2064 attribute8) = NVL(p_party_account_rec.attribute8, '1')
2065 AND decode(p_party_account_rec.attribute9,
2066 NULL, NVL(attribute9, '1'),
2067 attribute9) = NVL(p_party_account_rec.attribute9, '1')
2068 AND decode(p_party_account_rec.attribute10,
2069 NULL, NVL(attribute10, '1'),
2070 attribute10) = NVL(p_party_account_rec.attribute10, '1')
2071 AND decode(p_party_account_rec.attribute11,
2072 NULL, NVL(attribute11, '1'),
2073 attribute11) = NVL(p_party_account_rec.attribute11, '1')
2074 AND decode(p_party_account_rec.attribute12,
2075 NULL, NVL(attribute12, '1'),
2076 attribute12) = NVL(p_party_account_rec.attribute12, '1')
2077 AND decode(p_party_account_rec.attribute13,
2078 NULL, NVL(attribute13, '1'),
2079 attribute13) = NVL(p_party_account_rec.attribute13, '1')
2080 AND decode(p_party_account_rec.attribute14,
2081 NULL, NVL(attribute14, '1'),
2082 attribute14) = NVL(p_party_account_rec.attribute14, '1')
2083 AND decode(p_party_account_rec.attribute15,
2084 NULL, NVL(attribute15, '1'),
2085 attribute15) = NVL(p_party_account_rec.attribute15, '1');
2086
2087 RETURN l_return_value;
2088 /*
2089 AND bill_to_address = p_party_account_rec.bill_to_address
2090 AND ship_to_address = p_party_account_rec.ship_to_address
2091
2092 AND context = p_party_account_rec.context
2093 AND attribute1 = p_party_account_rec.attribute1
2094 AND attribute2 = p_party_account_rec.attribute2
2095 AND attribute3 = p_party_account_rec.attribute3
2096 AND attribute4 = p_party_account_rec.attribute4
2097 AND attribute5 = p_party_account_rec.attribute5
2098 AND attribute6 = p_party_account_rec.attribute6
2099 AND attribute7 = p_party_account_rec.attribute7
2100 AND attribute8 = p_party_account_rec.attribute8
2101 AND attribute9 = p_party_account_rec.attribute9
2102 AND attribute10 = p_party_account_rec.attribute10
2103 AND attribute11 = p_party_account_rec.attribute11
2104 AND attribute12 = p_party_account_rec.attribute12
2105 AND attribute13 = p_party_account_rec.attribute13
2106 AND attribute14 = p_party_account_rec.attribute14
2107 AND attribute15 = p_party_account_rec.attribute15;
2108 */
2109
2110 EXCEPTION
2111 WHEN NO_DATA_FOUND THEN
2112 l_return_value := FALSE;
2113 RETURN l_return_value;
2114
2115 END Is_Account_Expired;
2116
2117 /*-----------------------------------------------------------*/
2118 /* Procedure name: Is_Party_Expired */
2119 /* Description : Is the party expired */
2120 /*-----------------------------------------------------------*/
2121
2122 FUNCTION Is_Party_Expired
2123 ( p_party_rec IN csi_datastructures_pub.party_rec
2124 ,p_stack_err_msg IN BOOLEAN DEFAULT TRUE
2125 ) RETURN BOOLEAN IS
2126 l_dummy VARCHAR2(1);
2127 l_return_value BOOLEAN := TRUE;
2128
2129 BEGIN
2130
2131 SELECT 'x'
2132 INTO l_dummy
2133 FROM csi_i_parties
2134 WHERE instance_id = p_party_rec.instance_id
2135 AND party_source_table = p_party_rec.party_source_table
2136 AND party_id = p_party_rec.party_id
2137 AND relationship_type_code = p_party_rec.relationship_type_code
2138 AND contact_flag = p_party_rec.contact_flag
2139 AND decode(p_party_rec.contact_ip_id,
2140 NULL, NVL(contact_ip_id, 1),
2141 contact_ip_id) = NVL(p_party_rec.contact_ip_id, 1)
2142 AND active_start_date = p_party_rec.active_start_date
2143 AND active_end_date < sysdate
2144 AND decode(p_party_rec.context,
2145 NULL, NVL(context, '1'),
2146 context) = NVL(p_party_rec.context, '1')
2147 AND decode(p_party_rec.attribute1,
2148 NULL, NVL(attribute1, '1'),
2149 attribute1) = NVL(p_party_rec.attribute1, '1')
2150 AND decode(p_party_rec.attribute2,
2151 NULL, NVL(attribute2, '1'),
2152 attribute2) = NVL(p_party_rec.attribute2, '1')
2153 AND decode(p_party_rec.attribute3,
2154 NULL, NVL(attribute3, '1'),
2155 attribute3) = NVL(p_party_rec.attribute3, '1')
2156 AND decode(p_party_rec.attribute4,
2157 NULL, NVL(attribute4, '1'),
2158 attribute4) = NVL(p_party_rec.attribute4, '1')
2159 AND decode(p_party_rec.attribute5,
2160 NULL, NVL(attribute5, '1'),
2161 attribute5) = NVL(p_party_rec.attribute5, '1')
2162 AND decode(p_party_rec.attribute6,
2163 NULL, NVL(attribute6, '1'),
2164 attribute6) = NVL(p_party_rec.attribute6, '1')
2165 AND decode(p_party_rec.attribute7,
2166 NULL, NVL(attribute7, '1'),
2167 attribute7) = NVL(p_party_rec.attribute7, '1')
2168 AND decode(p_party_rec.attribute8,
2169 NULL, NVL(attribute8, '1'),
2170 attribute8) = NVL(p_party_rec.attribute8, '1')
2171 AND decode(p_party_rec.attribute9,
2172 NULL, NVL(attribute9, '1'),
2173 attribute9) = NVL(p_party_rec.attribute9, '1')
2174 AND decode(p_party_rec.attribute10,
2175 NULL, NVL(attribute10, '1'),
2176 attribute10) = NVL(p_party_rec.attribute10, '1')
2177 AND decode(p_party_rec.attribute11,
2178 NULL, NVL(attribute11, '1'),
2179 attribute11) = NVL(p_party_rec.attribute11, '1')
2180 AND decode(p_party_rec.attribute12,
2181 NULL, NVL(attribute12, '1'),
2182 attribute12) = NVL(p_party_rec.attribute12, '1')
2183 AND decode(p_party_rec.attribute13,
2184 NULL, NVL(attribute13, '1'),
2185 attribute13) = NVL(p_party_rec.attribute13, '1')
2186 AND decode(p_party_rec.attribute14,
2187 NULL, NVL(attribute14, '1'),
2188 attribute14) = NVL(p_party_rec.attribute14, '1')
2189 AND decode(p_party_rec.attribute15,
2190 NULL, NVL(attribute15, '1'),
2191 attribute15) = NVL(p_party_rec.attribute15, '1');
2192
2193 RETURN l_return_value;
2194 EXCEPTION
2195 WHEN NO_DATA_FOUND THEN
2196 l_return_value := FALSE;
2197 RETURN l_return_value;
2198
2199 END Is_Party_Expired;
2200
2201
2202 /*-----------------------------------------------------------*/
2203 /* Procedure name: Transfer_Party_Rules */
2204 /* Description : Expire accounts of the party if party is */
2205 /* being changed */
2206 /*-----------------------------------------------------------*/
2207
2208 PROCEDURE Transfer_Party_Rules
2209 ( p_api_version IN NUMBER
2210 ,p_commit IN VARCHAR2 := fnd_api.g_false
2211 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2212 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
2213 ,p_party_rec IN csi_datastructures_pub.party_rec
2214 ,p_stack_err_msg IN BOOLEAN DEFAULT TRUE
2215 ,p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec
2216 ,x_return_status OUT NOCOPY VARCHAR2
2217 ,x_msg_count OUT NOCOPY NUMBER
2218 ,x_msg_data OUT NOCOPY VARCHAR2
2219 ) IS
2220
2221 l_api_name CONSTANT VARCHAR2(30) := 'Transfer Party Rules ';
2222 l_api_version CONSTANT NUMBER := 1.0;
2223 l_csi_debug_level NUMBER;
2224 l_msg_count NUMBER;
2225 l_msg_index NUMBER;
2226 l_party_account_rec csi_datastructures_pub.party_account_rec;
2227
2228
2229 CURSOR GET_IP_ACCOUNT (i_inst_party_id IN NUMBER) IS
2230 SELECT ip_account_id,
2231 relationship_type_code, -- Added by sguthiva for bug 2307804
2232 party_account_id, -- Added by sguthiva for bug 2307804
2233 object_version_number
2234 FROM csi_ip_accounts
2235 WHERE instance_party_id = i_inst_party_id
2236 AND (( ACTIVE_END_DATE IS NULL) OR (ACTIVE_END_DATE >= SYSDATE)) ;
2237
2238 BEGIN
2239
2240 -- Standard Start of API savepoint
2241 -- SAVEPOINT Transfer_Party_Rules;
2242
2243 -- Standard call to check for call compatibility.
2244 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2245 p_api_version ,
2246 l_api_name ,
2247 g_pkg_name )
2248 THEN
2249 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2250 END IF;
2251
2252 -- Initialize message list if p_init_msg_list is set to TRUE.
2253 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2254 FND_MSG_PUB.initialize;
2255 END IF;
2256
2257 -- Initialize API return status to success
2258 -- Verify if the Party rel combination exists
2259 x_return_status := FND_API.G_RET_STS_SUCCESS;
2260
2261 -- Check the profile option CSI_DEBUG_LEVEL for debug message reporting
2262 l_csi_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
2263
2264 -- If CSI_DEBUG_LEVEL = 1 then dump the procedure name
2265 IF (l_csi_debug_level > 0) THEN
2266 csi_gen_utility_pvt.put_line( 'Transfer_Party_Rules');
2267 END IF;
2268
2269 -- If the debug level = 2 then dump all the parameters values.
2270 IF (l_csi_debug_level > 1) THEN
2271 csi_gen_utility_pvt.put_line( 'Transfer_Party_Rules:'||
2272 p_api_version ||'-'||
2273 p_commit ||'-'||
2274 p_init_msg_list );
2275
2276 -- Dump the records in the log file
2277 csi_gen_utility_pvt.dump_party_rec(p_party_rec );
2278 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
2279 END IF;
2280
2281
2282 -- csi_party_relationships_pvt.g_force_expire_flag := 'Y'; -- commented by sguthiva for bug 2307804
2283 -- Expire accounts for the party
2284 FOR C1 IN GET_IP_ACCOUNT(p_party_rec.instance_party_id)
2285 LOOP
2286 IF GET_IP_ACCOUNT%FOUND THEN
2287 l_party_account_rec.ip_account_id := C1.ip_account_id;
2288 l_party_account_rec.expire_flag := fnd_api.g_true; -- Added by sguthiva for bug 2307804
2289 l_party_account_rec.object_version_number := C1.object_version_number;
2290 -- Added by sguthiva for bug 2307804
2291 IF C1.relationship_type_code <> 'OWNER'
2292 THEN
2293 --End Addition by sguthiva for bug 2307804
2294
2295 csi_party_relationships_pvt.expire_inst_party_account
2296 ( p_api_version => p_api_version
2297 ,p_commit => p_commit
2298 ,p_init_msg_list => p_init_msg_list
2299 ,p_validation_level => p_validation_level
2300 ,p_party_account_rec => l_party_account_rec
2301 ,p_txn_rec => p_txn_rec
2302 ,x_return_status => x_return_status
2303 ,x_msg_count => x_msg_count
2304 ,x_msg_data => x_msg_data );
2305
2306 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2307 l_msg_index := 1;
2308 l_msg_count := x_msg_count;
2309 WHILE l_msg_count > 0 LOOP
2310 x_msg_data := FND_MSG_PUB.GET(
2311 l_msg_index,
2312 FND_API.G_FALSE);
2313 csi_gen_utility_pvt.put_line( 'message data = Error from csi_party_relationships_pvt.expire_inst_party_account');
2314 csi_gen_utility_pvt.put_line( 'message data = '||x_msg_data);
2315 l_msg_index := l_msg_index + 1;
2316 l_msg_count := l_msg_count - 1;
2317 END LOOP;
2318 RAISE FND_API.G_EXC_ERROR;
2319 END IF;
2320 END IF; -- Added by sguthiva for bug 2307804
2321 END IF;
2322 END LOOP;
2323 -- csi_party_relationships_pvt.g_force_expire_flag := 'N'; -- commented by sguthiva for bug 2307804
2324
2325
2326 -- Check if the party in question has a contact
2327 /* The following code has been added for bug 2127250 */
2328 IF p_party_rec.contact_ip_id IS NOT NULL
2329 AND p_party_rec.contact_ip_id <> fnd_api.g_miss_num
2330 THEN
2331 /* End of addition for bug 2127250 */
2332 IF (CSI_Instance_parties_vld_pvt.Is_Party_Contact_Exists
2333 (p_party_rec.contact_ip_id,
2334 TRUE)) THEN
2335 RAISE FND_API.G_EXC_ERROR;
2336 END IF;
2337 END IF;
2338
2339 -- End of API body
2340
2341 -- Standard check of p_commit.
2342 IF FND_API.To_Boolean( p_commit ) THEN
2343 COMMIT WORK;
2344 END IF;
2345
2346
2347 -- Standard call to get message count and if count is get message info.
2348 FND_MSG_PUB.Count_And_Get
2349 (p_count => x_msg_count ,
2350 p_data => x_msg_data );
2351
2352 EXCEPTION
2353 WHEN FND_API.G_EXC_ERROR THEN
2354 -- ROLLBACK TO Transfer_Party_Rules;
2355 x_return_status := FND_API.G_RET_STS_ERROR ;
2356 FND_MSG_PUB.Count_And_Get
2357 ( p_count => x_msg_count,
2358 p_data => x_msg_data );
2359 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2360 -- ROLLBACK TO Transfer_Party_Rules;
2361 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2362 FND_MSG_PUB.Count_And_Get
2363 ( p_count => x_msg_count,
2364 p_data => x_msg_data );
2365 WHEN OTHERS THEN
2366 -- ROLLBACK TO Transfer_Party_Rules;
2367 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2368 IF FND_MSG_PUB.Check_Msg_Level
2369 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2370 THEN
2371 FND_MSG_PUB.Add_Exc_Msg
2372 ( g_pkg_name, l_api_name );
2373 END IF;
2374 FND_MSG_PUB.Count_And_Get
2375 ( p_count => x_msg_count,
2376 p_data => x_msg_data );
2377
2378 END Transfer_Party_Rules;
2379
2380
2381 /*-----------------------------------------------------------*/
2382 /* Procedure name: Is_Preferred_Contact_Pty */
2383 /* Description : Check if Preferred party exist for the */
2384 /* current party relationship */
2385 /*-----------------------------------------------------------*/
2386
2387 FUNCTION Is_Preferred_Contact_Pty
2388 ( p_Instance_id IN NUMBER,
2389 p_relationship_type IN VARCHAR2 ,
2390 p_start_date IN DATE ,
2391 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
2392 ) RETURN BOOLEAN IS
2393
2394 l_dummy VARCHAR(1);
2395 l_return_value BOOLEAN := TRUE;
2396
2397 CURSOR C1 IS
2398 SELECT 'x'
2399 FROM csi_i_parties
2400 WHERE instance_id = p_Instance_id
2401 -- AND relationship_type_code = p_relationship_type
2402 AND preferred_flag = 'Y'
2403 AND contact_flag = 'Y'
2404 AND ((active_end_date is null) OR (active_end_date >= sysdate))
2405 AND NVL(active_end_date, SYSDATE) >= DECODE(active_end_date, NULL, SYSDATE, NVL(p_start_date, FND_API.G_MISS_DATE));
2406 BEGIN
2407 OPEN C1;
2408 FETCH C1 INTO l_dummy;
2409 IF C1%FOUND THEN
2410 IF ( p_stack_err_msg = TRUE ) THEN
2411 FND_MESSAGE.SET_NAME('CSI','CSI_API_PREFERRED_PTY_EXISTS');
2412 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_instance_id);
2413 FND_MESSAGE.SET_TOKEN('RELATIONSHIP_TYPE',p_relationship_type);
2414 FND_MSG_PUB.Add;
2415 END IF;
2416 ELSE
2417 l_return_value := FALSE;
2418 END IF;
2419 CLOSE C1;
2420 RETURN l_return_value;
2421 EXCEPTION
2422 WHEN OTHERS THEN
2423 l_return_value := FALSE;
2424 RETURN l_return_value;
2425 END Is_Preferred_Contact_Pty;
2426
2427
2428 /*-----------------------------------------------------------*/
2429 /* Procedure name: Is_Primary_Contact_Pty */
2430 /* Description : Check if Primary party exist for the */
2431 /* current party relationship */
2432 /*-----------------------------------------------------------*/
2433
2434 FUNCTION Is_Primary_Contact_Pty
2435 ( p_Instance_id IN NUMBER,
2436 p_contact_ip_id IN NUMBER,
2437 p_relationship_type IN VARCHAR2 ,
2438 p_start_date IN DATE ,
2439 p_end_date IN DATE ,
2440 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
2441 ) RETURN BOOLEAN IS
2442
2443 l_dummy VARCHAR(1);
2444 l_return_value BOOLEAN := TRUE;
2445
2446 CURSOR C1 IS
2447 SELECT 'x'
2448 FROM csi_i_parties
2449 WHERE instance_id = p_Instance_id
2450 AND relationship_type_code = p_relationship_type
2451 AND primary_flag = 'Y'
2452 AND contact_flag = 'Y'
2453 AND contact_ip_id = p_contact_ip_id
2454 AND ((active_end_date is null) OR (active_end_date > p_start_date));
2455 -- AND active_start_date < p_end_date; --sk added
2456 -- AND ((active_end_date is null) OR (active_end_date >= sysdate))
2457 -- AND NVL(active_end_date, SYSDATE) >= DECODE(active_end_date, NULL, SYSDATE, NVL(p_start_date, FND_API.G_MISS_DATE));
2458 BEGIN
2459 OPEN C1;
2460 FETCH C1 INTO l_dummy;
2461 IF C1%FOUND THEN
2462 IF ( p_stack_err_msg = TRUE ) THEN
2463 FND_MESSAGE.SET_NAME('CSI','CSI_API_PRIMARY_PTY_EXISTS');
2464 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_instance_id);
2465 FND_MESSAGE.SET_TOKEN('RELATIONSHIP_TYPE',p_relationship_type);
2466 FND_MSG_PUB.Add;
2467 END IF;
2468 ELSE
2469 l_return_value := FALSE;
2470 END IF;
2471 CLOSE C1;
2472 RETURN l_return_value;
2473 EXCEPTION
2474 WHEN OTHERS THEN
2475 l_return_value := FALSE;
2476 RETURN l_return_value;
2477 END Is_Primary_Contact_Pty;
2478
2479 /*-----------------------------------------------------------*/
2480 /* Procedure name: Is_Preferred_Pty */
2481 /* Description : Check if Preferred party exist for the */
2482 /* current party relationship */
2483 /*-----------------------------------------------------------*/
2484
2485 FUNCTION Is_Preferred_Pty
2486 ( p_Instance_id IN NUMBER,
2487 p_relationship_type IN VARCHAR2 ,
2488 p_start_date IN DATE ,
2489 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
2490 ) RETURN BOOLEAN IS
2491
2492 l_dummy VARCHAR(1);
2493 l_return_value BOOLEAN := TRUE;
2494
2495 CURSOR C1 IS
2496 SELECT 'x'
2497 FROM csi_i_parties
2498 WHERE instance_id = p_Instance_id
2499 -- AND relationship_type_code = p_relationship_type
2500 AND preferred_flag = 'Y'
2501 AND contact_flag <> 'Y'
2502 AND ((active_end_date is null) OR (active_end_date >= sysdate))
2503 AND NVL(active_end_date, SYSDATE) >= DECODE(active_end_date, NULL, SYSDATE, NVL(p_start_date, FND_API.G_MISS_DATE));
2504
2505
2506 BEGIN
2507 OPEN C1;
2508 FETCH C1 INTO l_dummy;
2509 IF C1%FOUND THEN
2510 IF ( p_stack_err_msg = TRUE ) THEN
2511 FND_MESSAGE.SET_NAME('CSI','CSI_API_PREFERRED_PTY_EXISTS');
2512 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_instance_id);
2513 FND_MESSAGE.SET_TOKEN('RELATIONSHIP_TYPE',p_relationship_type);
2514 FND_MSG_PUB.Add;
2515 END IF;
2516 ELSE
2517 l_return_value := FALSE;
2518 END IF;
2519 CLOSE C1;
2520 RETURN l_return_value;
2521 EXCEPTION
2522 WHEN OTHERS THEN
2523 l_return_value := FALSE;
2524 RETURN l_return_value;
2525 END Is_Preferred_Pty;
2526
2527
2528 /*-----------------------------------------------------------*/
2529 /* Procedure name: Is_Primary_Pty */
2530 /* Description : Check if Primary party exist for the */
2531 /* current party relationship */
2532 /*-----------------------------------------------------------*/
2533
2534 FUNCTION Is_Primary_Pty
2535 ( p_Instance_id IN NUMBER,
2536 p_relationship_type IN VARCHAR2 ,
2537 p_start_date IN DATE ,
2538 p_end_date IN DATE ,
2539 p_stack_err_msg IN BOOLEAN DEFAULT TRUE
2540 ) RETURN BOOLEAN IS
2541
2542 l_dummy VARCHAR(1);
2543 l_return_value BOOLEAN := TRUE;
2544
2545 CURSOR C1 IS
2546 SELECT 'x'
2547 FROM csi_i_parties
2548 WHERE instance_id = p_Instance_id
2549 AND relationship_type_code = p_relationship_type
2550 AND primary_flag = 'Y'
2551 AND contact_flag <> 'Y'
2552 AND ((active_end_date IS NULL) OR (active_end_date > p_start_date));
2553 -- AND active_start_date < p_end_date ; sk commented this for bug 2077093.
2554 -- AND ((active_end_date is null) OR (active_end_date >= sysdate)) --sk commented
2555 -- AND NVL(active_end_date, SYSDATE) >= DECODE(active_end_date, NULL, SYSDATE, NVL(p_start_date, FND_API.G_MISS_DATE));
2556 BEGIN
2557 OPEN C1;
2558 FETCH C1 INTO l_dummy;
2559 IF C1%FOUND THEN
2560 IF ( p_stack_err_msg = TRUE ) THEN
2561 FND_MESSAGE.SET_NAME('CSI','CSI_API_PRIMARY_PTY_EXISTS');
2562 FND_MESSAGE.SET_TOKEN('INSTANCE_ID',p_instance_id);
2563 FND_MESSAGE.SET_TOKEN('RELATIONSHIP_TYPE',p_relationship_type);
2564 FND_MSG_PUB.Add;
2565 END IF;
2566 ELSE
2567 l_return_value := FALSE;
2568 END IF;
2569 CLOSE C1;
2570 RETURN l_return_value;
2571 EXCEPTION
2572 WHEN OTHERS THEN
2573 l_return_value := FALSE;
2574 RETURN l_return_value;
2575 END Is_Primary_Pty;
2576
2577
2578
2579
2580 END CSI_Instance_parties_vld_pvt;