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