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