1 PACKAGE BODY ar_deposit_lib_pvt AS
2 /* $Header: ARXDEPLB.pls 120.11.12000000.2 2007/04/11 11:06:32 spdixit ship $ */
3 /*=======================================================================+
4 | Package Global Constants
5 +=======================================================================*/
6
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AR_DEPOSIT_LIB_PVT';
8
9 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
10 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
11 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
12 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
13 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
14 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
15
16
17
18 /*=======================================================================+
19 | FUNCTION get_salesrep_name_id will find the id of
20 | salesrep from salesrep name or validate it
21 +=======================================================================*/
22 FUNCTION get_salesrep_name_id(p_default_salesrep_id in number,
23 p_deposit_date in Date) RETURN NUMBER IS
24 l_default_srep_name VARCHAR2(30);
25 l_default_srep_number VARCHAR2(30);
26 l_return number := 1;
27
28 BEGIN
29 arp_util.debug('ar_deposit_lib_pvt.get_salesrep_name_id() +' );
30 SELECT name,
31 salesrep_number
32 INTO l_default_srep_name,
33 l_default_srep_number
34 FROM RA_SALESREPS
35 WHERE SALESREP_ID = p_default_salesrep_id
36 AND NVL(status,'A') ='A'
37 AND p_deposit_date between nvl(start_date_active, p_deposit_date) and
38 nvl(end_date_active, p_deposit_date);
39
40 return(p_default_salesrep_id);
41 arp_util.debug('ar_deposit_lib_pvt.get_salesrep_name_id() -' );
42 EXCEPTION
43 WHEN NO_DATA_FOUND THEN
44
45 l_return:= -1;
46 arp_util.debug('EXCEPTION :ar_deposit_lib_pvt.
47 get_salesrep_name_id(NO_DATA_FOUND)' );
48 return(l_return);
49 WHEN OTHERS THEN
50 l_return:= -1;
51 arp_util.debug('EXCEPTION :
52 ar_deposit_lib_pvt.get_salesrep_name_id(OTHERS)' );
53 return(l_return);
54
55 END;
56
57
58 /*=======================================================================+
59 | PROCEDURE default_bill_to_contact will find the default bill to contact
60 | for bill to customer
61 +=======================================================================*/
62
63 PROCEDURE default_bill_to_contact(p_contact_id OUT NOCOPY NUMBER,
64 p_bill_to_customer_id IN NUMBER,
65 p_bill_to_site_use_id IN NUMBER)
66 IS
67 l_dft_contact_id NUMBER;
68 l_contact_id NUMBER;
69 l_found boolean;
70 l_more_than_one boolean;
71
72 cursor c_customer_contact is
73 select distinct acct_role.cust_account_role_id contact_id
74 from hz_cust_account_roles acct_role,
75 hz_parties party,
76 hz_relationships rel,
77 hz_org_contacts org_cont,
78 hz_parties rel_party
79 where acct_role.party_id = rel.party_id
80 and acct_role.role_type = 'CONTACT'
81 and org_cont.party_relationship_id = rel.relationship_id
82 and rel.subject_id = party.party_id
83 and rel.party_id = rel_party.party_id
84 and rel.subject_table_name = 'HZ_PARTIES'
85 and rel.object_table_name = 'HZ_PARTIES'
86 and rel.directional_flag = 'F'
87 and acct_role.cust_account_id = p_bill_to_customer_id
88 and acct_role.cust_acct_site_id is null
89 /* the contact must be active. however, for credit memos
90 against specific transactions, the credited transaction's
91 contact may also be used even if it is not active. */
92 AND ( acct_role.cust_account_role_id = NULL
93 OR ( acct_role.status = 'A'))
94 ORDER BY 1;
95
96 cursor c_address_contact is
97 select distinct acct_role.cust_account_role_id contact_id
98 from hz_cust_account_roles acct_role,
99 hz_parties party,
100 hz_relationships rel,
101 hz_org_contacts org_cont,
102 hz_parties rel_party,
103 hz_cust_acct_sites acct_site,
104 hz_cust_site_uses site_uses
105 where acct_role.party_id = rel.party_id
106 and acct_role.role_type = 'CONTACT'
107 and org_cont.party_relationship_id = rel.relationship_id
108 and rel.subject_id = party.party_id
109 and rel.party_id = rel_party.party_id
110 and rel.subject_table_name = 'HZ_PARTIES'
111 and rel.object_table_name = 'HZ_PARTIES'
112 and rel.directional_flag = 'F'
113 and acct_role.cust_account_id = p_bill_to_customer_id
114 and site_uses.site_use_id = p_bill_to_site_use_id
115 and acct_site.cust_account_id = acct_role.cust_account_id
116 /* show customer level as well as address level contacts */
117 and acct_role.cust_acct_site_id = site_uses.cust_acct_site_id
118 and acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
119 and site_uses.site_use_code = 'BILL_TO'
120
121 /* the contact must be active. however, for credit memos
122 against specific transactions, the credited transaction's
123 contact may also be used even if it is not active. */
124 AND ( acct_role.cust_account_role_id = NULL
125 OR ( acct_role.status = 'A') )
126 ORDER BY 1;
127
128
129 BEGIN
130 arp_util.debug('ar_deposit_lib_pvt.default_bill_to_contact() +' );
131 /*
132 1) If the customer defines one contact at customer level and the contact
133 is active and not define a contact at address level, the contact at
134 customer level will default.
135 2) If the customer defines one contact at address level, the contact is
136 active and the address level Primary check box in Business Purposes tab
137 is checked and not define a contact at customer level, the contact at
138 address level will default.
139 3) If the customer define one contact at both customer and address level
140 and:
141 3.1) Contact is active just at customer level: contact at customer
142 level will default.
143 3.2) Contact is active just at address level and the address level
144 Primary check box in Business Purposes tab is checked:
145 contact at address level will default.
146 3.3) Both contacts are active and the address level Primary check
147 box in Business Purposes tab is checked: contact at address
148 level will default.
149 4) If the customer defines more than one contact at address level and
150 just one contact at customer level, and the contact at customer level
151 is active, the contact at customer level will default.
152 5) If the customer defines more than one contact at customer level and
153 just one contact at address level, and the contact at addrees level
154 is active, and the address level Primary check box in Business
155 Purposes tab is checked the contact at address level will default.
156 6) If the customer defines more than one contact at customer level
157 and more than one contact at address level, all of them are actives
158 and the address level Primary check box in Business Purposes tab
159 is checked, none contact will default and the customer must
160 choose the contact from the LOV.
161 */
162
163 IF p_bill_to_site_use_id is not null
164 THEN
165 OPEN c_address_contact;
166 FETCH c_address_contact INTO l_contact_id;
167 l_found := c_address_contact%FOUND;
168 IF l_found then
169 fetch c_address_contact into l_contact_id;
170 l_more_than_one := c_address_contact%FOUND;
171 END IF;
172 CLOSE c_address_contact;
173
174 IF l_found and not l_more_than_one THEN
175
176 l_dft_contact_id := l_contact_id;
177
178 ELSE
179 OPEN c_customer_contact;
180 FETCH c_customer_contact into l_contact_id;
181 l_found := c_customer_contact%FOUND;
182 IF l_found then
183 fetch c_customer_contact into l_contact_id;
184 l_more_than_one := c_customer_contact%FOUND;
185 END IF;
186 CLOSE c_customer_contact;
187 IF l_found and not l_more_than_one THEN
188 l_dft_contact_id := l_contact_id;
189 ELSE
190 l_dft_contact_id := null;
191 END IF;
192 END IF;
193
194 ELSE
195 OPEN c_customer_contact;
196 FETCH c_customer_contact into l_contact_id;
197 l_found := c_customer_contact%FOUND;
198 IF l_found then
199 fetch c_customer_contact into l_contact_id;
200 l_more_than_one := c_customer_contact%FOUND;
201 END IF;
202 CLOSE c_customer_contact;
203 IF l_found and not l_more_than_one THEN
204 l_dft_contact_id := l_contact_id;
205 ELSE
206 l_dft_contact_id := null;
207 END IF;
208 END IF;
209 p_contact_id :=l_dft_contact_id;
210
211 arp_util.debug('ar_deposit_lib_pvt.default_bill_to_contact() -' );
212 END;
213 /*=======================================================================+
214 | PROCEDURE get_salesrep_defaults will find the defaulted salesrep
215 +=======================================================================*/
216
217
218 PROCEDURE get_salesrep_defaults( p_salesrep_id OUT NOCOPY NUMBER,
219 p_bill_to_customer_id IN NUMBER,
220 p_bill_to_site_use_id IN NUMBER,
221 p_ship_to_customer_id IN NUMBER,
222 p_ship_to_site_use_id IN NUMBER,
223 p_deposit_date Date) IS
224
225 l_default_srep_id_1 NUMBER;
226 l_default_srep_id_2 NUMBER;
227 l_default_srep_id_3 NUMBER;
228 l_default_srep_id_4 NUMBER;
229 l_return NUMBER := 1;
230 l_default_srep_name VARCHAR2(30);
231 l_default_srep_num INTEGER;
232 l_org_id NUMBER;
233 l_salesreprequiredflag VARCHAR2(1);
234
235 BEGIN
236
237 arp_util.debug('ar_deposit_lib_pvt.get_salesrep_defaults() +' );
238
239 p_salesrep_id :=null;
240 begin
241 SELECT SALESREP_REQUIRED_FLAG,ORG_ID
242 into l_salesreprequiredflag,l_org_id
243 FROM AR_SYSTEM_PARAMETERS;
244
245 exception
246 when no_data_found then
247 arp_util.debug(' profile not defined()- ');
248 when others then
249 arp_util.debug(' profile others : exception ');
250 end;
251 /* **********************************************************************
252 Procedure:
253 Salesrep Information defaulting.
254 Description:
255 This block is executed after bill to location and ship to location
256 are defaulted after selecting the customer.
257
258 ************************************************************************ */
259 /*-----------------------------------------------------+
260 | Default the Primary Salesrep : Hierarchy |
261 | |
262 | -- From the Bill to Site Value |
263 | -- From the Ship to Site Value |
264 | -- From Customer defaults (if Not Multi-Org) |
265 | -- To 'No Sales Credits' if Required_Flag='Y' |
266 |_____________________________________________________*/
267
268 /* Bill to */
269
270 begin
271 select su.primary_salesrep_id
272 into l_default_srep_id_1
273 from hz_cust_acct_sites acct_site,
274 hz_party_sites party_site,
275 hz_locations loc,
276 hz_cust_site_uses su,
277 fnd_territories_vl t
278 where acct_site.cust_acct_site_id = su.cust_acct_site_id
279 and acct_site.party_site_id = party_site.party_site_id
280 and loc.location_id = party_site.location_id
281 and loc.country = t.territory_code
282 and acct_site.cust_account_id = p_bill_to_customer_id
283 and su.site_use_id = nvl(p_bill_to_site_use_id, su.site_use_id)
284 and su.site_use_code = 'BILL_TO'
285 and ( su.site_use_id = null
286 or ( su.status = 'A'
287 and acct_site.status = 'A'
288 )
289 )
290 and su.primary_flag = 'Y';
291 l_return := get_salesrep_name_id(l_default_srep_id_1,p_deposit_date);
292
293 IF (l_return <> -1 ) THEN
294 p_salesrep_id :=l_default_srep_id_1;
295 END IF;
296 arp_util.debug('l_default_srep_id_1 '||to_char(l_default_srep_id_1));
297 return;
298 exception
299 when no_data_found then
300 arp_util.debug('no data : l_default_srep_id_1 ');
301 l_default_srep_id_1:=null;
302 when others then
303 arp_util.debug('others : l_default_srep_id_1 ');
304 l_default_srep_id_1:=null;
305
306 end;
307
308 IF l_default_srep_id_1 is null THEN
309 begin
310 select asa.primary_salesrep_id
311 /* selecting salesrep_id for Rel 11 */
312 into l_default_srep_id_2
313 from
314 (
315 SELECT
316 A.CUST_ACCOUNT_ID CUSTOMER_ID ,
317 A.STATUS A_STATUS ,
318 SU.PRIMARY_FLAG PRIMARY_FLAG ,
319 SU.STATUS SU_STATUS ,
320 SU.SITE_USE_ID SITE_USE_ID ,
321 SU.PRIMARY_SALESREP_ID
322 FROM
323 HZ_CUST_ACCT_SITES A,
324 HZ_CUST_SITE_USES SU
325 WHERE
326 A.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
327 AND SU.SITE_USE_CODE = 'SHIP_TO'
328 ) asa
329 where asa.customer_id = p_ship_to_customer_id
330 and ( asa.site_use_id = p_ship_to_site_use_id
331 or ( asa.su_status = 'A'
332 and asa.a_status = 'A'
333 )
334 )
335 and asa.primary_flag='Y';
336
337
338 l_return := get_salesrep_name_id(l_default_srep_id_2,p_deposit_date);
339
340 IF (l_return <> -1 ) THEN
341 p_salesrep_id :=l_default_srep_id_2;
342 END IF;
343
344 arp_util.debug('l_default_srep_id_2 '||to_char(l_default_srep_id_2));
345 return;
346
347 exception
348 when no_data_found then
349 l_default_srep_id_2:=null;
350
351 end;
352 END IF;
353
354 IF l_default_srep_id_2 is null THEN
355
356 /* Customer Level - IF no Multi Org */
357 IF l_org_id is NULL
358 THEN
359 l_default_srep_id_3 := -3;
360
361 l_return :=
362 get_salesrep_name_id(l_default_srep_id_3,p_deposit_date);
363
364 IF (l_return = -1 )
365 THEN
366
367 /* To 'No Sales Credits' if Required_Flag='Y' */
368 -- ar_system_parameters.salesrep_required_flag%type;
369 IF ( l_salesreprequiredflag ='Y')
370 THEN
371 l_default_srep_id_3 := -3;
372
373 ELSE
374 l_default_srep_id_3 :=null;
375 END IF; /* To 'No Sales Credits' if Required_Flag='Y' */
376
377 END IF;
378
379 ELSE
380 /* Customer Level (Not Req.- IF Multi Org - go to Required Flag */
381 IF ( l_salesreprequiredflag ='Y')
382 THEN
383 l_default_srep_id_3 := -3;
384
385 ELSE
386 l_default_srep_id_3 :=null;
387 END IF; /* To 'No Sales Credits' if Required_Flag='Y' */
388 END IF;
389 p_salesrep_id :=l_default_srep_id_3;
390 return;
391 END IF;
392
393
394 arp_util.debug('ar_deposit_lib_pvt.get_salesrep_defaults() - ' );
395 END get_salesrep_defaults;
396
397 /*=======================================================================+
398 | Default_commitment_Date will find the default commitment date
399 +=======================================================================*/
400 PROCEDURE Default_commitment_Date(p_deposit_date IN DATE,
401 p_start_date_commitment IN OUT NOCOPY DATE,
402 p_end_date_commitment IN OUT NOCOPY DATE,
403 p_return_status OUT NOCOPY VARCHAR2) IS
404 BEGIN
405 arp_util.debug('ar_deposit_lib_pvt.Default_commitment_Date() +' );
406 p_return_status := FND_API.G_RET_STS_SUCCESS;
407
408 IF p_start_date_commitment IS NULL THEN
409 p_start_date_commitment:=p_deposit_date;
410 END IF;
411
412
413 IF p_start_date_commitment > nvl(p_end_date_commitment,
414 p_start_date_commitment) THEN
415 FND_MESSAGE.set_name( 'AR', 'AR_TW_BAD_COMMITMT_DATE_RANGE' );
416 FND_MESSAGE.set_token( 'START_DATE', TO_CHAR(p_start_date_commitment));
417 FND_MESSAGE.set_token( 'END_DATE', TO_CHAR(p_end_date_commitment));
418 FND_MSG_PUB.Add;
419 p_return_status := FND_API.G_RET_STS_ERROR;
420 END IF;
421 IF p_end_date_commitment is not null and
422 p_deposit_date > p_end_date_commitment THEN
423
424 FND_MESSAGE.set_name( 'AR', 'AR_TW_COMMIT_END_TRX_DATE' );
425 FND_MESSAGE.set_token( 'TRX_DATE', TO_CHAR(p_deposit_date));
426 FND_MESSAGE.set_token( 'END_DATE', TO_CHAR(p_end_date_commitment));
427 FND_MSG_PUB.Add;
428 p_return_status := FND_API.G_RET_STS_ERROR;
429
430 END IF;
431
432 /* IF (p_deposit_date < p_start_date_commitment or
433 p_deposit_date > nvl(p_end_date_commitment,
434 p_start_date_commitment)) THEN
435 FND_MESSAGE.set_name( 'AR', 'AR_TW_BAD_DATE_COMMITMENT' );
436 FND_MSG_PUB.Add;
437 p_return_status := FND_API.G_RET_STS_ERROR;
438 END IF;*/
439
440 arp_util.debug('ar_deposit_lib_pvt.Default_commitment_Date() -' );
441 END Default_commitment_Date;
442
443 /*=======================================================================+
444 | FUNCTION Get_Territory_id will find the default Territory_id
445 +=======================================================================*/
446
447 FUNCTION Get_Territory_id(p_bill_to_customer_id IN NUMBER,
448 p_bill_to_location IN VARCHAR2,
449 p_ship_to_customer_id IN NUMBER,
450 p_ship_to_location IN VARCHAR2,
451 p_salesrep_id IN NUMBER,
452 p_deposit_date IN Date ,
453 p_return_status OUT NOCOPY VARCHAR2
454 ) RETURN NUMBER IS
455 l_territory_default VARCHAR2(50);
456 l_territory_id NUMBER;
457 cursor c_salesrep_territory IS
458 SELECT st.territory_id territory_id
459 FROM ra_salesrep_territories st
460 WHERE st.salesrep_id = p_salesrep_id
461 AND 'A' = NVL(st.status(+), 'A')
462 AND p_deposit_date BETWEEN NVL(st.start_date_active(+), p_deposit_date )
463 AND NVL(st.end_date_active(+), p_deposit_date );
464 BEGIN
465
466 arp_util.debug('ar_deposit_lib_pvt.Get_Territory_id() +' );
467 p_return_status := FND_API.G_RET_STS_SUCCESS;
468 begin
469 select DEFAULT_TERRITORY
470 into l_territory_default
471 from ar_system_parameters;
472 exception
473 when no_data_found then
474 arp_util.debug('System option not defined for the org');
475 when others then
476 arp_util.debug('EXCEPTIONS: Others system option selection');
477 end;
478
479 IF ( l_territory_default = 'BILL' ) THEN
480 declare
481 l_Site_Use_Id number := Get_Site_Use_Id(p_bill_to_customer_id,
482 p_bill_to_location,
483 'BILL_TO',NULL,p_return_status);
484 begin
485 select territory_id
486 into l_territory_id
487 from hz_cust_site_uses_all
488 where site_use_id = l_Site_Use_Id ;
489
490 exception
491 when no_data_found then
492 l_territory_id := null;
493 when others then
494 arp_util.debug('EXCEPTIONS: Others in territory_id TO SITE,');
495 end;
496
497 ELSIF ( l_territory_default = 'SHIP' ) THEN
498 declare
499 l_Site_Use_Id number := Get_Site_Use_Id(p_ship_to_customer_id,
500 p_ship_to_location,
501 'SHIP_TO',NULL,p_return_status);
502 begin
503 select territory_id
504 into l_territory_id
505 from hz_cust_site_uses_all
506 where site_use_id =l_Site_Use_Id ;
507
508 exception
509 when no_data_found then
510 l_territory_id := null;
511 when others then
512 arp_util.debug('EXCEPTIONS: Others in territory_id TO SITE,');
513 end;
514
515 ELSIF ( l_territory_default = 'SALES' ) THEN
516
517 IF (l_territory_id IS NULL) THEN
518
519 OPEN c_salesrep_territory;
520 LOOP
521 FETCH c_salesrep_territory into l_territory_id;
522 EXIT when c_salesrep_territory%NOTFOUND;
523 END LOOP;
524 IF (c_salesrep_territory%ROWCOUNT > 1) THEN
525 l_territory_id :=null;
526 ELSE
527 arp_util.debug('Copied Territory_id from Salesperson') ;
528
529 END IF;
530 close c_salesrep_territory;
531
532 END IF;
533
534
535 END IF;
536 RETURN(l_territory_id);
537 arp_util.debug('ar_deposit_lib_pvt.Get_Territory_id() -' );
538 END;
539
540 /*=======================================================================+
541 | FUNCTION Get_Ship_Via will get Ship_Via
542 +=======================================================================*/
543
544 FUNCTION Get_Ship_Via( p_bill_to_customer_id IN NUMBER,
545 p_bill_to_location IN VARCHAR2,
546 p_ship_to_customer_id IN NUMBER,
547 p_ship_to_location IN VARCHAR2,
548 p_return_status OUT NOCOPY VARCHAR2
549 )
550 RETURN VARCHAR2 IS
551
552 l_Ship_Via_ship_default VARCHAR2(100):=NULL;
553 l_Ship_Via_bill_default VARCHAR2(100):=NULL;
554 l_Ship_Via_ship_site_default VARCHAR2(100):=NULL;
555 l_Ship_Via_bill_site_default VARCHAR2(100):=NULL;
556
557 BEGIN
558 p_return_status := FND_API.G_RET_STS_SUCCESS;
559 arp_util.debug('ar_deposit_lib_pvt.Get_Ship_Via() +' );
560
561 IF p_ship_to_customer_id IS NOT NULL AND
562 p_ship_to_location IS NOT NULL
563 THEN
564 declare
565 l_Site_Use_Id number := Get_Site_Use_Id(p_ship_to_customer_id,
566 p_ship_to_location,
567 'SHIP_TO',NULL, p_return_status);
568 begin
569 select ship_via
570 into l_ship_via_ship_site_default
571 from hz_cust_site_uses_all
572 where site_use_id =l_Site_Use_Id ;
573
574 exception
575 when no_data_found then
576 l_ship_via_ship_site_default := null;
577 when others then
578 l_ship_via_ship_site_default := null;
579 arp_util.debug('EXCEPTIONS: Others in
580 SHIP TO SITE, Get_ship_via_POINT()');
581 end;
582
583 END IF;
584
585 IF l_ship_via_ship_site_default IS NULL THEN
586 begin
587 select ship_via into l_ship_via_ship_default
588 from hz_cust_accounts
589 where cust_account_id = p_ship_to_customer_id;
590 exception
591 when no_data_found then
592 l_ship_via_ship_default := null;
593 when others then
594 l_ship_via_ship_default := null;
595 arp_util.debug('EXCEPTIONS: Others in
596 SHIP TO Get_ship_via_POINT()');
597 end;
598 ELSE
599 RETURN(l_ship_via_ship_site_default);
600 END IF;
601
602 IF l_ship_via_ship_default IS NULL THEN
603 IF p_bill_to_customer_id IS NOT NULL AND
604 p_bill_to_location IS NOT NULL
605 THEN
606 declare
607 l_Site_Use_Id number := Get_Site_Use_Id(p_bill_to_customer_id,
608 p_bill_to_location,
609 'BILL_TO',NULL,p_return_status);
610 begin
611 select ship_via
612 into l_ship_via_bill_site_default
613 from hz_cust_site_uses
614 where site_use_id = l_Site_Use_Id;
615
616 exception
617 when no_data_found then
618 l_ship_via_bill_site_default := null;
619 when others then
620 l_ship_via_bill_site_default := null;
621 arp_util.debug('EXCEPTIONS: Others in
622 BILL_TO site, Get_ship_via_POINT()');
623 end;
624
625 END IF;
626
627 IF l_ship_via_bill_site_default IS NULL THEN
628 begin
629 select ship_via
630 into l_ship_via_bill_default
631 from hz_cust_accounts
632 where cust_account_id = p_bill_to_customer_id;
633
634 RETURN(l_ship_via_bill_default);
635 exception
636 when no_data_found then
637 l_ship_via_bill_default := null;
638 when others then
639 l_ship_via_bill_default := null;
640 arp_util.debug('EXCEPTIONS: Others in
641 BILL_TO , Get_ship_via_POINT()');
642 end;
643 ELSE
644 RETURN(l_ship_via_bill_site_default);
645 END IF;
646 ELSE
647 RETURN(l_ship_via_ship_default);
648 END IF;
649 --if nothing is retreived
650 RETURN(l_ship_via_bill_default);
651
652 arp_util.debug('ar_deposit_lib_pvt.Get_Ship_Via() -' );
653
654 END Get_Ship_Via;
655
656 /*=======================================================================+
657 | FUNCTION Get_FOB_POINT will get FOB_POINT
658 +=======================================================================*/
659
660 FUNCTION Get_FOB_POINT( p_bill_to_customer_id IN NUMBER,
661 p_bill_to_location IN VARCHAR2,
662 p_ship_to_customer_id IN NUMBER,
663 p_ship_to_location IN VARCHAR2,
664 p_return_status OUT NOCOPY VARCHAR2
665 )
666 RETURN VARCHAR2 IS
667
668 l_fob_ship_default VARCHAR2(100):=NULL;
669 l_fob_bill_default VARCHAR2(100):=NULL;
670 l_fob_ship_site_default VARCHAR2(100):=NULL;
671 l_fob_bill_site_default VARCHAR2(100):=NULL;
672
673 BEGIN
674
675 arp_util.debug('ar_deposit_lib_pvt.Get_FOB_POINT() +' );
676 p_return_status := FND_API.G_RET_STS_SUCCESS;
677
678 IF p_ship_to_customer_id IS NOT NULL AND
679 p_ship_to_location IS NOT NULL
680 THEN
681 arp_util.debug('p_ship_to_customer_id IS NOT NULL
682 AND p_ship_to_location IS NOT NULL');
683 declare
684 l_Site_Use_Id number := Get_Site_Use_Id(p_ship_to_customer_id,
685 p_ship_to_location,
686 'SHIP_TO',NULL,p_return_status);
687 begin
688 select fob_point
689 into l_fob_ship_site_default
690 from hz_cust_site_uses
691 where site_use_id =l_Site_Use_Id ;
692
693 exception
694 when no_data_found then
695 arp_util.debug('no_data');
696 l_fob_ship_site_default := null;
697 when others then
698 l_fob_ship_site_default := null;
699 arp_util.debug('EXCEPTIONS: Others in
700 SHIP TO SITE, Get_FOB_POINT()');
701 end;
702
703 END IF;
704
705 IF l_fob_ship_site_default IS NULL THEN
706 begin
707 select fob_point into l_fob_ship_default
708 from hz_cust_accounts
709 where cust_account_id = p_ship_to_customer_id;
710 exception
711 when no_data_found then
712 l_fob_ship_default := null;
713 when others then
714 l_fob_ship_default := null;
715 arp_util.debug('EXCEPTIONS: Others in SHIP TO Get_FOB_POINT()');
716 end;
717 ELSE
718 RETURN(l_fob_ship_site_default);
719 END IF;
720
721 IF l_fob_ship_default IS NULL THEN
722 IF p_bill_to_customer_id IS NOT NULL AND
723 p_bill_to_location IS NOT NULL
724 THEN
725 declare
726 l_Site_Use_Id number := Get_Site_Use_Id(p_ship_to_customer_id,
727 p_ship_to_location,
728 'SHIP_TO',NULL,p_return_status);
729 begin
730 select fob_point
731 into l_fob_bill_site_default
732 from hz_cust_site_uses
733 where site_use_id = l_Site_Use_Id;
734
735 exception
736 when no_data_found then
737 l_fob_bill_site_default := null;
738 when others then
739 l_fob_bill_site_default := null;
740 arp_util.debug('EXCEPTIONS: Others in
741 BILL_TO site, Get_FOB_POINT()');
742 end;
743
744 END IF;
745
746 IF l_fob_bill_site_default IS NULL THEN
747 begin
748 select fob_point
749 into l_fob_bill_default
750 from hz_cust_accounts
751 where cust_account_id = p_bill_to_customer_id;
752
753 RETURN(l_fob_bill_default);
754 exception
755 when no_data_found then
756 l_fob_bill_default := null;
757 when others then
758 l_fob_bill_default := null;
759 arp_util.debug('EXCEPTIONS: Others in
760 BILL_TO , Get_FOB_POINT()');
761 end;
762 ELSE
763 RETURN(l_fob_bill_site_default);
764 END IF;
765 ELSE
766 RETURN(l_fob_ship_default);
767 END IF;
768 --if nothing is in then
769 RETURN(l_fob_bill_default);
770
771
772 arp_util.debug('ar_deposit_lib_pvt.Get_FOB_POINT() -' );
773
774 END Get_FOB_POINT;
775
776 /*=======================================================================+
777 | FUNCTION GET_CONTACT_ID will get CONTACT_ID
778 +=======================================================================*/
779
780 FUNCTION GET_CONTACT_ID( p_customer_id IN NUMBER,
781 p_person_first_name IN VARCHAR2,
782 p_person_last_name IN VARCHAR2,
783 p_return_status OUT NOCOPY VARCHAR2
784 )
785 RETURN VARCHAR2 IS
786 l_selected_id NUMBER;
787 BEGIN
788 arp_util.debug('ar_deposit_lib_pvt.GET_CONTACT_ID() +' );
789 begin
790 SELECT acct_role.cust_account_role_id
791 INTO l_selected_id
792 from hz_cust_account_roles acct_role,
793 hz_parties party,
794 hz_relationships rel,
795 hz_org_contacts org_cont,
796 hz_parties rel_party
797 where acct_role.party_id = rel.party_id
798 and acct_role.role_type = 'CONTACT'
799 and org_cont.party_relationship_id =
800 rel.relationship_id
801 and rel.subject_id = party.party_id
802 and rel.party_id = rel_party.party_id
803 and rel.subject_table_name = 'HZ_PARTIES'
804 and rel.object_table_name = 'HZ_PARTIES'
805 and rel.directional_flag = 'F'
806 and acct_role.cust_account_id = p_customer_id
807 /* the contact must be active. however, for credit memos
808 against specific transactions, the credited transaction's
809 contact may also be used even if it is not active. */
810 AND acct_role.status = 'A'
811 AND party.person_last_name = p_person_last_name
812 AND party.person_first_name = p_person_first_name;
813
814 exception
815 when no_data_found then
816 l_selected_id := null;
817 arp_util.debug('EXCEPTIONS:no data found , GET_CONTACT_ID()');
818 --that the customer site use id could not be defaulted.
819 p_return_status := FND_API.G_RET_STS_ERROR;
820 FND_MESSAGE.SET_NAME('AR','AR_DAPI_CUS_CONTACT_INVALID');
821 FND_MSG_PUB.Add;
822
823 when others then
824 l_selected_id := null;
825 arp_util.debug('EXCEPTIONS: Others , GET_CONTACT_ID()');
826 end;
827
828 return(l_selected_id);
829 arp_util.debug('ar_deposit_lib_pvt.GET_CONTACT_ID() -' );
830 END GET_CONTACT_ID;
831
832 /*=======================================================================+
833 | FUNCTION GET_ID will get GET_ID
834 +=======================================================================*/
835
836 FUNCTION Get_Id(
837 p_entity IN VARCHAR2,
838 p_value IN VARCHAR2,
839 p_return_status OUT NOCOPY VARCHAR2
840 ) RETURN VARCHAR2 IS
841
842 l_cached_id VARCHAR2(100);
843 l_selected_id VARCHAR2(100);
844 l_index BINARY_INTEGER;
845
846 BEGIN
847
848 arp_util.debug('Get_Id()+ ');
849 l_selected_id := null;
850
851 IF ( p_entity = 'CUSTOMER_NUMBER' )
852 THEN
853
854
855 SELECT c.cust_account_id
856 INTO l_selected_id
857 FROM hz_cust_accounts c,
858 hz_customer_profiles cp,
859 hz_parties party
860 WHERE c.cust_account_id = cp.cust_account_id (+) and
861 cp.site_use_id is null and
862 c.account_number = p_value and
863 c.status <> 'I'
864 AND c.party_id = party.party_id;
865
866 ELSIF ( p_entity = 'CUSTOMER_NAME' )
867 THEN
868
869 SELECT cust_acct.cust_account_id
870 INTO l_selected_id
871 FROM hz_cust_accounts cust_acct,
872 hz_customer_profiles cp,
873 hz_parties party
874 WHERE cust_acct.cust_account_id = cp.cust_account_id (+)
875 and cust_acct.party_id = party.party_id(+)
876 and cp.site_use_id is null
877 and cust_acct.status <> 'I'
878 and party.party_name = p_value;
879 ELSIF ( p_entity = 'SALESREP_NAME' )
880 THEN
881
882 SELECT salesrep_id
883 INTO l_selected_id
884 FROM ra_salesreps
885 WHERE name = p_value;
886
887 ELSIF ( p_entity = 'BATCH_SOURCE_NAME' )
888 THEN
889 SELECT batch_source_id
890 INTO l_selected_id
891 FROM ra_batch_sources
892 WHERE name = p_value and
893 nvl(status, 'A') = 'A' and (batch_source_type = 'INV');
894 ELSIF ( p_entity = 'TERM_NAME' )
895 THEN
896 SELECT term_id
897 INTO l_selected_id
898 FROM ra_terms
899 WHERE name = p_value;
900
901 ELSIF (p_entity = 'RECEIPT_METHOD_NAME' )
902
903 THEN
904
905 SELECT receipt_method_id
906 INTO l_selected_id
907 FROM ar_receipt_methods
908 WHERE name = p_value;
909 /* REMOVED the defaulting of the bank_account_id from CUSTOMER_BANK_ACCOUNT_NUMBER, CUSTOMER_BANK_ACCOUNT_NAME */
910
911 /* removed ap_bank_accounts to point to ce_bank_accounts */
912 ELSIF (p_entity = 'REMIT_BANK_ACCOUNT_NUMBER')
913 THEN
914 SELECT bank_account_id
915 INTO l_selected_id
916 FROM ce_bank_accounts
917 WHERE bank_account_num = p_value
918 AND pg_deposit_date < NVL(end_date,
919 TO_DATE('01/01/2200',
920 'DD/MM/YYYY') );
921
922 ELSIF (p_entity = 'REMIT_BANK_ACCOUNT_NAME')
923 THEN
924 SELECT bank_account_id
925 INTO l_selected_id
926 FROM ce_bank_accounts
927 WHERE bank_account_name = p_value
928 AND pg_deposit_date < NVL(end_date,
929 TO_DATE('01/01/2200',
930 'DD/MM/YYYY') );
931
932 ELSIF (p_entity = 'CURRENCY_NAME')
933 THEN
934 SELECT currency_code
935 INTO l_selected_id
936 FROM fnd_currencies_vl
937 WHERE name = p_value;
938
939 ELSIF (p_entity = 'EXCHANGE_RATE_TYPE_NAME')
940 THEN
941 SELECT conversion_type
942 INTO l_selected_id
943 FROM gl_daily_conversion_types
944 WHERE user_conversion_type = p_value ;
945
946 END IF;
947
948 RETURN( l_selected_id );
949 arp_util.debug('Get_Id()- ');
950 EXCEPTION
951
952 WHEN NO_DATA_FOUND THEN
953 arp_util.debug('Value not found. Entity: ' ||
954 p_entity ||' Value: ' || p_value);
955 return(null);
956 arp_util.debug('Get_Id()- ');
957
958 WHEN OTHERS THEN
959 arp_util.debug('Value not found. Entity: ' ||
960 p_entity ||' Value: ' || p_value);
961 RAISE;
962
963 END Get_Id;
964
965 /*=======================================================================+
966 | FUNCTION Get_Site_Use_Id will get Site_Use_Id of customer location
967 +=======================================================================*/
968
969 FUNCTION Get_Site_Use_Id(
970 p_customer_id IN hz_cust_acct_sites.cust_account_id%TYPE,
971 p_location IN hz_cust_site_uses.location%TYPE,
972 p_site_use_code1 IN hz_cust_site_uses.site_use_code%TYPE DEFAULT NULL,
973 p_site_use_code2 IN hz_cust_site_uses.site_use_code%TYPE DEFAULT NULL,
974 p_return_status OUT NOCOPY VARCHAR2)
975 RETURN hz_cust_site_uses.site_use_id%type IS
976
977 l_cached_id hz_cust_site_uses.site_use_id%type;
978 l_selected_id hz_cust_site_uses.site_use_id%type;
979 l_index BINARY_INTEGER;
980 BEGIN
981
982 arp_util.debug('ar_deposit_lib_pvt.Get_Site_Use_Id() +' );
983 p_return_status := FND_API.G_RET_STS_SUCCESS;
984 IF p_customer_id IS NOT NULL THEN
985 IF (p_location IS NOT NULL) THEN
986 BEGIN
987
988 SELECT site_use.site_use_id
989 INTO l_selected_id
990 FROM hz_cust_site_uses site_use,
991 hz_cust_acct_sites acct_site
992 WHERE acct_site.cust_account_id = p_customer_id
993 AND acct_site.status = 'A'
994 AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id
995 AND (site_use.site_use_code = nvl(p_site_use_code1,
996 site_use.site_use_code) OR
997 site_use.site_use_code = nvl(p_site_use_code1,
998 site_use.site_use_code))
999 AND site_use.status = 'A'
1000 AND site_use.location = p_location;
1001 EXCEPTION
1002 WHEN no_data_found THEN
1003 arp_util.debug('No data found in the hz_cust_site_uses
1004 for the location :'||p_location);
1005 p_return_status := FND_API.G_RET_STS_ERROR;
1006 FND_MESSAGE.SET_NAME('AR','AR_DAPI_CUS_LOC_INVALID');
1007 FND_MSG_PUB.Add;
1008 --the error message will be raised in the validation routine.
1009
1010 END;
1011
1012 ELSE
1013 --the case when no location is specified for the customer.
1014 --here we are defaulting the primary bill_to loaction.
1015 BEGIN
1016
1017 SELECT site_use.site_use_id
1018 INTO l_selected_id
1019 FROM hz_cust_site_uses site_use,
1020 hz_cust_acct_sites acct_site
1021 WHERE acct_site.cust_account_id = p_customer_id
1022 AND acct_site.status = 'A'
1023 AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id
1024 AND (site_use.site_use_code = nvl(p_site_use_code1,
1025 site_use.site_use_code) OR
1026 site_use.site_use_code = nvl(p_site_use_code1,
1027 site_use.site_use_code))
1028 AND site_use.status = 'A'
1029 AND site_use.primary_flag = 'Y';
1030
1031 EXCEPTION
1032 WHEN no_data_found THEN
1033 arp_util.debug('No_data_found : Site use id could
1034 not be defaulted for customer_id '
1035 ||to_char(p_customer_id));
1036 --This is the case where customer site use id is null
1037 --neither it was supplied by the user nor it could be defaulted
1038 --a WARNING message raised in the validation routine to indicate
1039 --that the customer site use id could not be defaulted.
1040 p_return_status := FND_API.G_RET_STS_ERROR;
1041 FND_MESSAGE.SET_NAME('AR','AR_DAPI_CUS_SITE_DFT_INVALID');
1042 FND_MSG_PUB.Add;
1043 END;
1044
1045 END IF;
1046 END IF;
1047
1048 RETURN( l_selected_id );
1049 arp_util.debug('ar_deposit_lib_pvt.Get_Site_Use_Id() -' );
1050 EXCEPTION
1051 WHEN others THEN
1052 arp_util.debug('EXCEPTION: Get_Site_Use_Id.');
1053 raise;
1054
1055 END Get_Site_Use_Id;
1056
1057
1058 /*=======================================================================+
1059 | FUNCTION Get_Cross_Validated_Id will validate both name and number
1060 +=======================================================================*/
1061
1062
1063 FUNCTION Get_Cross_Validated_Id( p_entity IN VARCHAR2,
1064 p_number_value IN VARCHAR2,
1065 p_name_value IN VARCHAR2,
1066 p_return_status OUT NOCOPY VARCHAR2
1067 ) RETURN VARCHAR2 IS
1068 l_id_from_name VARCHAR2(100);
1069 l_id_from_num VARCHAR2(100);
1070 BEGIN
1071 arp_util.debug('ar_deposit_lib_pvt.Get_Cross_Validated_Id() +' );
1072 IF (p_number_value IS NULL) OR
1073 (p_name_value IS NULL)
1074 THEN
1075 RETURN(NULL);
1076 END IF;
1077
1078 p_return_status := FND_API.G_RET_STS_SUCCESS;
1079
1080 l_id_from_name := Get_Id(p_entity||'_NAME',
1081 p_name_value,
1082 p_return_status
1083 );
1084
1085 l_id_from_num := Get_Id(p_entity||'_NUMBER',
1086 p_number_value,
1087 p_return_status
1088 );
1089
1090 IF l_id_from_name = l_id_from_num THEN
1091 RETURN(l_id_from_name);
1092 ELSE
1093
1094 RETURN(NULL);
1095 END IF;
1096 arp_util.debug('ar_deposit_lib_pvt.Get_Cross_Validated_Id() -' );
1097 EXCEPTION
1098 WHEN others THEN
1099 arp_util.debug('EXCEPTION: Get_Cross_Validated_Id() '||p_entity);
1100 raise;
1101 END Get_Cross_Validated_Id;
1102
1103
1104
1105 /*=======================================================================+
1106 | Default_gl_date will find the defaulted value of gl date
1107 +=======================================================================*/
1108
1109 PROCEDURE Default_gl_date(p_entered_date IN DATE,
1110 p_gl_date OUT NOCOPY DATE,
1111 p_return_status OUT NOCOPY VARCHAR2) IS
1112 l_error_message VARCHAR2(128);
1113 l_defaulting_rule_used VARCHAR2(50);
1114 l_default_gl_date DATE;
1115 BEGIN
1116 p_return_status := FND_API.G_RET_STS_SUCCESS;
1117 arp_util.debug('ar_deposit_lib_pvt.Default_gl_date() +' );
1118 IF p_gl_date IS NULL THEN
1119 IF (arp_util.validate_and_default_gl_date(
1120 p_entered_date,
1121 NULL,
1122 NULL,
1123 NULL,
1124 NULL,
1125 p_entered_date,
1126 NULL,
1127 NULL,
1128 'N',
1129 NULL,
1130 arp_global.set_of_books_id,
1131 222,
1132 l_default_gl_date,
1133 l_defaulting_rule_used,
1134 l_error_message) = TRUE)
1135 THEN
1136 p_gl_date := l_default_gl_date;
1137 arp_util.debug('Defaulted GL Date : '||to_char(p_gl_date,'DD-MON-YYYY'));
1138 ELSE
1139 arp_util.debug('GL Date could not be defaulted ');
1140 -- Raise error message if failure in defaulting the gl_date
1141 FND_MESSAGE.SET_NAME('AR', 'GENERIC_MESSAGE');
1142 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', l_error_message);
1143 FND_MSG_PUB.Add;
1144 p_return_status := FND_API.G_RET_STS_ERROR;
1145 END IF;
1146 END IF;
1147 arp_util.debug('ar_deposit_lib_pvt.Default_gl_date() -' );
1148 END default_gl_date;
1149
1150
1151 /*=======================================================================+
1152 | Find_Exchange_Rate will find the exchanage rate
1153 +=======================================================================*/
1154
1155 FUNCTION Find_Exchange_Rate(
1156 p_currency_code IN ra_customer_trx.invoice_currency_code%TYPE,
1157 p_exchange_rate_date IN ra_customer_trx.exchange_date%TYPE,
1158 p_exchange_rate_type IN ra_customer_trx.exchange_rate_type%type
1159 )
1160 RETURN NUMBER IS
1161 l_count BINARY_INTEGER;
1162 l_index_curr BINARY_INTEGER;
1163 l_exchange_rate NUMBER;
1164 l_set_of_books_id NUMBER := arp_global.set_of_books_id;
1165 l_functional_currency VARCHAR2(100) := arp_global.functional_currency;
1166 BEGIN
1167 arp_util.debug('ar_deposit_lib_pvt.Find_Exchange_Rate() +' );
1168 IF (p_currency_code IS NOT NULL) AND
1169 (p_currency_code <> l_functional_currency) AND
1170 (p_exchange_rate_date IS NOT NULL) AND
1171 (p_exchange_rate_type IS NOT NULL) AND
1172 (p_exchange_rate_type <>'User')
1173 THEN
1174 -- This section of code is commented out as the implementation of the
1175 -- of the caching mechanism has been deferred as of now
1176
1177 l_exchange_rate := gl_currency_api.get_rate(
1178 l_set_of_books_id,
1179 p_currency_code,
1180 p_exchange_rate_date,
1181 p_exchange_rate_type
1182 );
1183
1184 END IF;
1185 RETURN( l_exchange_rate );
1186 arp_util.debug('ar_deposit_lib_pvt.Find_Exchange_Rate() -' );
1187 EXCEPTION
1188 WHEN gl_currency_api.NO_RATE THEN
1189 --rate does not exist set appropriate message.
1190 --p_return_status := FND_API.G_RET_STS_ERROR ;
1191 return(null);
1192 arp_util.debug('Exception : gl_currency_api.NO_RATE ');
1193 WHEN gl_currency_api.INVALID_CURRENCY THEN
1194 -- invalid currency set appropriate message.
1195 --p_return_status := FND_API.G_RET_STS_ERROR ;
1196 return(null);
1197 arp_util.debug('Exception: gl_currency_api.INVALID_CURRENCY ');
1198 WHEN others THEN
1199 arp_util.debug('EXCEPTION: Find_Exchange_Rate() ');
1200 raise;
1201 END Find_Exchange_Rate;
1202
1203 /*=======================================================================+
1204 | Get_cross_rate will cross_rate between currencies
1205 +=======================================================================*/
1206
1207 FUNCTION Get_cross_rate (p_from_currency IN VARCHAR2,
1208 p_to_currency IN VARCHAR2,
1209 p_exchange_rate_date IN DATE,
1210 p_exchange_rate IN NUMBER
1211 ) RETURN NUMBER IS
1212 l_euro_to_emu_rate NUMBER;
1213 l_fixed_rate BOOLEAN;
1214 l_relationship VARCHAR2(50);
1215 euro_code VARCHAR2(15);
1216 l_cross_rate NUMBER;
1217 BEGIN
1218 arp_util.debug('ar_deposit_lib_pvt.Get_cross_rate() +' );
1219 gl_currency_api.get_relation(
1220 p_from_currency,
1221 p_to_currency,
1222 trunc(p_exchange_rate_date),
1223 l_fixed_rate,
1224 l_relationship);
1225 euro_code := gl_currency_api.get_euro_code;
1226
1227 IF (l_relationship = 'EMU-OTHER') THEN
1228 l_euro_to_emu_rate :=
1229 gl_currency_api.get_rate(
1230 euro_code,
1231 p_from_currency,
1232 trunc(p_exchange_rate_date),
1233 NULL);
1234 ELSIF (l_relationship = 'OTHER-EMU') THEN
1235 l_euro_to_emu_rate :=
1236 gl_currency_api.get_rate(
1237 euro_code,
1238 p_to_currency,
1239 trunc(p_exchange_rate_date),
1240 NULL);
1241 ELSE
1242 RAISE gl_euro_user_rate_api.INVALID_RELATION;
1243 END IF;
1244 l_cross_rate :=
1245 gl_euro_user_rate_api.get_cross_rate(p_from_currency,
1246 p_to_currency,
1247 p_exchange_rate_date,
1248 p_exchange_rate,
1249 l_euro_to_emu_rate);
1250 return(l_cross_rate);
1251 arp_util.debug('ar_deposit_lib_pvt.Get_cross_rate() -' );
1252 EXCEPTION
1253 WHEN gl_euro_user_rate_api.INVALID_RELATION THEN
1254 null;
1255 WHEN gl_euro_user_rate_api.INVALID_CURRENCY THEN
1256 null;
1257 WHEN others THEN
1258 raise;
1259 END Get_cross_rate;
1260
1261 /*=======================================================================+
1262 | Default_Currency_info will find the default currency info
1263 +=======================================================================*/
1264
1265 PROCEDURE Default_Currency_info(
1266 p_currency_code IN OUT NOCOPY ra_customer_trx.invoice_currency_code%TYPE,
1267 p_receipt_date IN OUT NOCOPY ra_customer_trx.exchange_date%TYPE,
1268 p_exchange_rate_date IN OUT NOCOPY ra_customer_trx.exchange_date%TYPE,
1269 p_exchange_rate_type IN OUT NOCOPY ra_customer_trx.exchange_rate_type%TYPE,
1270 p_exchange_rate IN OUT NOCOPY ra_customer_trx.exchange_rate%TYPE,
1271 p_return_status OUT NOCOPY VARCHAR2
1272 ) IS
1273
1274 l_euro_to_emu_rate NUMBER;
1275 l_euro_to_other_prompt VARCHAR2(30);
1276 l_euro_to_emu_prompt VARCHAR2(30);
1277 l_emu_to_other_prompt VARCHAR2(30);
1278 l_cross_rate NUMBER;
1279 l_conversion_rate NUMBER;
1280 BEGIN
1281 arp_util.debug('ar_deposit_lib_pvt.Default_Currency_info() +' );
1282 p_return_status := FND_API.G_RET_STS_SUCCESS;
1283
1284 IF p_currency_code <> arp_global.functional_currency THEN
1285
1286 --default exchange rate date if null
1287 IF (p_exchange_rate_date IS NULL) THEN
1288 p_exchange_rate_date := p_receipt_date;
1289 END IF;
1290
1291 --default exchange rate type if null
1292 IF p_exchange_rate_type IS NULL THEN
1293 p_exchange_rate_type := pg_profile_def_x_rate_type;
1294 END IF;
1295
1296 IF p_exchange_rate_type IS NOT NULL THEN
1297
1298 IF p_exchange_rate_type <> 'User' THEN
1299 --for any exchange_rate type other than 'User',
1300 --default exchange rate if not entered.
1301 IF p_exchange_rate IS NULL THEN
1302 p_exchange_rate := Find_Exchange_Rate(
1303 p_currency_code,
1304 p_exchange_rate_date,
1305 p_exchange_rate_type
1306 );
1307 ELSE
1308 --if user has entered exchange rate for type <> User,
1309 -- raise error message
1310 p_return_status := FND_API.G_RET_STS_ERROR;
1311 FND_MESSAGE.SET_NAME('AR','AR_RAPI_X_RATE_INVALID');
1312 FND_MSG_PUB.Add;
1313 END IF;
1314 ELSE
1315 --case where rate_type is 'User'
1316
1317 --if the user entered exchange rate is greater than 0 then
1318 --check for the case of EMU currency
1319 IF p_exchange_rate >0 THEN
1320
1321 --This is the case rate_type is User and exchange_rate exists
1322
1323 -- Returns 'Y' if the current conversion type is User AND
1324 -- they are converting from EMU -> OTHER or OTHER -> EMU AND
1325 -- they are not allowed to enter EMU -> OTHER and
1326 -- OTHER -> EMU rates directly
1327 -- Returns 'N' Otherwise
1328
1329 IF (gl_euro_user_rate_api.is_cross_rate(p_currency_code,
1330 arp_global.functional_currency,
1331 p_exchange_rate_date,
1332 p_exchange_rate_type) = 'Y')
1333 THEN
1334
1335 gl_euro_user_rate_api.get_prompts_and_rate(
1336 p_currency_code,
1337 arp_global.functional_currency,
1338 p_exchange_rate_date,
1339 l_euro_to_other_prompt,
1340 l_euro_to_emu_prompt,
1341 l_emu_to_other_prompt,
1342 l_euro_to_emu_rate);
1343
1344 l_cross_rate :=
1345 gl_euro_user_rate_api.get_cross_rate(
1346 p_currency_code,
1347 p_currency_code,
1348 p_exchange_rate_date,
1349 p_exchange_rate,
1350 l_euro_to_emu_rate);
1351
1352 p_exchange_rate := l_cross_rate;
1353 ELSE
1354 -- case where gl_euro_user_rate_api.is_cross_rate = 'N'
1355 -- here the exchange_rate is directly between the EMU and the non-EMU currency.
1356
1357 p_exchange_rate := round(p_exchange_rate,38);
1358
1359 END IF; --is_cross_rate
1360
1361 END IF; -- exchange_rate >0
1362
1363 END IF; --rate type <> 'User'
1364 END IF; --if echange rate type IS NOT NULL
1365 END IF; --entered_currency <> functional currency
1366
1367 arp_util.debug('ar_deposit_lib_pvt.Default_Currency_info() -' );
1368 EXCEPTION
1369 WHEN others THEN
1370 arp_util.debug('EXCEPTION: Default_Currency_Info() ');
1371 arp_util.debug('p_currency_code = '||p_currency_code);
1372 raise;
1373 END Default_Currency_Info;
1374
1375 /*=======================================================================+
1376 | Default_deposit_ids will be called by ar_deposit_api_pub to perform some
1377 | basic validation and find id's based on name /number combination
1378 +=======================================================================*/
1379
1380 PROCEDURE Default_deposit_ids(
1381 x_salesrep_id IN OUT NOCOPY NUMBER,
1382 p_salesrep_name IN VARCHAR2 DEFAULT NULL,
1383 x_term_id IN OUT NOCOPY NUMBER ,
1384 p_term_name IN VARCHAR2 DEFAULT NULL,
1385 x_batch_source_id IN OUT NOCOPY NUMBER,
1386 p_batch_source_name IN ra_batch_sources.name%type,
1387 x_cust_trx_type_id IN OUT NOCOPY NUMBER,
1388 p_cust_trx_type IN varchar2,
1389 x_bill_to_customer_id IN OUT NOCOPY NUMBER,
1390 x_bill_to_customer_site_use_id IN OUT NOCOPY hz_cust_site_uses.site_use_id%TYPE,
1391 p_bill_to_customer_name IN hz_parties.party_name%TYPE,
1392 p_bill_to_customer_number IN
1393 hz_cust_accounts.account_number%TYPE,
1394 p_bill_to_location IN OUT NOCOPY hz_cust_site_uses.location%type,
1395 x_bill_to_contact_id IN OUT NOCOPY NUMBER,
1396 p_bill_to_contact_first_name IN VARCHAR2,
1397 p_bill_to_contact_last_name IN VARCHAR2,
1398 x_ship_to_customer_id IN OUT NOCOPY NUMBER,
1399 x_ship_to_customer_site_use_id IN OUT NOCOPY hz_cust_site_uses.site_use_id%TYPE,
1400 p_ship_to_customer_name IN hz_parties.party_name%TYPE,
1401 p_ship_to_customer_number IN
1402 hz_cust_accounts.account_number%TYPE,
1403 p_ship_to_location IN OUT NOCOPY hz_cust_site_uses.location%type,
1404 x_ship_to_contact_id IN OUT NOCOPY NUMBER,
1405 p_ship_to_contact_first_name IN VARCHAR2,
1406 p_ship_to_contact_last_name IN VARCHAR2,
1407 p_usr_currency_code IN fnd_currencies_vl.name%TYPE,
1408 p_usr_exchange_rate_type IN
1409 gl_daily_conversion_types.user_conversion_type%TYPE,
1410 x_currency_code IN OUT NOCOPY ar_cash_receipts.currency_code%TYPE,
1411 x_exchange_rate_type IN OUT NOCOPY
1412 ar_cash_receipts.exchange_rate_type%TYPE,
1413 x_remit_to_address_id IN OUT NOCOPY NUMBER ,
1414 p_cust_location_site_num IN VARCHAR2,
1415 x_sold_to_customer_id IN OUT NOCOPY NUMBER,
1416 p_sold_to_customer_name IN VARCHAR2,
1417 p_sold_to_customer_number IN VARCHAR2,
1418 x_paying_customer_id IN OUT NOCOPY NUMBER ,
1419 x_paying_customer_site_use_id IN OUT NOCOPY hz_cust_site_uses.site_use_id%TYPE,
1420 p_paying_customer_name IN VARCHAR2,
1421 p_paying_customer_number IN VARCHAR2,
1422 p_paying_location IN VARCHAR2,
1423 x_receipt_method_id IN OUT NOCOPY NUMBER ,
1424 p_receipt_method_name IN OUT NOCOPY VARCHAR2,
1425 x_cust_bank_account_id IN OUT NOCOPY NUMBER,
1426 p_cust_bank_account_name IN VARCHAR2,
1427 p_cust_bank_account_number IN VARCHAR2,
1428 x_memo_line_id IN OUT NOCOPY NUMBER,
1429 p_memo_line_name IN VARCHAR2,
1430 x_inventory_id IN OUT NOCOPY NUMBER,
1431 p_deposit_number IN VARCHAR2,
1432 p_deposit_date IN DATE,
1433 p_return_status OUT NOCOPY VARCHAR2
1434 ) IS
1435
1436 l_receipt_method_id NUMBER;
1437 l_cust_bank_account_id NUMBER;
1438 l_customer_id NUMBER;
1439 l_get_id_return_status VARCHAR2(1);
1440 l_get_x_val_return_status VARCHAR2(1);
1441 l_dummy_return_status VARCHAR2(1);
1442 l_site_use_return_status VARCHAR2(1);
1443 l_contact_return_status VARCHAR2(1);
1444 l_remit_to_address_rec ARP_TRX_DEFAULTS_3.address_rec_type;
1445 l_dft_remit_to_address_rec ARP_TRX_DEFAULTS_3.address_rec_type;
1446 l_dft_remit_to_address_id NUMBER ;
1447 l_remit_to_address_id NUMBER;
1448 l_match_state hz_locations.state%type;
1449 l_match_country hz_locations.country%type;
1450 l_match_postal_code hz_locations.postal_code%type;
1451 l_match_address_id NUMBER :=NULL;
1452 l_match_site_use_id NUMBER :=NULL;
1453 l_pay_unrelated_invoices_flag
1454 ar_system_parameters.pay_unrelated_invoices_flag%type;
1455 l_bill_to_location hz_cust_site_uses.location%type;
1456 l_ship_to_location hz_cust_site_uses.location%type;
1457 l_dft_bill_to_location hz_cust_site_uses.location%type;
1458 l_dft_ship_to_location hz_cust_site_uses.location%type;
1459 l_dummy NUMBER;
1460
1461
1462 BEGIN
1463 arp_util.debug('ar_deposit_lib_pvt.Default_deposit_ids() +' );
1464 p_return_status := FND_API.G_RET_STS_SUCCESS;
1465 pg_deposit_date := p_deposit_date;
1466 l_bill_to_location := p_bill_to_location;
1467 l_ship_to_location := p_ship_to_location;
1468
1469
1470
1471 IF x_term_id IS NULL THEN
1472
1473 IF p_term_name IS NOT NULL THEN
1474 begin
1475 SELECT term_id
1476 INTO x_term_id
1477 FROM ra_terms
1478 WHERE name = p_term_name and
1479 nvl(p_deposit_date, trunc(sysdate)) between start_date_active
1480 and nvl(end_date_active,nvl(p_deposit_date,trunc(sysdate)));
1481
1482 exception
1483 when no_data_found then
1484 FND_MESSAGE.SET_NAME('AR','AR_DAPI_TERM_NAME_INVALID');
1485 FND_MSG_PUB.Add;
1486 p_return_status := FND_API.G_RET_STS_ERROR;
1487
1488 end;
1489 END IF;
1490 ELSE
1491 begin
1492 SELECT term_id
1493 INTO x_term_id
1494 FROM ra_terms
1495 WHERE term_id = x_term_id and
1496 nvl(p_deposit_date, trunc(sysdate)) between start_date_active
1497 and nvl(end_date_active,nvl(p_deposit_date,trunc(sysdate)));
1498
1499 exception
1500 when no_data_found then
1501 FND_MESSAGE.SET_NAME('AR','AR_DAPI_TERM_ID_INVALID');
1502 FND_MSG_PUB.Add;
1503 p_return_status := FND_API.G_RET_STS_ERROR;
1504
1505 end;
1506
1507 END IF;
1508
1509
1510 IF x_salesrep_id IS NULL AND
1511 p_salesrep_name IS NOT NULL
1512 THEN
1513 begin
1514 SELECT salesrep_id
1515 INTO x_salesrep_id
1516 FROM ra_salesreps
1517 WHERE name = p_salesrep_name and
1518 NVL(status,'A') ='A' and
1519 p_deposit_date between nvl(start_date_active, p_deposit_date) and
1520 nvl(end_date_active, p_deposit_date);
1521
1522 exception
1523 when no_data_found then
1524 FND_MESSAGE.SET_NAME('AR','AR_DAPI_SALESREP_NAME_INVALID');
1525 FND_MSG_PUB.Add;
1526 p_return_status := FND_API.G_RET_STS_ERROR;
1527
1528 end;
1529 ELSIF (x_salesrep_id IS NOT NULL) THEN
1530
1531 begin
1532 SELECT salesrep_id
1533 INTO x_salesrep_id
1534 FROM ra_salesreps
1535 WHERE salesrep_id = x_salesrep_id and
1536 NVL(status,'A') ='A' and
1537 p_deposit_date between nvl(start_date_active, p_deposit_date) and
1538 nvl(end_date_active, p_deposit_date);
1539
1540 exception
1541 when no_data_found then
1542 FND_MESSAGE.SET_NAME('AR','AR_DAPI_SALESREP_ID_INVALID');
1543 FND_MSG_PUB.Add;
1544 p_return_status := FND_API.G_RET_STS_ERROR;
1545 end;
1546
1547
1548 END IF;
1549
1550
1551
1552 IF x_memo_line_id IS NULL THEN
1553 IF p_memo_line_name IS NOT NULL
1554 THEN
1555 BEGIN
1556 select memo_line_id
1557 into x_memo_line_id
1558 from ar_memo_lines
1559 where line_type='LINE' and
1560 sysdate between nvl(trunc(start_date),sysdate)
1561 and nvl(trunc(end_date),sysdate) and
1562 name = p_memo_line_name;
1563 EXCEPTION
1564 when no_data_found then
1565 FND_MESSAGE.SET_NAME('AR','AR_DAPI_MEMO_NAME_INVALID');
1566 FND_MSG_PUB.Add;
1567 p_return_status := FND_API.G_RET_STS_ERROR;
1568 END;
1569
1570 END IF;
1571 ELSE
1572 IF p_memo_line_name IS NOT NULL
1573 THEN
1574 FND_MESSAGE.SET_NAME('AR','AR_DAPI_MEMO_WRG');
1575 FND_MSG_PUB.Add;
1576 END IF;
1577
1578 BEGIN
1579 select memo_line_id
1580 into l_dummy
1581 from ar_memo_lines
1582 where line_type='LINE' and
1583 sysdate between nvl(trunc(start_date),sysdate)
1584 and nvl(trunc(end_date),sysdate) and
1585 memo_line_id = x_memo_line_id;
1586 EXCEPTION
1587 when no_data_found then
1588 FND_MESSAGE.SET_NAME('AR','AR_DAPI_MEMO_NAME_INVALID');
1589 FND_MSG_PUB.Add;
1590 p_return_status := FND_API.G_RET_STS_ERROR;
1591 END;
1592
1593 END IF;
1594
1595 IF x_inventory_id IS NOT NULL THEN
1596
1597 BEGIN
1598 select inventory_item_id
1599 into l_dummy
1600 from MTL_SYSTEM_ITEMS_B
1601 where inventory_item_id = x_inventory_id and
1602 ORGANIZATION_ID = oe_profile.value('SO_ORGANIZATION_ID') and
1603 ENABLED_FLAG = 'Y';
1604 EXCEPTION
1605 when no_data_found then
1606 FND_MESSAGE.SET_NAME('AR','AR_DAPI_INV_ID_INVALID');
1607 FND_MSG_PUB.Add;
1608 p_return_status := FND_API.G_RET_STS_ERROR;
1609 END;
1610 IF x_memo_line_id is NOT NULL then
1611 FND_MESSAGE.SET_NAME('AR','AR_DAPI_INV_MEMO_COM');
1612 FND_MSG_PUB.Add;
1613 p_return_status := FND_API.G_RET_STS_ERROR;
1614 END IF;
1615
1616 END IF;
1617
1618
1619 IF x_batch_source_id IS NULL THEN
1620 IF p_batch_source_name IS NOT NULL
1621 THEN
1622
1623 begin
1624 /* SELECT batch_source_id
1625 INTO x_batch_source_id
1626 FROM ra_batch_sources
1627 WHERE name = p_batch_source_name and
1628 batch_source_id not in (11,12) and
1629 nvl(status,'A')= 'A' and (batch_source_type = 'INV');*/
1630 SELECT batch_source_id
1631 INTO x_batch_source_id
1632 FROM ra_batch_sources bs
1633 WHERE name = p_batch_source_name and
1634 batch_source_id not in (11,12) and
1635 nvl(status,'A')= 'A' and (batch_source_type = 'INV') and
1636 (( p_deposit_number is not null and
1637 auto_trx_numbering_flag = 'N' ) or
1638 ( p_deposit_number is null and
1639 auto_trx_numbering_flag = 'Y' ) ) and
1640 nvl(p_deposit_date, trunc(sysdate)) between nvl(bs.start_date, nvl(p_deposit_date, trunc(sysdate))) and
1641 nvl(bs.end_date, nvl(p_deposit_date, trunc(sysdate)));
1642
1643 exception
1644 when no_data_found then
1645 arp_util.debug('x_batch_source_id IS NULL');
1646 FND_MESSAGE.SET_NAME('AR','AR_DAPI_BS_NAME_INVALID');
1647 FND_MSG_PUB.Add;
1648 p_return_status := FND_API.G_RET_STS_ERROR;
1649
1650 end;
1651
1652 END IF;
1653 ELSE
1654 --validate the id here or in vadidate_deposit routine
1655 IF p_batch_source_name IS NOT NULL then
1656 arp_util.debug('x_batch_source_id IS ignored');
1657 FND_MESSAGE.SET_NAME('AR','AR_DAPI_BS_NAME_IGN');
1658 FND_MSG_PUB.Add;
1659
1660 END IF;
1661
1662 begin
1663 /* SELECT batch_source_id
1664 INTO l_dummy
1665 FROM ra_batch_sources
1666 WHERE batch_source_id = x_batch_source_id and
1667 batch_source_id not in (11,12) and
1668 nvl(status,'A')= 'A' and (batch_source_type = 'INV');*/
1669 SELECT batch_source_id
1670 INTO l_dummy
1671 FROM ra_batch_sources bs
1672 WHERE batch_source_id = x_batch_source_id and
1673 batch_source_id not in (11,12) and
1674 nvl(status,'A')= 'A' and (batch_source_type = 'INV') and
1675 (( p_deposit_number is not null and
1676 auto_trx_numbering_flag = 'N' ) or
1677 ( p_deposit_number is null and
1678 auto_trx_numbering_flag = 'Y' ) ) and
1679 nvl(p_deposit_date, trunc(sysdate)) between nvl(bs.start_date, nvl(p_deposit_date, trunc(sysdate))) and
1680 nvl(bs.end_date, nvl(p_deposit_date, trunc(sysdate)));
1681 exception
1682 when no_data_found then
1683 arp_util.debug('x_batch_source_id :no_data_found');
1684 FND_MESSAGE.SET_NAME('AR','AR_DAPI_BS_ID_INVALID');
1685 FND_MSG_PUB.Add;
1686 p_return_status := FND_API.G_RET_STS_ERROR;
1687
1688 end;
1689
1690 END IF;
1691
1692 IF x_cust_trx_type_id IS NULL THEN
1693 IF p_cust_trx_type IS NOT NULL THEN
1694
1695 BEGIN
1696 SELECT cust_trx_type_id
1697 INTO x_cust_trx_type_id
1698 FROM ra_cust_trx_types
1699 where type = 'DEP' and
1700 nvl(p_deposit_date, trunc(sysdate)) between
1701 nvl(start_date(+), nvl(p_deposit_date, trunc(sysdate))) and
1702 nvl(end_date(+), nvl(p_deposit_date, trunc(sysdate))) and
1703 NAME = p_cust_trx_type;
1704 EXCEPTION
1705
1706 WHEN NO_DATA_FOUND THEN
1707 FND_MESSAGE.SET_NAME('AR','AR_DAPI_TRANS_TYPE_INVALID');
1708 FND_MSG_PUB.Add;
1709 p_return_status := FND_API.G_RET_STS_ERROR;
1710 WHEN OTHERS THEN
1711 RAISE;
1712 END;
1713 /* ELSE
1714 FND_MESSAGE.SET_NAME('AR','AR_DAPI_TRANS_TYPE_NULL');
1715 FND_MSG_PUB.Add;
1716 p_return_status := FND_API.G_RET_STS_ERROR;
1717 */
1718 END IF;
1719 ELSE
1720 IF p_cust_trx_type IS NOT NULL THEN
1721 FND_MESSAGE.SET_NAME('AR','AR_DAPI_TRANS_TYPE_IGN');
1722 FND_MSG_PUB.Add;
1723 END IF;
1724
1725 -- validate x_cust_trx_type_id
1726 BEGIN
1727 SELECT cust_trx_type_id
1728 INTO l_dummy
1729 FROM ra_cust_trx_types
1730 where type = 'DEP' and
1731 nvl(p_deposit_date, trunc(sysdate)) between
1732 nvl(start_date(+), nvl(p_deposit_date, trunc(sysdate))) and
1733 nvl(end_date(+), nvl(p_deposit_date, trunc(sysdate))) and
1734 cust_trx_type_id = x_cust_trx_type_id;
1735 EXCEPTION
1736
1737 WHEN NO_DATA_FOUND THEN
1738 FND_MESSAGE.SET_NAME('AR','AR_DAPI_TRANS_TYPE_ID_INVALID');
1739 FND_MSG_PUB.Add;
1740 p_return_status := FND_API.G_RET_STS_ERROR;
1741 WHEN OTHERS THEN
1742 RAISE;
1743 END;
1744
1745 END IF;
1746
1747
1748 -- get BILL to Customer ID/Number/Name
1749 IF (x_bill_to_customer_id is NULL)
1750 THEN
1751 IF(p_bill_to_customer_name IS NOT NULL) and
1752 (p_bill_to_customer_number IS NULL)
1753 THEN
1754 x_bill_to_customer_id := Get_Id('CUSTOMER_NAME',
1755 p_bill_to_customer_name,
1756 l_dummy_return_status);
1757 IF x_bill_to_customer_id IS NULL THEN
1758 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUS_NAME_INVALID');
1759 FND_MSG_PUB.Add;
1760 p_return_status := FND_API.G_RET_STS_ERROR;
1761 END IF;
1762
1763 ELSIF(p_bill_to_customer_name IS NULL) and
1764 (p_bill_to_customer_number IS NOT NULL)
1765 THEN
1766 x_bill_to_customer_id := Get_Id( 'CUSTOMER_NUMBER',
1767 p_bill_to_customer_number,
1768 l_dummy_return_status);
1769 IF x_bill_to_customer_id IS NULL THEN
1770 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUS_NUM_INVALID');
1771 FND_MSG_PUB.Add;
1772 p_return_status := FND_API.G_RET_STS_ERROR;
1773 END IF;
1774
1775 ELSIF(p_bill_to_customer_name IS NOT NULL) and
1776 (p_bill_to_customer_number IS NOT NULL)
1777 THEN
1778 x_bill_to_customer_id := Get_Cross_Validated_Id( 'CUSTOMER',
1779 p_bill_to_customer_number,
1780 p_bill_to_customer_name,
1781 l_dummy_return_status);
1782 IF x_bill_to_customer_id IS NULL THEN
1783 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUS_NAME_NUM_INVALID');
1784 FND_MSG_PUB.Add;
1785 p_return_status := FND_API.G_RET_STS_ERROR;
1786 END IF;
1787
1788
1789 END IF;
1790
1791 ELSE
1792 --In case the ID has been entered by the user
1793 IF (p_bill_to_customer_name IS NOT NULL) OR
1794 (p_bill_to_customer_number IS NOT NULL) THEN
1795 --give a warning message to indicate that the
1796 -- customer_number and customer_name
1797 --entered by the user have been ignored.
1798 IF FND_MSG_PUB.Check_Msg_Level(G_MSG_SUCCESS)
1799 THEN
1800 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUS_NAME_NUM_IGN');
1801 FND_MSG_PUB.Add;
1802 p_return_status := FND_API.G_RET_STS_ERROR;
1803 END IF;
1804 END IF;
1805 /*--------------------------------+
1806 | |
1807 | Validate Customer_id |
1808 | |
1809 +--------------------------------*/
1810 BEGIN
1811 SELECT cust.cust_account_id
1812 INTO l_customer_id
1813 FROM hz_cust_accounts cust,
1814 hz_customer_profiles cp,
1815 hz_parties party
1816 WHERE cust.cust_account_id = cp.cust_account_id (+) and
1817 cp.site_use_id is null and
1818 cust.cust_account_id = x_bill_to_customer_id and
1819 cust.status <> 'I' and
1820 cust.party_id = party.party_id;
1821 EXCEPTION
1822 WHEN NO_DATA_FOUND THEN
1823 p_return_status := FND_API.G_RET_STS_ERROR;
1824 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUST_ID_INVALID');
1825 FND_MSG_PUB.Add;
1826
1827 WHEN OTHERS THEN
1828 arp_util.debug('EXCEPTION: Cache_Customer_id() ');
1829 arp_util.debug('p_customer_id = ' ||
1830 TO_CHAR(x_bill_to_customer_id));
1831 RAISE;
1832 END;
1833
1834
1835 END IF;
1836
1837
1838 -- get SHIP to Customer ID/Number/Name
1839 IF (x_ship_to_customer_id IS NOT NULL or
1840 p_ship_to_customer_name IS NOT NULL or
1841 p_ship_to_customer_number IS NOT NULL ) THEN
1842
1843 IF (x_ship_to_customer_id is NULL)
1844 THEN
1845 IF(p_ship_to_customer_name IS NOT NULL) and
1846 (p_ship_to_customer_number IS NULL)
1847 THEN
1848 x_ship_to_customer_id := Get_Id('CUSTOMER_NAME',
1849 p_ship_to_customer_name,
1850 l_dummy_return_status);
1851 IF x_ship_to_customer_id IS NULL THEN
1852 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUS_NAME_INVALID');
1853 FND_MSG_PUB.Add;
1854 p_return_status := FND_API.G_RET_STS_ERROR;
1855 END IF;
1856
1857 ELSIF(p_ship_to_customer_name IS NULL) and
1858 (p_ship_to_customer_number IS NOT NULL)
1859 THEN
1860 x_ship_to_customer_id := Get_Id( 'CUSTOMER_NUMBER',
1861 p_ship_to_customer_number,
1862 l_dummy_return_status);
1863 IF x_ship_to_customer_id IS NULL THEN
1864 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUS_NUM_INVALID');
1865 FND_MSG_PUB.Add;
1866 p_return_status := FND_API.G_RET_STS_ERROR;
1867 END IF;
1868
1869 ELSIF(p_ship_to_customer_name IS NOT NULL) and
1870 (p_ship_to_customer_number IS NOT NULL)
1871 THEN
1872 x_ship_to_customer_id := Get_Cross_Validated_Id
1873 ( 'CUSTOMER',
1874 p_ship_to_customer_number,
1875 p_ship_to_customer_name,
1876 l_dummy_return_status);
1877 IF x_ship_to_customer_id IS NULL THEN
1878 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUS_NAME_NUM_INVALID');
1879 FND_MSG_PUB.Add;
1880 p_return_status := FND_API.G_RET_STS_ERROR;
1881 END IF;
1882 END IF;
1883
1884 ELSE
1885 --In case the ID has been entered by the user
1886 IF (p_ship_to_customer_name IS NOT NULL) OR
1887 (p_ship_to_customer_number IS NOT NULL) THEN
1888 --give a warning message to indicate that the customer_number
1889 --and customer_name
1890 --entered by the user have been ignored.
1891 IF FND_MSG_PUB.Check_Msg_Level(G_MSG_SUCCESS)
1892 THEN
1893 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUS_NAME_NUM_IGN');
1894 FND_MSG_PUB.Add;
1895 p_return_status := FND_API.G_RET_STS_ERROR;
1896 END IF;
1897 END IF;
1898 /*--------------------------------+
1899 | |
1900 | Validate Customer_id |
1901 | |
1902 +--------------------------------*/
1903 BEGIN
1904 SELECT cust.cust_account_id
1905 INTO l_customer_id
1906 FROM hz_cust_accounts cust,
1907 hz_customer_profiles cp,
1908 hz_parties party
1909 WHERE cust.cust_account_id = cp.cust_account_id (+) and
1910 cp.site_use_id is null and
1911 cust.cust_account_id = x_ship_to_customer_id and
1912 cust.status <> 'I' and
1913 cust.party_id = party.party_id;
1914 EXCEPTION
1915 WHEN NO_DATA_FOUND THEN
1916 p_return_status := FND_API.G_RET_STS_ERROR;
1917 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUST_ID_INVALID');
1918 FND_MSG_PUB.Add;
1919
1920 WHEN OTHERS THEN
1921 arp_util.debug('x_ship_to_customer_id = ' ||
1922 TO_CHAR(x_ship_to_customer_id));
1923 RAISE;
1924 END;
1925
1926
1927 END IF;
1928
1929
1930 END IF;
1931
1932 --through an error if both are null
1933 IF x_bill_to_customer_id is null and
1934 x_ship_to_customer_id is null
1935 THEN
1936 FND_MESSAGE.SET_NAME('AR','AR_DAPI_BILL_OR_SHIP_CUST_REQ');
1937 FND_MSG_PUB.Add;
1938 p_return_status := FND_API.G_RET_STS_ERROR;
1939 END IF;
1940
1941
1942 -- find default for l_bill_to_location
1943 Begin
1944 select su.location
1945 into l_dft_bill_to_location
1946 from hz_cust_acct_sites acct_site,
1947 hz_party_sites party_site,
1948 hz_locations loc,
1949 hz_cust_site_uses su,
1950 fnd_territories_vl t
1951 where acct_site.cust_acct_site_id = su.cust_acct_site_id
1952 and acct_site.party_site_id = party_site.party_site_id
1953 and loc.location_id = party_site.location_id
1954 and loc.country = t.territory_code
1955 and acct_site.cust_account_id = x_bill_to_customer_id
1956 and su.site_use_id = nvl(null,su.site_use_id)
1957 and su.site_use_code = 'BILL_TO'
1958 and ( su.site_use_id = null
1959 or ( su.status = 'A'
1960 and acct_site.status = 'A'
1961 )
1962 )
1963 and su.primary_flag = 'Y';
1964
1965 exception
1966 when no_data_found then
1967 l_dft_bill_to_location:= null;
1968 end;
1969
1970 if l_bill_to_location is null then
1971 l_bill_to_location := l_dft_bill_to_location;
1972 p_bill_to_location := l_dft_bill_to_location;
1973 end if;
1974
1975
1976 -- find default for l_ship_to_location
1977 Begin
1978 select su.location
1979 into l_dft_ship_to_location
1980 from hz_cust_acct_sites acct_site,
1981 hz_party_sites party_site,
1982 hz_locations loc,
1983 hz_cust_site_uses su,
1984 fnd_territories_vl t
1985 where acct_site.cust_acct_site_id = su.cust_acct_site_id
1986 and acct_site.party_site_id = party_site.party_site_id
1987 and loc.location_id = party_site.location_id
1988 and loc.country = t.territory_code
1989 and acct_site.cust_account_id = x_ship_to_customer_id
1990 and su.site_use_id = nvl(null,su.site_use_id)
1991 and su.site_use_code = 'SHIP_TO'
1992 and ( su.site_use_id = null
1993 or ( su.status = 'A'
1994 and acct_site.status = 'A'
1995 )
1996 )
1997 and su.primary_flag = 'Y';
1998
1999 exception
2000 when no_data_found then
2001 l_dft_ship_to_location:= null;
2002 end;
2003
2004 if l_ship_to_location is null then
2005 l_ship_to_location := l_dft_ship_to_location;
2006 p_ship_to_location := l_dft_ship_to_location;
2007 end if;
2008
2009 -- default bill to from ship to if bill to is null
2010 IF x_bill_to_customer_id is null THEN
2011
2012 x_bill_to_customer_id := x_ship_to_customer_id;
2013 l_bill_to_location := l_ship_to_location;
2014 p_bill_to_location := l_ship_to_location;
2015 FND_MESSAGE.SET_NAME('AR','AR_DAPI_BILL_VAL_SHIP_IGN');
2016 FND_MSG_PUB.Add;
2017 END IF;
2018
2019
2020
2021 --get bill to Customer site use id
2022 IF x_bill_to_customer_id IS NOT NULL THEN
2023 --we need to validate location here
2024
2025 x_bill_to_customer_site_use_id := Get_Site_Use_Id(x_bill_to_customer_id,
2026 l_bill_to_location,
2027 'BILL_TO',
2028 'DRAWEE' ,
2029 l_site_use_return_status
2030 );
2031 if x_bill_to_customer_site_use_id is null then
2032 FND_MESSAGE.SET_NAME('AR','AR_DAPI_LOC_INV');
2033 FND_MSG_PUB.Add;
2034 p_return_status := FND_API.G_RET_STS_ERROR;
2035 end if;
2036
2037 END IF;
2038
2039
2040
2041 IF (x_bill_to_contact_id is NULL)
2042 THEN
2043
2044 IF(p_bill_to_contact_first_name IS NOT NULL) and
2045 (p_bill_to_contact_last_name IS NOT NULL)
2046
2047 THEN
2048
2049 x_bill_to_contact_id :=
2050 AR_DEPOSIT_LIB_PVT.GET_CONTACT_ID(x_bill_to_customer_id,
2051 p_bill_to_contact_first_name,
2052 p_bill_to_contact_last_name,
2053 l_contact_return_status);
2054 if x_bill_to_contact_id is null then
2055 FND_MESSAGE.SET_NAME('AR','AR_DAPI_BIll_CONTACT_COM_INV');
2056 FND_MSG_PUB.Add;
2057 p_return_status := FND_API.G_RET_STS_ERROR;
2058 end if;
2059
2060 ELSIF (p_bill_to_contact_first_name IS NULL and
2061 p_bill_to_contact_last_name IS NOT NULL) OR
2062 (p_bill_to_contact_first_name IS NOT NULL and
2063 p_bill_to_contact_last_name IS NULL) THEN
2064 arp_util.debug('Bill_to_contact_id both last and
2065 first name are required');
2066 FND_MESSAGE.SET_NAME('AR','AR_DAPI_BIll_CONTACT_NAME_INV');
2067 FND_MSG_PUB.Add;
2068 p_return_status := FND_API.G_RET_STS_ERROR;
2069
2070 END IF;
2071
2072 ELSE
2073
2074 --validate contact id
2075 begin
2076 SELECT acct_role.cust_account_role_id
2077 INTO l_dummy
2078 from hz_cust_account_roles acct_role,
2079 hz_parties party,
2080 hz_relationships rel,
2081 hz_org_contacts org_cont,
2082 hz_parties rel_party
2083 where acct_role.party_id = rel.party_id
2084 and acct_role.role_type = 'CONTACT'
2085 and org_cont.party_relationship_id =
2086 rel.relationship_id
2087 and rel.subject_id = party.party_id
2088 and rel.party_id = rel_party.party_id
2089 and rel.subject_table_name = 'HZ_PARTIES'
2090 and rel.object_table_name = 'HZ_PARTIES'
2091 and rel.directional_flag = 'F'
2092 and acct_role.cust_account_id = x_bill_to_customer_id
2093 /* the contact must be active. however, for credit memos
2094 against specific transactions, the credited transaction's
2095 contact may also be used even if it is not active. */
2096 AND acct_role.status = 'A'
2097 AND acct_role.cust_account_role_id = x_bill_to_contact_id;
2098
2099 exception
2100 when no_data_found then
2101 p_return_status := FND_API.G_RET_STS_ERROR;
2102 FND_MESSAGE.SET_NAME('AR','AR_DAPI_CUS_CONTACT_INVALID');
2103 FND_MSG_PUB.Add;
2104
2105 when others then
2106 arp_util.debug('EXCEPTIONS: Others , GET_CONTACT_ID()');
2107 end;
2108
2109 END IF;
2110
2111
2112 --get ship to Customer site use id
2113 IF x_ship_to_customer_id IS NOT NULL THEN
2114
2115 x_ship_to_customer_site_use_id :=
2116 Get_Site_Use_Id(x_ship_to_customer_id,
2117 l_ship_to_location,
2118 'SHIP_TO',
2119 NULL ,l_site_use_return_status
2120 );
2121 if x_ship_to_customer_site_use_id is null then
2122 FND_MESSAGE.SET_NAME('AR','AR_DAPI_LOC_INV');
2123 FND_MSG_PUB.Add;
2124 p_return_status := FND_API.G_RET_STS_ERROR;
2125 end if;
2126 IF (x_ship_to_contact_id is NULL)
2127 THEN
2128 IF(p_ship_to_contact_first_name IS NOT NULL) and
2129 (p_ship_to_contact_last_name IS NOT NULL)
2130
2131 THEN
2132
2133 x_ship_to_contact_id :=
2134 AR_DEPOSIT_LIB_PVT.GET_CONTACT_ID(x_ship_to_customer_id,
2135 p_ship_to_contact_first_name,
2136 p_ship_to_contact_last_name,
2137 l_contact_return_status);
2138 if x_ship_to_contact_id is null then
2139 FND_MESSAGE.SET_NAME('AR','AR_DAPI_SHIP_CONTACT_COM_INV');
2140 FND_MSG_PUB.Add;
2141 p_return_status := FND_API.G_RET_STS_ERROR;
2142 end if;
2143
2144 ELSIF(p_ship_to_contact_first_name IS NULL and
2145 p_ship_to_contact_last_name IS NOT NULL) OR
2146 (p_ship_to_contact_first_name IS NOT NULL and
2147 p_ship_to_contact_last_name IS NULL) THEN
2148
2149 arp_util.debug('Ship_to_contact_id both last and
2150 first name are required');
2151 FND_MESSAGE.SET_NAME('AR','AR_DAPI_SHIP_CONTACT_NAME_INV');
2152 FND_MSG_PUB.Add;
2153 p_return_status := FND_API.G_RET_STS_ERROR;
2154 END IF;
2155
2156 ELSE
2157 --validate x_ship_to_contact_id
2158 begin
2159 SELECT acct_role.cust_account_role_id
2160 INTO l_dummy
2161 from hz_cust_account_roles acct_role,
2162 hz_parties party,
2163 hz_relationships rel,
2164 hz_org_contacts org_cont,
2165 hz_parties rel_party
2166 where acct_role.party_id = rel.party_id
2167 and acct_role.role_type = 'CONTACT'
2168 and org_cont.party_relationship_id =
2169 rel.relationship_id
2170 and rel.subject_id = party.party_id
2171 and rel.party_id = rel_party.party_id
2172 and rel.subject_table_name = 'HZ_PARTIES'
2173 and rel.object_table_name = 'HZ_PARTIES'
2174 and rel.directional_flag = 'F'
2175 and acct_role.cust_account_id = x_ship_to_customer_id
2176 /* the contact must be active. however, for credit memos
2177 against specific transactions, the credited transaction's
2178 contact may also be used even if it is not active. */
2179 AND acct_role.status = 'A'
2180 AND acct_role.cust_account_role_id = x_ship_to_contact_id;
2181
2182 exception
2183 when no_data_found then
2184 p_return_status := FND_API.G_RET_STS_ERROR;
2185 FND_MESSAGE.SET_NAME('AR','AR_DAPI_CUS_CONTACT_INVALID');
2186 FND_MSG_PUB.Add;
2187
2188 when others then
2189 arp_util.debug('EXCEPTIONS: Others , GET_CONTACT_ID()');
2190 end;
2191
2192 END IF;
2193
2194 END IF;
2195
2196 --Receipt method ID,Name
2197 IF x_receipt_method_id IS NULL
2198 THEN
2199 IF p_receipt_method_name IS NOT NULL THEN
2200 x_receipt_method_id := Get_Id('RECEIPT_METHOD_NAME',
2201 p_receipt_method_name,
2202 l_get_id_return_status
2203 );
2204 IF x_receipt_method_id IS NULL THEN
2205 FND_MESSAGE.SET_NAME('AR','AR_RAPI_RCPT_MD_NAME_INVALID');
2206 FND_MSG_PUB.Add;
2207 p_return_status := FND_API.G_RET_STS_ERROR;
2208 END IF;
2209 END IF;
2210
2211 ELSE
2212 IF (p_receipt_method_name IS NOT NULL) THEN
2213 --give a warning message to indicate that the receipt_method_name
2214 --entered by the user has been ignored.
2215 IF FND_MSG_PUB.Check_Msg_Level(G_MSG_SUCCESS)
2216 THEN
2217 FND_MESSAGE.SET_NAME('AR','AR_RAPI_RCPT_MD_NAME_IGN');
2218 FND_MSG_PUB.Add;
2219 END IF;
2220 ELSE
2221 BEGIN
2222 SELECT name
2223 INTO p_receipt_method_name
2224 FROM ar_receipt_methods
2225 WHERE receipt_method_id = x_receipt_method_id;
2226 EXCEPTION
2227 WHEN no_data_found THEN
2228 p_return_status := FND_API.G_RET_STS_ERROR;
2229 FND_MESSAGE.SET_NAME('AR','AR_RAPI_RCPT_MD_ID_INVALID');
2230 FND_MSG_PUB.Add;
2231 arp_util.debug('Invalid receipt method id ');
2232 END;
2233
2234 END IF;
2235
2236 END IF;
2237
2238
2239 --Customer bank account Number,Name,ID
2240 -- REMOVED FOR PAYMENT UPTAKE there is no need to default the Customer bank account Number,Name,ID
2241
2242 -- Exchange_rate_type
2243 IF x_exchange_rate_type IS NULL THEN
2244 IF p_usr_exchange_rate_type IS NOT NULL
2245 THEN
2246 x_exchange_rate_type := Get_Id('EXCHANGE_RATE_NAME',
2247 p_usr_exchange_rate_type,
2248 l_get_id_return_status
2249 );
2250 IF x_exchange_rate_type IS NULL THEN
2251 FND_MESSAGE.SET_NAME('AR','AR_RAPI_USR_X_RATE_TYP_INVALID');
2252 FND_MSG_PUB.Add;
2253 p_return_status := FND_API.G_RET_STS_ERROR;
2254 END IF;
2255 END IF;
2256
2257 ELSE
2258 IF (p_usr_exchange_rate_type IS NOT NULL) THEN
2259 --give a warning message to indicate that the usr_exchange_rate_type
2260 -- entered by the user have been ignored.
2261 IF FND_MSG_PUB.Check_Msg_Level(G_MSG_SUCCESS)
2262 THEN
2263 FND_MESSAGE.SET_NAME('AR','AR_RAPI_USR_X_RATE_TYPE_IGN');
2264 FND_MSG_PUB.Add;
2265 END IF;
2266 END IF;
2267
2268 END IF;
2269
2270 --Currency code
2271 IF x_currency_code IS NULL THEN
2272 IF p_usr_currency_code IS NOT NULL
2273 THEN
2274 x_currency_code := Get_Id('CURRENCY_NAME',
2275 p_usr_currency_code,
2276 l_get_id_return_status
2277 );
2278 IF x_currency_code IS NULL THEN
2279 FND_MESSAGE.SET_NAME('AR','AR_RAPI_USR_CURR_CODE_INVALID');
2280 FND_MSG_PUB.Add;
2281 p_return_status := FND_API.G_RET_STS_ERROR;
2282 END IF;
2283 ELSE
2284
2285 x_currency_code := arp_global.functional_currency;
2286 --Raise a warning message saying that currency was defaulted
2287 IF FND_MSG_PUB.Check_Msg_Level(G_MSG_SUCCESS)
2288 THEN
2289 FND_MESSAGE.SET_NAME('AR','AR_RAPI_FUNC_CURR_DEFAULTED');
2290 FND_MSG_PUB.Add;
2291 END IF;
2292
2293 END IF;
2294
2295 ELSE
2296 IF (p_usr_currency_code IS NOT NULL) THEN
2297
2298 --give a warning message to indicate that the usr_currency_code
2299 -- entered by the user have been ignored.
2300 IF FND_MSG_PUB.Check_Msg_Level(G_MSG_SUCCESS)
2301 THEN
2302 FND_MESSAGE.SET_NAME('AR','AR_RAPI_USR_CURR_CODE_IGN');
2303 FND_MSG_PUB.Add;
2304 END IF;
2305
2306 END IF;
2307 END IF;
2308
2309 -- for remit_to_address_id
2310
2311 arp_util.debug('before x_remit_to_address');
2312 IF x_remit_to_address_id is NULL THEN
2313 arp_util.debug('x_remit_to_address_id is null');
2314 IF p_cust_location_site_num is NULL
2315 THEN
2316 arp_util.debug('p_cust_location_site_num is null, hence defaulted');
2317 begin
2318 ARP_TRX_DEFAULTS_3.get_default_remit_to(l_dft_remit_to_address_id,
2319 l_dft_remit_to_address_rec);
2320 x_remit_to_address_id := l_dft_remit_to_address_id;
2321 exception
2322 when others then
2323 FND_MESSAGE.SET_NAME('AR','AR_DAPI_REMIT_ADDRESS_DFT_ERR');
2324 FND_MSG_PUB.Add;
2325 p_return_status := FND_API.G_RET_STS_ERROR;
2326 end;
2327 ELSE
2328 arp_util.debug('p_cust_location_site_num is not null, hence fetching');
2329 Begin
2330 ARP_TRX_DEFAULTS_3.get_remit_to_address(l_match_state,
2331 l_match_country,
2332 l_match_postal_code,
2333 p_cust_location_site_num,
2334 --customer location site number
2335 l_match_site_use_id,
2336 --bill_to_sit
2337 l_remit_to_address_id ,
2338 l_remit_to_address_rec);
2339 x_remit_to_address_id := l_remit_to_address_id;
2340 exception
2341 when others then
2342 FND_MESSAGE.SET_NAME('AR','AR_DAPI_CUST_LOC_SITE_NUM_INV');
2343 FND_MSG_PUB.Add;
2344 p_return_status := FND_API.G_RET_STS_ERROR;
2345 end ;
2346
2347 END IF;
2348 ELSE
2349 arp_util.debug('x_remit_to_address_id isnot null');
2350 IF p_cust_location_site_num is NOT NULL THEN
2351 --give a warning message to indicate that the
2352 --customer_number and customer_name
2353 --entered by the user have been ignored.
2354 IF FND_MSG_PUB.Check_Msg_Level(G_MSG_SUCCESS)
2355 THEN
2356 FND_MESSAGE.SET_NAME('AR','AR_DAPI_LOC_SITE_NUM_IGN'); --new message to be seeded
2357 FND_MSG_PUB.Add;
2358
2359 END IF;
2360 END IF;
2361 begin
2362 select address_id into l_dummy
2363 from ar_active_remit_to_addresses_v
2364 where address_id = x_remit_to_address_id;
2365 exception
2366 when no_data_found then
2367 arp_util.debug('no_data_found in is x_remit_to_address');
2368 FND_MESSAGE.SET_NAME('AR','AR_DAPI_REMIT_ADDR_ID_INVD');
2369 FND_MSG_PUB.Add;
2370 p_return_status := FND_API.G_RET_STS_ERROR;
2371 end;
2372
2373 END IF;
2374
2375 arp_util.debug('after x_remit_to_address');
2376
2377 IF (x_sold_to_customer_id IS NULL) THEN
2378
2379 IF ( p_sold_to_customer_name IS NOT NULL and
2380 p_sold_to_customer_number IS NULL )
2381 THEN
2382
2383 x_sold_to_customer_id := Get_Id('CUSTOMER_NAME',
2384 p_sold_to_customer_name,
2385 l_get_id_return_status);
2386 IF x_sold_to_customer_id IS NULL THEN
2387 FND_MESSAGE.SET_NAME('AR','AR_DAPI_SOLD_CUST_NAME_INVALID');
2388 FND_MSG_PUB.Add;
2389 p_return_status := FND_API.G_RET_STS_ERROR;
2390 END IF;
2391 ELSIF ( p_sold_to_customer_name IS NULL and
2392 p_sold_to_customer_number IS NOT NULL )
2393 THEN
2394 x_sold_to_customer_id := Get_Id( 'CUSTOMER_NUMBER',
2395 p_sold_to_customer_number,
2396 l_get_id_return_status);
2397 IF x_sold_to_customer_id IS NULL THEN
2398 FND_MESSAGE.SET_NAME('AR','AR_DAPI_SOLD_CUST_NUM_INVALID');
2399 FND_MSG_PUB.Add;
2400 p_return_status := FND_API.G_RET_STS_ERROR;
2401 END IF;
2402
2403 ELSIF( p_sold_to_customer_name IS NOT NULL and
2404 p_sold_to_customer_number IS NOT NULL )
2405 THEN
2406 x_sold_to_customer_id := Get_Cross_Validated_Id( 'CUSTOMER',
2407 p_sold_to_customer_number,
2408 p_sold_to_customer_name,
2409 l_get_x_val_return_status);
2410 IF x_sold_to_customer_id IS NULL THEN
2411 FND_MESSAGE.SET_NAME('AR','AR_DAPI_SOLD_CUST_COM_INVALID');
2412 FND_MSG_PUB.Add;
2413 p_return_status := FND_API.G_RET_STS_ERROR;
2414 END IF;
2415 ELSIF( p_sold_to_customer_name IS NULL and
2416 p_sold_to_customer_number IS NULL )
2417 THEN
2418
2419 --give a warning message to indicate that the sold_to_customer
2420 --is defaulted to bill to customer.
2421 x_sold_to_customer_id := x_bill_to_customer_id;
2422 IF FND_MSG_PUB.Check_Msg_Level(G_MSG_SUCCESS)
2423 THEN
2424 FND_MESSAGE.SET_NAME('AR','AR_DAPI_SOLD_CUST_DFT');
2425 FND_MSG_PUB.Add;
2426 END IF;
2427 END IF;
2428 ELSE
2429 IF (p_sold_to_customer_name IS NOT NULL) OR
2430 (p_sold_to_customer_number IS NOT NULL) THEN
2431 --give a warning message to indicate that the customer_number
2432 --and customer_name
2433 --entered by the user have been ignored.
2434 IF FND_MSG_PUB.Check_Msg_Level(G_MSG_SUCCESS)
2435 THEN
2436 FND_MESSAGE.SET_NAME('AR','AR_DAPI_SOLD_CUS_IGN');
2437 FND_MSG_PUB.Add;
2438
2439 END IF;
2440 END IF;
2441 /*--------------------------------+
2442 | |
2443 | Validate sold to Customer_id |
2444 | |
2445 +--------------------------------*/
2446 BEGIN
2447 SELECT cust.cust_account_id
2448 INTO l_customer_id
2449 FROM hz_cust_accounts cust,
2450 hz_customer_profiles cp,
2451 hz_parties party
2452 WHERE cust.cust_account_id = cp.cust_account_id (+) and
2453 cp.site_use_id is null and
2454 cust.cust_account_id = x_sold_to_customer_id and
2455 cust.status <> 'I' and
2456 cust.party_id = party.party_id;
2457 EXCEPTION
2458 WHEN NO_DATA_FOUND THEN
2459 p_return_status := FND_API.G_RET_STS_ERROR;
2460 FND_MESSAGE.SET_NAME('AR','AR_DAPI_SOLD_CUST_ID_INVALID');
2461 FND_MSG_PUB.Add;
2462
2463 WHEN OTHERS THEN
2464 arp_util.debug('EXCEPTION: sold to Customer_id() ');
2465 RAISE;
2466 END;
2467
2468 END IF;
2469
2470
2471 IF (x_paying_customer_id IS NULL)
2472 THEN
2473 --validate x_paying_customer_id
2474 x_paying_customer_id := x_bill_to_customer_id;
2475
2476 IF ( p_paying_customer_name IS NOT NULL and
2477 p_paying_customer_number IS NULL )
2478 THEN
2479
2480 x_paying_customer_id := Get_Id('CUSTOMER_NAME',
2481 p_paying_customer_name,
2482 l_get_id_return_status);
2483 IF x_paying_customer_id IS NULL THEN
2484 FND_MESSAGE.SET_NAME('AR','AR_DAPI_PAY_CUST_NAME_INVALID');
2485 FND_MSG_PUB.Add;
2486 p_return_status := FND_API.G_RET_STS_ERROR;
2487 END IF;
2488 ELSIF ( p_paying_customer_name IS NULL and
2489 p_paying_customer_number IS NOT NULL )
2490 THEN
2491 x_paying_customer_id := Get_Id( 'CUSTOMER_NUMBER',
2492 p_paying_customer_number,
2493 l_get_id_return_status);
2494 IF x_paying_customer_id IS NULL THEN
2495 FND_MESSAGE.SET_NAME('AR','AR_DAPI_PAY_CUST_NUM_INVALID');
2496 FND_MSG_PUB.Add;
2497 p_return_status := FND_API.G_RET_STS_ERROR;
2498 END IF;
2499
2500 ELSIF( p_paying_customer_name IS NOT NULL and
2501 p_paying_customer_number IS NOT NULL )
2502 THEN
2503 x_paying_customer_id := Get_Cross_Validated_Id( 'CUSTOMER',
2504 p_paying_customer_number,
2505 p_paying_customer_name,
2506 l_get_x_val_return_status);
2507 IF x_paying_customer_id IS NULL THEN
2508 FND_MESSAGE.SET_NAME('AR','AR_DAPI_PAY_CUST_COM_INVALID');
2509 FND_MSG_PUB.Add;
2510 p_return_status := FND_API.G_RET_STS_ERROR;
2511 END IF;
2512 ELSE
2513 x_paying_customer_id := x_bill_to_customer_id;
2514 END IF;
2515
2516 ELSE
2517 IF (p_paying_customer_name IS NOT NULL) OR
2518 (p_paying_customer_number IS NOT NULL) THEN
2519 --give a warning message to indicate that the customer_number
2520 --and customer_name
2521 --entered by the user have been ignored.
2522 IF FND_MSG_PUB.Check_Msg_Level(G_MSG_SUCCESS)
2523 THEN
2524 FND_MESSAGE.SET_NAME('AR','AR_DAPI_CUS_NAME_NUM_IGN');
2525 FND_MSG_PUB.Add;
2526 p_return_status := FND_API.G_RET_STS_ERROR;
2527 END IF;
2528 END IF;
2529 /*--------------------------------+
2530 | |
2531 | Validate paying Customer_id |
2532 | |
2533 +--------------------------------*/
2534 BEGIN
2535 SELECT cust.cust_account_id
2536 INTO l_customer_id
2537 FROM hz_cust_accounts cust,
2538 hz_customer_profiles cp,
2539 hz_parties party
2540 WHERE cust.cust_account_id = cp.cust_account_id (+) and
2541 cp.site_use_id is null and
2542 cust.cust_account_id = x_paying_customer_id and
2543 cust.status <> 'I' and
2544 cust.party_id = party.party_id;
2545 EXCEPTION
2546 WHEN NO_DATA_FOUND THEN
2547 p_return_status := FND_API.G_RET_STS_ERROR;
2548 FND_MESSAGE.SET_NAME('AR','AR_DAPI_PAY_CUST_ID_INVALID');
2549 FND_MSG_PUB.Add;
2550
2551 WHEN OTHERS THEN
2552 arp_util.debug('EXCEPTION: paying_Customer_id() ');
2553 RAISE;
2554 END;
2555 END IF;
2556
2557 --Customer site use id
2558 IF x_paying_customer_id IS NOT NULL THEN
2559
2560 --we need to validate p_paying_location here
2561
2562 IF x_paying_customer_site_use_id IS NULL THEN
2563 IF p_paying_location is not null then
2564 x_paying_customer_site_use_id :=
2565 Get_Site_Use_Id(x_paying_customer_id,
2566 nvl(p_paying_location,l_bill_to_location),
2567 'BILL_TO',
2568 'DRAWEE' ,l_site_use_return_status
2569 );
2570 arp_util.debug('x_paying_customer_site_use_id'||
2571 to_char(x_paying_customer_site_use_id));
2572 else
2573 --get default location of paying customer
2574 Begin
2575 select su.site_use_id
2576 into x_paying_customer_site_use_id
2577 from hz_cust_acct_sites acct_site,
2578 hz_party_sites party_site,
2579 hz_locations loc,
2580 hz_cust_site_uses su,
2581 fnd_territories_vl t
2582 where acct_site.cust_acct_site_id = su.cust_acct_site_id
2583 and acct_site.party_site_id = party_site.party_site_id
2584 and loc.location_id = party_site.location_id
2585 and loc.country = t.territory_code
2586 and acct_site.cust_account_id = x_paying_customer_id
2587 and su.site_use_id = nvl(null,su.site_use_id)
2588 and su.site_use_code = 'BILL_TO'
2589 and ( su.site_use_id = null
2590 or ( su.status = 'A'
2591 and acct_site.status = 'A'
2592 )
2593 )
2594 and su.primary_flag = 'Y';
2595
2596 exception
2597 when no_data_found then
2598 if x_paying_customer_id = x_bill_to_customer_id and
2599 l_bill_to_location is not null then
2600 x_paying_customer_site_use_id := Get_Site_Use_Id(x_paying_customer_id,
2601 l_bill_to_location,
2602 'BILL_TO',
2603 'DRAWEE' ,l_site_use_return_status);
2604 else
2605 x_paying_customer_site_use_id :=null;
2606 end if;
2607 end;
2608
2609 end if;
2610
2611
2612 if x_paying_customer_site_use_id is null then
2613 FND_MESSAGE.SET_NAME('AR','AR_DAPI_LOC_INV');
2614 FND_MSG_PUB.Add;
2615 p_return_status := FND_API.G_RET_STS_ERROR;
2616 end if;
2617 ELSE
2618 IF p_paying_location IS NOT NULL THEN
2619 --raise warning that
2620 null;
2621 END IF;
2622 END IF;
2623 END IF;
2624
2625 arp_util.debug('ar_deposit_lib_pvt.Default_deposit_ids() -' );
2626 EXCEPTION
2627 WHEN others THEN
2628 arp_util.debug('EXCEPTION: ar_deposit_lib_pvt.Default_deposit_ids() +',
2629 G_MSG_UERROR);
2630 raise;
2631 END Default_deposit_ids;
2632
2633
2634 PROCEDURE Get_deposit_Defaults(
2635 p_currency_code IN OUT NOCOPY
2636 ra_customer_trx.invoice_currency_code%TYPE,
2637 p_exchange_rate_type IN OUT NOCOPY
2638 ra_customer_trx.exchange_rate_type%TYPE,
2639 p_exchange_rate IN OUT NOCOPY ra_customer_trx.exchange_rate%TYPE,
2640 p_exchange_rate_date IN OUT NOCOPY ra_customer_trx.exchange_date%TYPE,
2641 p_start_date_commitmenmt IN OUT NOCOPY DATE,
2642 p_end_date_commitmenmt IN OUT NOCOPY DATE,
2643 p_amount IN OUT NOCOPY ar_cash_receipts.amount%TYPE,
2644 p_deposit_date IN OUT NOCOPY DATE,
2645 p_gl_date IN OUT NOCOPY DATE,
2646 p_bill_to_customer_id IN NUMBER,
2647 p_bill_to_site_use_id IN NUMBER,
2648 p_ship_to_customer_id IN NUMBER,
2649 p_ship_to_site_use_id IN NUMBER,
2650 p_salesrep_id OUT NOCOPY NUMBER,
2651 p_bill_to_contact_id OUT NOCOPY NUMBER,
2652 p_called_from IN VARCHAR2,
2653 p_return_status OUT NOCOPY VARCHAR2
2654 )
2655 IS
2656
2657 l_def_curr_return_status VARCHAR2(1);
2658 l_def_rm_return_status VARCHAR2(1);
2659 l_def_gl_dt_return_status VARCHAR2(1);
2660 l_def_comm_dt_return_status VARCHAR2(1);
2661 BEGIN
2662 arp_util.debug('ar_deposit_lib_pvt.Get_deposit_Defaults() +' );
2663 p_return_status := FND_API.G_RET_STS_SUCCESS;
2664 -- default the receipt date if NULL
2665 IF (p_deposit_date IS NULL)
2666 THEN
2667 Select trunc(sysdate)
2668 into p_deposit_date
2669 from dual;
2670 END IF;
2671
2672 -- default the gl_date
2673 IF p_gl_date IS NULL THEN
2674 Default_gl_date(p_deposit_date,
2675 p_gl_date,
2676 l_def_gl_dt_return_status);
2677 arp_util.debug('l_default_gl_date_return_status : '||
2678 l_def_gl_dt_return_status);
2679 END IF;
2680
2681
2682
2683 Default_commitment_Date(p_deposit_date,
2684 p_start_date_commitmenmt,
2685 p_end_date_commitmenmt,
2686 l_def_comm_dt_return_status);
2687
2688 -- Default the Currency parameters
2689 Default_Currency_info(p_currency_code,
2690 p_deposit_date,
2691 p_exchange_rate_date,
2692 p_exchange_rate_type,
2693 p_exchange_rate,
2694 l_def_curr_return_status
2695 );
2696
2697 --Set the precision of the receipt amount as per currency
2698 IF p_amount is NOT NULL THEN
2699 p_amount := arp_util.CurrRound( p_amount,
2700 p_currency_code
2701 );
2702 END IF;
2703
2704
2705 get_salesrep_defaults(p_salesrep_id, p_bill_to_customer_id,
2706 p_bill_to_site_use_id,p_ship_to_customer_id ,
2707 p_ship_to_site_use_id,p_deposit_date);
2708
2709
2710 default_bill_to_contact(p_bill_to_contact_id ,
2711 p_bill_to_customer_id,
2712 p_bill_to_site_use_id );
2713
2714
2715 IF l_def_rm_return_status <> FND_API.G_RET_STS_SUCCESS OR
2716 l_def_gl_dt_return_status <> FND_API.G_RET_STS_SUCCESS OR
2717 l_def_comm_dt_return_status <> FND_API.G_RET_STS_SUCCESS OR
2718 l_def_curr_return_status <> FND_API.G_RET_STS_SUCCESS
2719 THEN
2720 p_return_status := FND_API.G_RET_STS_ERROR;
2721 END IF;
2722 arp_util.debug('************Cash Defaults********************');
2723 arp_util.debug('p_gl_date : '||to_char(p_gl_date,'DD-MON-YYYY'));
2724 arp_util.debug('p_deposit_date : '||
2725 to_char(p_deposit_date,'DD-MON-YYYY'));
2726 arp_util.debug('p_currency_code : '||p_currency_code);
2727 arp_util.debug('p_exchange_rate_date: '||
2728 to_char(p_exchange_rate_date,'DD-MON-YYYY'));
2729 arp_util.debug('p_exchange_rate_type: '||p_exchange_rate_type);
2730 arp_util.debug('p_exchange_rate : '||to_char(p_exchange_rate));
2731 arp_util.debug('ar_deposit_lib_pvt.Get_deposit_Defaults() -' );
2732 END Get_deposit_Defaults;
2733
2734
2735 PROCEDURE get_doc_seq(p_application_id IN NUMBER,
2736 p_document_name IN VARCHAR2,
2737 p_sob_id IN NUMBER,
2738 p_met_code IN CHAR,
2739 p_trx_date IN DATE,
2740 p_doc_sequence_value IN OUT NOCOPY NUMBER,
2741 p_doc_sequence_id OUT NOCOPY NUMBER,
2742 p_return_status OUT NOCOPY VARCHAR2
2743 ) AS
2744 l_doc_seq_ret_stat NUMBER;
2745 l_doc_sequence_name VARCHAR2(50);
2746 l_doc_sequence_type VARCHAR2(50);
2747 l_doc_sequence_value NUMBER;
2748 l_db_sequence_name VARCHAR2(50);
2749 l_seq_ass_id NUMBER;
2750 l_prd_tab_name VARCHAR2(50);
2751 l_aud_tab_name VARCHAR2(50);
2752 l_msg_flag VARCHAR2(1);
2753 BEGIN
2754 arp_util.debug('ar_deposit_lib_pvt.get_doc_seq() +' );
2755 arp_util.debug('SEQ : '||NVL( pg_profile_doc_seq, 'N'));
2756 arp_util.debug('p_document_name :'||p_document_name);
2757 arp_util.debug('p_application_id :'||to_char(p_application_id));
2758 arp_util.debug('p_sob_id :'||to_char(p_sob_id));
2759 p_return_status := FND_API.G_RET_STS_SUCCESS;
2760 IF ( NVL( pg_profile_doc_seq, 'N') <> 'N' )
2761 THEN
2762 BEGIN
2763 /*------------------------------+
2764 | Get the document sequence. |
2765 +------------------------------*/
2766 l_doc_seq_ret_stat:=
2767 fnd_seqnum.get_seq_info (
2768 p_application_id,
2769 p_document_name,
2770 p_sob_id,
2771 p_met_code,
2772 trunc(p_trx_date),
2773 p_doc_sequence_id,
2774 l_doc_sequence_type,
2775 l_doc_sequence_name,
2776 l_db_sequence_name,
2777 l_seq_ass_id,
2778 l_prd_tab_name,
2779 l_aud_tab_name,
2780 l_msg_flag,
2781 'Y',
2782 'Y');
2783 arp_util.debug('Doc sequence return status :'||
2784 to_char(nvl(l_doc_seq_ret_stat,-99)));
2785 arp_util.debug('l_doc_sequence_name :'||l_doc_sequence_name);
2786 arp_util.debug('l_doc_sequence_id :'||
2787 to_char(nvl(p_doc_sequence_id,-99)));
2788
2789 IF l_doc_seq_ret_stat = -8 THEN
2790 --this is the case of Always Used
2791 arp_util.debug('The doc sequence does not exist
2792 for the current document');
2793 p_return_status := FND_API.G_RET_STS_ERROR;
2794 --Error message
2795 FND_MESSAGE.Set_Name( 'AR','AR_RAPI_DOC_SEQ_NOT_EXIST_A');
2796 FND_MSG_PUB.Add;
2797 ELSIF l_doc_seq_ret_stat = -2 THEN
2798 --this is the case of Partially Used
2799 arp_util.debug('The doc sequence does not exist
2800 for the current document');
2801 --Warning
2802 IF FND_MSG_PUB.Check_Msg_Level(G_MSG_SUCCESS)
2803 THEN
2804 FND_MESSAGE.SET_NAME('AR','AR_RAPI_DOC_SEQ_NOT_EXIST_P');
2805 FND_MSG_PUB.Add;
2806 END IF;
2807 END IF;
2808
2809 IF ( l_doc_sequence_name IS NOT NULL
2810 AND p_doc_sequence_id IS NOT NULL)
2811 THEN
2812 /*------------------------------------+
2813 | Automatic Document Numbering case |
2814 +------------------------------------*/
2815 arp_util.debug('Automatic Document Numbering case ');
2816 l_doc_seq_ret_stat :=
2817 fnd_seqnum.get_seq_val (
2818 p_application_id,
2819 p_document_name,
2820 p_sob_id,
2821 p_met_code,
2822 TRUNC(p_trx_date),
2823 l_doc_sequence_value,
2824 p_doc_sequence_id);
2825 IF p_doc_sequence_value IS NOT NULL THEN
2826 --raise an error message because the user is
2827 --not supposed to pass
2828 --in a value for the document sequence number in this case.
2829 p_return_status := FND_API.G_RET_STS_ERROR;
2830 FND_MESSAGE.Set_Name('AR', 'AR_RAPI_DOC_SEQ_AUTOMATIC');
2831 FND_MSG_PUB.Add;
2832 END IF;
2833 p_doc_sequence_value := l_doc_sequence_value;
2834 arp_util.debug('l_doc_sequence_value :'||
2835 to_char(nvl(p_doc_sequence_value,-99)));
2836 ELSIF (
2837 p_doc_sequence_id IS NOT NULL
2838 AND p_doc_sequence_value IS NOT NULL
2839 )
2840 THEN
2841 /*-------------------------------------+
2842 | Manual Document Numbering case |
2843 | with the document value specified. |
2844 | Use the specified value. |
2845 +-------------------------------------*/
2846
2847 NULL;
2848
2849 ELSIF (
2850 p_doc_sequence_id IS NOT NULL
2851 AND p_doc_sequence_value IS NULL
2852 )
2853 THEN
2854 /*-----------------------------------------+
2855 | Manual Document Numbering case |
2856 | with the document value not specified. |
2857 | Generate a document value mandatory |
2858 | error. |
2859 +-----------------------------------------*/
2860 IF NVL(pg_profile_doc_seq,'N') = 'A' THEN
2861 p_return_status := FND_API.G_RET_STS_ERROR;
2862 FND_MESSAGE.Set_Name('AR',
2863 'AR_RAPI_DOC_SEQ_VALUE_NULL_A');
2864 FND_MESSAGE.Set_Token('SEQUENCE',
2865 l_doc_sequence_name);
2866 FND_MSG_PUB.Add;
2867 ELSIF NVL(pg_profile_doc_seq,'N') = 'P' THEN
2868 --Warning
2869 IF FND_MSG_PUB.Check_Msg_Level(G_MSG_SUCCESS)
2870 THEN
2871 FND_MESSAGE.SET_NAME('AR',
2872 'AR_RAPI_DOC_SEQ_VALUE_NULL_P');
2873 FND_MSG_PUB.Add;
2874 END IF;
2875 END IF;
2876
2877
2878 END IF;
2879
2880 EXCEPTION
2881 WHEN NO_DATA_FOUND THEN
2882 /*------------------------------------------+
2883 | No document assignment was found. |
2884 | Generate an error if document numbering |
2885 | is mandatory. |
2886 +------------------------------------------*/
2887 arp_util.debug('no_data_found raised');
2888 IF (pg_profile_doc_seq = 'A' ) THEN
2889 p_return_status := FND_API.G_RET_STS_ERROR;
2890 FND_MESSAGE.Set_Name( 'FND','UNIQUE-ALWAYS USED');
2891 FND_MSG_PUB.Add;
2892 ELSE
2893 p_doc_sequence_id := NULL;
2894 p_doc_sequence_value := NULL;
2895 END IF;
2896
2897 WHEN OTHERS THEN
2898 arp_util.debug('Unhandled exception in doc sequence
2899 assignment');
2900 raise;
2901
2902 END;
2903
2904 END IF;
2905 arp_util.debug('ar_deposit_lib_pvt.get_doc_seq() -' );
2906 END get_doc_seq;
2907
2908 PROCEDURE Validate_Desc_Flexfield(
2909 p_desc_flex_rec IN OUT NOCOPY ar_deposit_api_pub.attr_rec_type,
2910 p_desc_flex_name IN VARCHAR2,
2911 p_return_status IN OUT NOCOPY varchar2
2912 ) IS
2913
2914 l_flex_name fnd_descriptive_flexs.descriptive_flexfield_name%type;
2915 l_count NUMBER;
2916 l_col_name VARCHAR2(50);
2917 l_flex_exists VARCHAR2(1);
2918 CURSOR desc_flex_exists IS
2919 SELECT 'Y'
2920 FROM fnd_descriptive_flexs
2921 WHERE application_id = 222 and
2922 descriptive_flexfield_name = p_desc_flex_name;
2923 /* Start FP Bug 5467022 */
2924 /* bug 5467021 created get_flex_title to get title of flex field*/
2925 function get_flex_title ( p_flex_name in varchar2) return varchar2 is
2926 l_flex_title FND_DESCRIPTIVE_FLEXS_VL.title%type;
2927 begin
2928 select title
2929 into l_flex_title
2930 from FND_DESCRIPTIVE_FLEXS_VL
2931 where DESCRIPTIVE_FLEXFIELD_NAME=p_flex_name;
2932
2933 return l_flex_title;
2934 exception
2935 when others then
2936 arp_util.debug('Exception : Others in get_flex_title function'|| sqlerrm);
2937 return p_flex_name;
2938 end get_flex_title;
2939 /* End FP Bug 5467022 SPDIXIT */
2940 BEGIN
2941 arp_util.debug('ar_deposit_lib_pvt.Validate_Desc_Flexfield() +' );
2942 p_return_status := FND_API.G_RET_STS_SUCCESS;
2943
2944 OPEN desc_flex_exists;
2945 FETCH desc_flex_exists INTO l_flex_exists;
2946 IF desc_flex_exists%NOTFOUND THEN
2947 CLOSE desc_flex_exists;
2948 p_return_status := FND_API.G_RET_STS_ERROR;
2949 return;
2950 END IF;
2951 CLOSE desc_flex_exists;
2952 /* Start FP Bug 5467022 */
2953 arp_util.debug('attribute_category : '||p_desc_flex_rec.attribute_category);
2954 arp_util.debug('attribute1 : '||p_desc_flex_rec.attribute1);
2955 arp_util.debug('attribute2 : '||p_desc_flex_rec.attribute2);
2956 arp_util.debug('attribute3 : '||p_desc_flex_rec.attribute3);
2957 arp_util.debug('attribute4 : '||p_desc_flex_rec.attribute4);
2958 arp_util.debug('attribute5 : '||p_desc_flex_rec.attribute5);
2959 arp_util.debug('attribute6 : '||p_desc_flex_rec.attribute6);
2960 arp_util.debug('attribute7 : '||p_desc_flex_rec.attribute7);
2961 arp_util.debug('attribute8 : '||p_desc_flex_rec.attribute8);
2962 arp_util.debug('attribute9 : '||p_desc_flex_rec.attribute9);
2963 arp_util.debug('attribute10 : '||p_desc_flex_rec.attribute10);
2964 arp_util.debug('attribute11 : '||p_desc_flex_rec.attribute11);
2965 arp_util.debug('attribute12 : '||p_desc_flex_rec.attribute12);
2966 arp_util.debug('attribute13 : '||p_desc_flex_rec.attribute13);
2967 arp_util.debug('attribute14 : '||p_desc_flex_rec.attribute14);
2968 arp_util.debug('attribute15 : '||p_desc_flex_rec.attribute15);
2969 arp_util.debug('ar_deposit_lib_pvt.Validate_Desc_Flexfield() -' );
2970
2971 IF p_desc_flex_name = 'RA_CUSTOMER_TRX' THEN /* bug 5467021 if-else condition added and get_flex_title called in set_token*/
2972
2973 fnd_flex_descval.set_context_value(p_desc_flex_rec.attribute_category);
2974 fnd_flex_descval.set_column_value('ATTRIBUTE1', p_desc_flex_rec.attribute1);
2975 fnd_flex_descval.set_column_value('ATTRIBUTE2', p_desc_flex_rec.attribute2);
2976 fnd_flex_descval.set_column_value('ATTRIBUTE3', p_desc_flex_rec.attribute3);
2977 fnd_flex_descval.set_column_value('ATTRIBUTE4', p_desc_flex_rec.attribute4);
2978 fnd_flex_descval.set_column_value('ATTRIBUTE5', p_desc_flex_rec.attribute5);
2979 fnd_flex_descval.set_column_value('ATTRIBUTE6', p_desc_flex_rec.attribute6);
2980 fnd_flex_descval.set_column_value('ATTRIBUTE7', p_desc_flex_rec.attribute7);
2981 fnd_flex_descval.set_column_value('ATTRIBUTE8', p_desc_flex_rec.attribute8);
2982 fnd_flex_descval.set_column_value('ATTRIBUTE9', p_desc_flex_rec.attribute9);
2983 fnd_flex_descval.set_column_value('ATTRIBUTE10', p_desc_flex_rec.attribute10);
2984 fnd_flex_descval.set_column_value('ATTRIBUTE11',p_desc_flex_rec.attribute11);
2985 fnd_flex_descval.set_column_value('ATTRIBUTE12', p_desc_flex_rec.attribute12);
2986 fnd_flex_descval.set_column_value('ATTRIBUTE13', p_desc_flex_rec.attribute13);
2987 fnd_flex_descval.set_column_value('ATTRIBUTE14', p_desc_flex_rec.attribute14);
2988 fnd_flex_descval.set_column_value('ATTRIBUTE15', p_desc_flex_rec.attribute15);
2989 IF ( NOT fnd_flex_descval.validate_desccols('AR',p_desc_flex_name,'I') ) /*Bug 3291481*/
2990 THEN
2991
2992 FND_MESSAGE.SET_NAME('AR', 'AR_RAPI_DESC_FLEX_INVALID');
2993 -- FND_MESSAGE.SET_TOKEN('DFF_NAME',p_desc_flex_name);
2994 FND_MESSAGE.SET_TOKEN('DFF_NAME',p_desc_flex_name||' - Flex Field Name : "' ||get_flex_title('RA_CUSTOMER_TRX')||'"');
2995 FND_MSG_PUB.ADD ;
2996 p_return_status := FND_API.G_RET_STS_ERROR;
2997 END IF;
2998
2999 l_count := fnd_flex_descval.segment_count;
3000
3001 FOR i in 1..l_count LOOP
3002 l_col_name := fnd_flex_descval.segment_column_name(i);
3003
3004 /*Bug 3291481, replaced fnd_flex_descval.segment_value with fnd_flex_descval.segment_id*/
3005
3006 IF l_col_name = 'ATTRIBUTE1' THEN
3007 p_desc_flex_rec.attribute1 := fnd_flex_descval.segment_id(i);
3008 ELSIF l_col_name = 'ATTRIBUTE_CATEGORY' THEN
3009 p_desc_flex_rec.attribute_category := fnd_flex_descval.segment_id(i);
3010 ELSIF l_col_name = 'ATTRIBUTE2' THEN
3011 p_desc_flex_rec.attribute2 := fnd_flex_descval.segment_id(i);
3012 ELSIF l_col_name = 'ATTRIBUTE3' THEN
3013 p_desc_flex_rec.attribute3 := fnd_flex_descval.segment_id(i);
3014 ELSIF l_col_name = 'ATTRIBUTE4' THEN
3015 p_desc_flex_rec.attribute4 := fnd_flex_descval.segment_id(i);
3016 ELSIF l_col_name = 'ATTRIBUTE5' THEN
3017 p_desc_flex_rec.attribute5 := fnd_flex_descval.segment_id(i);
3018 ELSIF l_col_name = 'ATTRIBUTE6' THEN
3019 p_desc_flex_rec.attribute6 := fnd_flex_descval.segment_id(i);
3020 ELSIF l_col_name = 'ATTRIBUTE7' THEN
3021 p_desc_flex_rec.attribute7 := fnd_flex_descval.segment_id(i);
3022 ELSIF l_col_name = 'ATTRIBUTE8' THEN
3023 p_desc_flex_rec.attribute8 := fnd_flex_descval.segment_id(i);
3024 ELSIF l_col_name = 'ATTRIBUTE9' THEN
3025 p_desc_flex_rec.attribute9 := fnd_flex_descval.segment_id(i);
3026 ELSIF l_col_name = 'ATTRIBUTE10' THEN
3027 p_desc_flex_rec.attribute10 := fnd_flex_descval.segment_id(i);
3028 ELSIF l_col_name = 'ATTRIBUTE11' THEN
3029 p_desc_flex_rec.attribute11 := fnd_flex_descval.segment_id(i);
3030 ELSIF l_col_name = 'ATTRIBUTE12' THEN
3031 p_desc_flex_rec.attribute12 := fnd_flex_descval.segment_id(i);
3032 ELSIF l_col_name = 'ATTRIBUTE13' THEN
3033 p_desc_flex_rec.attribute13 := fnd_flex_descval.segment_id(i);
3034 ELSIF l_col_name = 'ATTRIBUTE14' THEN
3035 p_desc_flex_rec.attribute14 := fnd_flex_descval.segment_id(i);
3036 ELSIF l_col_name = 'ATTRIBUTE15' THEN
3037 p_desc_flex_rec.attribute15 := fnd_flex_descval.segment_id(i);
3038 END IF;
3039
3040 IF i > l_count THEN
3041 EXIT;
3042 END IF;
3043 END LOOP;
3044 /* Below coded added for bug 5467021. Assigning appropriate columns values based on flex field name before call to fnd api for validation */
3045
3046 ELSIF p_desc_flex_name = 'RA_INTERFACE_HEADER' THEN
3047 fnd_flex_descval.set_context_value(p_desc_flex_rec.attribute_category);
3048
3049 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE1',
3050 p_desc_flex_rec.attribute1);
3051 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE2',
3052 p_desc_flex_rec.attribute2);
3053 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE3',
3054 p_desc_flex_rec.attribute3);
3055 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE4',
3056 p_desc_flex_rec.attribute4);
3057 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE5',
3058 p_desc_flex_rec.attribute5);
3059 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE6',
3060 p_desc_flex_rec.attribute6);
3061 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE7',
3062 p_desc_flex_rec.attribute7);
3063 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE8',
3064 p_desc_flex_rec.attribute8);
3065 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE9',
3066 p_desc_flex_rec.attribute9);
3067 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE10',
3068 p_desc_flex_rec.attribute10);
3069 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE11',
3070 p_desc_flex_rec.attribute11);
3071 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE12',
3072 p_desc_flex_rec.attribute12);
3073 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE13',
3074 p_desc_flex_rec.attribute13);
3075 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE14',
3076 p_desc_flex_rec.attribute14);
3077 fnd_flex_descval.set_column_value('INTERFACE_HEADER_ATTRIBUTE15',
3078 p_desc_flex_rec.attribute15);
3079
3080
3081 IF ( NOT fnd_flex_descval.validate_desccols('AR',p_desc_flex_name,'I') )
3082 THEN
3083 FND_MESSAGE.SET_NAME('AR', 'AR_RAPI_DESC_FLEX_INVALID');
3084 FND_MESSAGE.SET_TOKEN('DFF_NAME',p_desc_flex_name||' - Flex Field Name : "' ||get_flex_title('RA_INTERFACE_HEADER')||'"');
3085 FND_MSG_PUB.ADD ;
3086 p_return_status := FND_API.G_RET_STS_ERROR;
3087 END IF;
3088
3089 l_count := fnd_flex_descval.segment_count;
3090
3091
3092 FOR i in 1..l_count LOOP
3093 l_col_name := fnd_flex_descval.segment_column_name(i);
3094
3095 IF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE1' THEN
3096 p_desc_flex_rec.attribute1 := fnd_flex_descval.segment_id(i);
3097 ELSIF l_col_name = 'INTERFACE_HEADER_CONTEXT' THEN
3098 p_desc_flex_rec.attribute_category := fnd_flex_descval.segment_id(i);
3099 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE2' THEN
3100 p_desc_flex_rec.attribute2 := fnd_flex_descval.segment_id(i);
3101 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE3' THEN
3102 p_desc_flex_rec.attribute3 := fnd_flex_descval.segment_id(i);
3103 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE4' THEN
3104 p_desc_flex_rec.attribute4 := fnd_flex_descval.segment_id(i);
3105 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE5' THEN
3106 p_desc_flex_rec.attribute5 := fnd_flex_descval.segment_id(i);
3107 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE6' THEN
3108 p_desc_flex_rec.attribute6 := fnd_flex_descval.segment_id(i);
3109 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE7' THEN
3110 p_desc_flex_rec.attribute7 := fnd_flex_descval.segment_id(i);
3111 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE8' THEN
3112 p_desc_flex_rec.attribute8 := fnd_flex_descval.segment_id(i);
3113 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE9' THEN
3114 p_desc_flex_rec.attribute9 := fnd_flex_descval.segment_id(i);
3115 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE10' THEN
3116 p_desc_flex_rec.attribute10 := fnd_flex_descval.segment_id(i);
3117 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE11' THEN
3118 p_desc_flex_rec.attribute11 := fnd_flex_descval.segment_id(i);
3119 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE12' THEN
3120 p_desc_flex_rec.attribute12 := fnd_flex_descval.segment_id(i);
3121 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE13' THEN
3122 p_desc_flex_rec.attribute13 := fnd_flex_descval.segment_id(i);
3123 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE14' THEN
3124 p_desc_flex_rec.attribute14 := fnd_flex_descval.segment_id(i);
3125 ELSIF l_col_name = 'INTERFACE_HEADER_ATTRIBUTE15' THEN
3126 p_desc_flex_rec.attribute15 := fnd_flex_descval.segment_id(i);
3127 END IF;
3128
3129 IF i > l_count THEN
3130 EXIT;
3131 END IF;
3132 END LOOP;
3133
3134 ELSIF p_desc_flex_name = 'RA_INTERFACE_LINES' THEN
3135 fnd_flex_descval.set_context_value(p_desc_flex_rec.attribute_category);
3136
3137 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE1',
3138 p_desc_flex_rec.attribute1);
3139 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE2',
3140 p_desc_flex_rec.attribute2);
3141 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE3',
3142 p_desc_flex_rec.attribute3);
3143 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE4',
3144 p_desc_flex_rec.attribute4);
3145 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE5',
3146 p_desc_flex_rec.attribute5);
3147 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE6',
3148 p_desc_flex_rec.attribute6);
3149 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE7',
3150 p_desc_flex_rec.attribute7);
3151 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE8',
3152 p_desc_flex_rec.attribute8);
3153 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE9',
3154 p_desc_flex_rec.attribute9);
3155 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE10',
3156 p_desc_flex_rec.attribute10);
3157 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE11',
3158 p_desc_flex_rec.attribute11);
3159 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE12',
3160 p_desc_flex_rec.attribute12);
3161 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE13',
3162 p_desc_flex_rec.attribute13);
3163 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE14',
3164 p_desc_flex_rec.attribute14);
3165 fnd_flex_descval.set_column_value('INTERFACE_LINE_ATTRIBUTE15',
3166 p_desc_flex_rec.attribute15);
3167
3168
3169 IF ( NOT fnd_flex_descval.validate_desccols('AR',p_desc_flex_name,'I') )
3170 THEN
3171 FND_MESSAGE.SET_NAME('AR', 'AR_RAPI_DESC_FLEX_INVALID');
3172 FND_MESSAGE.SET_TOKEN('DFF_NAME',p_desc_flex_name||' - Flex Field Name : "' ||get_flex_title('RA_INTERFACE_LINES')||'"');
3173 FND_MSG_PUB.ADD ;
3174 p_return_status := FND_API.G_RET_STS_ERROR;
3175 END IF;
3176
3177 l_count := fnd_flex_descval.segment_count;
3178
3179
3180 FOR i in 1..l_count LOOP
3181 l_col_name := fnd_flex_descval.segment_column_name(i);
3182
3183 IF l_col_name = 'INTERFACE_LINE_ATTRIBUTE1' THEN
3184 p_desc_flex_rec.attribute1 := fnd_flex_descval.segment_id(i);
3185 ELSIF l_col_name = 'INTERFACE_LINE_CONTEXT' THEN
3186 p_desc_flex_rec.attribute_category := fnd_flex_descval.segment_id(i);
3187 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE2' THEN
3188 p_desc_flex_rec.attribute2 := fnd_flex_descval.segment_id(i);
3189 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE3' THEN
3190 p_desc_flex_rec.attribute3 := fnd_flex_descval.segment_id(i);
3191 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE4' THEN
3192 p_desc_flex_rec.attribute4 := fnd_flex_descval.segment_id(i);
3193 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE5' THEN
3194 p_desc_flex_rec.attribute5 := fnd_flex_descval.segment_id(i);
3195 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE6' THEN
3196 p_desc_flex_rec.attribute6 := fnd_flex_descval.segment_id(i);
3197 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE7' THEN
3198 p_desc_flex_rec.attribute7 := fnd_flex_descval.segment_id(i);
3199 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE8' THEN
3200 p_desc_flex_rec.attribute8 := fnd_flex_descval.segment_id(i);
3201 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE9' THEN
3202 p_desc_flex_rec.attribute9 := fnd_flex_descval.segment_id(i);
3203 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE10' THEN
3204 p_desc_flex_rec.attribute10 := fnd_flex_descval.segment_id(i);
3205 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE11' THEN
3206 p_desc_flex_rec.attribute11 := fnd_flex_descval.segment_id(i);
3207 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE12' THEN
3208 p_desc_flex_rec.attribute12 := fnd_flex_descval.segment_id(i);
3209 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE13' THEN
3210 p_desc_flex_rec.attribute13 := fnd_flex_descval.segment_id(i);
3211 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE14' THEN
3212 p_desc_flex_rec.attribute14 := fnd_flex_descval.segment_id(i);
3213 ELSIF l_col_name = 'INTERFACE_LINE_ATTRIBUTE15' THEN
3214 p_desc_flex_rec.attribute15 := fnd_flex_descval.segment_id(i);
3215 END IF;
3216
3217 IF i > l_count THEN
3218 EXIT;
3219 END IF;
3220 END LOOP;
3221
3222
3223 END IF;/* End p_desc_flex_name= */
3224 /* bug 5467021 ends */
3225
3226 arp_util.debug('attribute_category : '||p_desc_flex_rec.attribute_category);
3227 arp_util.debug('attribute1 : '||p_desc_flex_rec.attribute1);
3228 arp_util.debug('attribute2 : '||p_desc_flex_rec.attribute2);
3229 arp_util.debug('attribute3 : '||p_desc_flex_rec.attribute3);
3230 arp_util.debug('attribute4 : '||p_desc_flex_rec.attribute4);
3231 arp_util.debug('attribute5 : '||p_desc_flex_rec.attribute5);
3232 arp_util.debug('attribute6 : '||p_desc_flex_rec.attribute6);
3233 arp_util.debug('attribute7 : '||p_desc_flex_rec.attribute7);
3234 arp_util.debug('attribute8 : '||p_desc_flex_rec.attribute8);
3235 arp_util.debug('attribute9 : '||p_desc_flex_rec.attribute9);
3236 arp_util.debug('attribute10 : '||p_desc_flex_rec.attribute10);
3237 arp_util.debug('attribute11 : '||p_desc_flex_rec.attribute11);
3238 arp_util.debug('attribute12 : '||p_desc_flex_rec.attribute12);
3239 arp_util.debug('attribute13 : '||p_desc_flex_rec.attribute13);
3240 arp_util.debug('attribute14 : '||p_desc_flex_rec.attribute14);
3241 arp_util.debug('attribute15 : '||p_desc_flex_rec.attribute15);
3242 arp_util.debug('ar_deposit_lib_pvt.Validate_Desc_Flexfield() -' );
3243 END Validate_Desc_Flexfield;
3244
3245 END ar_deposit_lib_pvt;
3246
[Home] [Help]