[Home] [Help]
PACKAGE BODY: APPS.HZP_CUST_PKG
Source
1 PACKAGE BODY hzp_cust_pkg AS
2 /* $Header: ARHCUSTB.pls 120.7 2005/06/16 21:10:13 jhuang ship $*/
3 --
4 -- PROCEDURE
5 -- check_unique_customer_name
6 --
7 -- DESCRIPTION
8 -- This procedure determins if an address has a site use of a particular
9 -- Type.
10 --
11 -- SCOPE - PUBLIC
12 --
13 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
14 --
15 -- ARGUMENTS : IN:
16 -- - p_rowid - rowid of row
17 -- - p_customer_name
18 --
19 -- OUT:
20 -- - p_warning_flag - Tells calling routine that there
21 -- is a non fatal warning on the message stack
22 --
23 -- RETURNS null
24 --
25 -- NOTES
26 --
27 --
28 PROCEDURE check_unique_customer_name (p_rowid IN VARCHAR2,
29 p_customer_name IN VARCHAR2,
30 p_warning_flag IN OUT NOCOPY VARCHAR2) IS
31 dummy NUMBER;
32 BEGIN
33
34 -- as per Sai... since this code was checking for account_name
35 -- and not party_name, The code was modified to use party_name and
36 -- then commented out since the initial logic was flawed. This
37 -- appears only in ARXCUDCI and even those calls are commented out.
38
39 NULL;
40 /******************************************************************
41 select 1
42 into dummy
43 from dual
44 where not exists ( select 1
45 from hz_parties
46 where party_name = p_customer_name
47 and ( ( p_rowid is null ) or (rowid <> p_rowid))
48 );
49 ****************************************************************/
50 EXCEPTION
51 WHEN NO_DATA_FOUND THEN
52 fnd_message.set_name ('AR','AR_CUST_NAME_ALREADY_EXISTS');
53 p_warning_flag := 'W';
54 END check_unique_customer_name;
55 --
56 --
57 --
58 --
59 --
60 -- PROCEDURE
61 -- check_unique_customer_number
62 --
63 -- DESCRIPTION
64 -- RRaise error if customer number is duplicate
65 --
66 -- SCOPE - PUBLIC
67 --
68 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
69 --
70 -- ARGUMENTS : IN:
71 -- - p_rowid - rowid of row
72 -- - p_customer_number
73 --
74 -- OUT:
75 --
76 -- RETURNS null
77 --
78 -- NOTES
79 --
80 --
81 PROCEDURE check_unique_customer_number(p_rowid IN VARCHAR2,
82 p_customer_number IN VARCHAR2) IS
83 dummy NUMBER;
84 BEGIN
85
86 select 1
87 into dummy
88 from dual
89 where not exists ( select 1
90 from hz_cust_accounts
91 where account_number = p_customer_number
92 and ( ( p_rowid is null ) or (rowid <> p_rowid))
93 );
94
95 EXCEPTION
96 WHEN NO_DATA_FOUND THEN
97 fnd_message.set_name ('AR','AR_CUST_NUMBER_EXISTS');
98 app_exception.raise_exception;
99 END check_unique_customer_number;
100 --
101 --
102 --
103 --
104 -- PROCEDURE
105 -- check_unique_party_number
106 --
107 -- DESCRIPTION
108 -- RRaise error if party number is duplicate
109 --
110 -- SCOPE - PUBLIC
111 --
112 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
113 --
114 -- ARGUMENTS : IN:
115 -- - p_rowid - rowid of row
116 -- - p_party_number
117 --
118 -- OUT:
119 --
120 -- RETURNS null
121 --
122 -- NOTES
123 --
124 --
125 procedure check_unique_party_number(p_rowid in varchar2,
126 p_party_number in varchar2
127 ) is
128 dummy number;
129 begin
130
131 select 1
132 into dummy
133 from dual
134 where not exists ( select 1
135 from hz_parties
136 where party_number = p_party_number
137 and ( ( p_rowid is null ) or (rowid <> p_rowid))
138 );
139
140 exception
141 when NO_DATA_FOUND then
142 fnd_message.set_name ('AR','AR_PARTY_NUMBER_EXISTS');
143 app_exception.raise_exception;
144 end check_unique_party_number;
145 --
146 --
147 --
148 -- PROCEDURE
149 -- check_unique_orig_system_ref
150 --
151 -- DESCRIPTION
152 -- Raise error if orig_system_referenc is duplicate
153 --
154 -- SCOPE - PUBLIC
155 --
156 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
157 --
158 -- ARGUMENTS : IN:
159 -- - p_rowid - rowid of row
160 -- - p_orig_system_reference
161 --
162 -- OUT:
163 --
164 -- RETURNS null
165 --
166 -- NOTES
167 --
168 --
169 procedure check_unique_orig_system_ref( p_rowid in varchar2,
170 p_orig_system_reference in varchar2
171 ) is
172 dummy number;
173 begin
174 select 1
175 into dummy
176 from dual
177 where not exists ( select 1
178 from hz_cust_accounts c
179 where c.orig_system_reference = p_orig_system_reference
180 and ( ( p_rowid is null ) or (c.rowid <> p_rowid)));
181
182 exception
183 when NO_DATA_FOUND then
184 fnd_message.set_name ('AR','AR_CUST_REF_ALREADY_EXISTS');
185 app_exception.raise_exception;
186
187 end check_unique_orig_system_ref;
188
189 -- PROCEDURE
190 -- delete_customer_alt_names
191 --
192 -- DESCRIPTION
193 -- Procedure to delete alternate names.
194 --
195 -- SCOPE - PUBLIC
196 --
197 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
198 --
199 -- ARGUMENTS : IN:
200 -- - p_rowid - rowid of row
201 -- - p_status
202 -- - p_customer_id
203 -- OUT:
204 --
205 -- RETURNS null
206 --
207 -- NOTES
208 --
209 -- MODIFICATION HISTORY
210 -- 06-Nov-01 Joe del Callar Fixed bug 2092530.
211 --
212
213 PROCEDURE delete_customer_alt_names(p_rowid IN VARCHAR2,
214 p_status IN VARCHAR2,
215 p_customer_id IN NUMBER) IS
216 l_status VARCHAR2(1);
217 l_lock_status NUMBER;
218 CURSOR statuscur IS
219 SELECT status
220 FROM hz_cust_accounts
221 WHERE rowid = p_rowid;
222
223 BEGIN
224 -- bug 2092530: removed the check to the ar_alt_name_search profile
225 -- option. Also cleaned up the select into l_status statement.
226 IF p_status = 'I' THEN
227
228 OPEN statuscur;
229 FETCH statuscur INTO l_status;
230 CLOSE statuscur;
231
232 IF (l_status = 'A') THEN
233
234 arp_cust_alt_match_pkg.lock_match(p_customer_id, NULL, l_lock_status);
235
236 IF (l_lock_status = 1) THEN
237 -- bug 928111: added alt_name for delete. but no way
238 -- to derive it from here so we are passing null.
239 arp_cust_alt_match_pkg.delete_match(p_customer_id, NULL, NULL) ;
240 END IF;
241 END IF;
242 END IF;
243
244 EXCEPTION
245 WHEN OTHERS THEN
246 arp_standard.debug('EXCEPTION: arp_cust_pkg.delete_customer_alt_names');
247 END delete_customer_alt_names;
248
249 /*===========================================================================+
250 | FUNCTION |
251 | get_statement_site |
252 | |
253 | DESCRIPTION |
254 | |
255 | Returns the site_use_id of a STATEMENT (STMTS) associated with the |
256 | customers address if present else return NULL. |
257 | |
258 | SCOPE - PUBLIC |
259 | |
260 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
261 | |
262 | ARGUMENTS : IN: |
263 | p_customer_id |
264 | OUT: |
265 | site_use_id |
266 | |
267 | RETURNS : site_use_id where site_use_code = 'STMTS' |
268 | |
269 | |
270 | NOTES : |
271 | |
272 | The function is intended to be used in SQL statements. |
273 | |
274 | The intent of its creation was to minimize the code change for all the |
275 | SQLs which were using : |
276 | |
277 | ra customers.statement_site_use_id = hz_cust_site_uses.site_use_id (+) |
278 | |
279 | These queries can now be changed to: |
280 | |
281 | ARP_CUST_PKG.get_statement_site(hz_cust_accounts.cust_account_id) = |
282 | hz_cust_site_uses.site_use_id (+) |
283 | |
284 | Make sure you donot pass a constant as an argument when making use |
285 | of this function in a query which is supposed to succeed even if the |
286 | the statement site does not exist for a customer. The outer join does |
287 | not kick off in an event when the function returns NULL thus making the|
288 | base query to fail. |
289 | |
290 | MODIFICATION HISTORY |
291 | |
292 | 19-JUN-1997 Neeraj Tandon Created |
293 | 04-May-2001 Debbie Jancis Modified for tca uptake. Removed all |
294 | references of ra/ar customer tables |
295 | and replaced with hz counterparts |
296 +===========================================================================*/
297
298 FUNCTION get_statement_site (
299 p_customer_id IN hz_cust_accounts.cust_account_id%type
300 )
301 RETURN NUMBER is
302
303 v_statement_site_use_id hz_cust_site_uses.site_use_id%type;
304
305 BEGIN
306
307 select site_uses.site_use_id
308 into v_statement_site_use_id
309 from hz_cust_acct_sites acct_site,
310 hz_cust_site_uses site_uses
311 where acct_site.cust_account_id = p_customer_id
312 and site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
313 and site_uses.site_use_code = 'STMTS'
314 and site_uses.status = 'A';
315
316 return v_statement_site_use_id;
317
318 EXCEPTION
319
320 WHEN NO_DATA_FOUND THEN
321 return to_number(NULL);
322
323 WHEN OTHERS THEN
324 raise;
325
326 END;
327 --
328 --
329 /*===========================================================================+
330 | FUNCTION |
331 | get_dunning_site |
332 | |
333 | DESCRIPTION |
334 | |
335 | Returns the site_use_id of DUNNING (DUN) associated with the |
336 | customers address if present else return NULL. |
337 | |
338 | SCOPE - PUBLIC |
339 | |
340 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
341 | |
342 | ARGUMENTS : IN: |
343 | p_customer_id |
344 | OUT: |
345 | site_use_id |
346 | |
347 | RETURNS : site_use_id where site_use_code = 'DUN' |
348 | |
349 | |
350 | NOTES : |
351 | |
352 | The function is intended to be used in SQL statements. |
353 | |
354 | The intent of its creation was to minimize the code change for all the |
355 | SQLs which were using : |
356 | |
357 | ra customers.dunning_site_use_id = hz_cust_site_uses.site_use_id (+) |
358 | |
359 | These queries can now be changed to: |
360 | |
361 | ARP_CUST_PKG.get_dunning(hz_cust_accounts.cust_account_id) = |
362 | hz_cust_site_uses.site_use_id (+) |
363 | |
364 | Make sure you donot pass a constant as an argument when making use |
365 | of this function in a query which is supposed to succeed even if the |
366 | the dunning site does not exist for a customer. The outer join does |
367 | not kick off in an event when the function returns NULL thus making the|
368 | base query to fail. |
369 | |
370 | MODIFICATION HISTORY |
371 | |
372 | 19-JUN-1997 Neeraj Tandon Created |
373 | 04-May-2001 Debbie Jancis Modified for tca uptake. Removed all |
374 | references of ra/ar customer tables |
375 | and replaced with hz counterparts |
376 +===========================================================================*/
377
378 FUNCTION get_dunning_site (
379 p_customer_id IN hz_cust_accounts.cust_account_id%type
380 )
381 RETURN NUMBER is
382
383 v_dunning_site_use_id hz_cust_site_uses.site_use_id%type;
384
385 BEGIN
386
387 select site_uses.site_use_id
388 into v_dunning_site_use_id
389 from hz_cust_acct_sites acct_site,
390 hz_cust_site_uses site_uses
391 where acct_site.cust_account_id = p_customer_id
392 and site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
393 and site_uses.site_use_code = 'DUN'
394 and site_uses.status = 'A';
395
396 return v_dunning_site_use_id;
397
398 EXCEPTION
399
400 WHEN NO_DATA_FOUND THEN
401 return to_number(NULL);
402
403 WHEN OTHERS THEN
404 raise;
405
406 END;
407 --
408 --
409 /*===========================================================================+
410 | FUNCTION |
411 | get_current_dunning_type |
412 | |
413 | DESCRIPTION |
414 | |
415 | Returns the current dunning_type associated with a customers profile |
416 | or BILL_TO profile or Dunning profile |
417 | |
418 | SCOPE - PUBLIC |
419 | |
420 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
421 | |
422 | ARGUMENTS : IN: |
423 | p_customer_id |
424 | p_bill_to_site_id |
425 | OUT: |
426 | dunning_type |
427 | |
428 | NOTES : |
429 | To be used in Account Details form to determine whether |
430 | staged_dunning_level field of ar_payment_schedules is updateable |
431 | or not. |
432 | |
433 | MODIFICATION HISTORY |
434 | |
435 | 30-JUN-1997 Neeraj Tandon Created |
436 | |
437 +===========================================================================+*/
438 --
439 FUNCTION get_current_dunning_type (
440 p_customer_id IN hz_cust_accounts.cust_account_id%type,
441 p_bill_to_site_id IN NUMBER
442 )
443 return varchar2 is
444 --
445 v_dunning_site hz_cust_site_uses.site_use_id%type;
446 v_dunning_type ar_dunning_letter_sets.dunning_type%type;
447 --
448 BEGIN
449 --
450 v_dunning_site := get_dunning_site(p_customer_id);
451 --
452 if v_dunning_site is NOT NULL then
453 --
454 select dls.dunning_type
455 into v_dunning_type
456 from hz_customer_profiles prof,
457 ar_dunning_letter_sets dls
458 where prof.cust_account_id = p_customer_id
459 and prof.site_use_id is NULL
460 and prof.dunning_letter_set_id = dls.dunning_letter_set_id;
461
462 else
463 --
464 select dls.dunning_type
465 into v_dunning_type
466 from hz_cust_site_uses su,
467 hz_cust_acct_sites ad_cus,
468 hz_customer_profiles cust_pro,
469 hz_customer_profiles site_pro,
470 ar_dunning_letter_sets dls,
471 hz_cust_accounts cus
472 where su.site_use_code = 'BILL_TO'
473 and su.status = 'A'
474 and su.site_use_id = p_bill_to_site_id
475 and ad_cus.cust_acct_site_id = su.cust_acct_site_id
476 and ad_cus.status = 'A'
477 and cust_pro.cust_account_id = ad_cus.cust_account_id
478 and cust_pro.site_use_id is NULL
479 and cust_pro.status ='A'
480 and site_pro.site_use_id (+) = su.site_use_id
481 and site_pro.status (+) ='A'
482 and dls.dunning_letter_set_id = nvl( site_pro.dunning_letter_set_id,
483 cust_pro.dunning_letter_set_id )
484 and dls.status = 'A'
485 and cus.cust_account_id = ad_cus.cust_account_id
486 and cus.status = 'A'
487 and cus.cust_account_id = p_customer_id;
488
489 end if;
490
491 return v_dunning_type;
492
493 EXCEPTION
494
495 WHEN NO_DATA_FOUND THEN
496 return NULL;
497
498 WHEN OTHERS THEN
499 raise;
500
501 END;
502 --
503 FUNCTION arxvamai_overall_cr_limit ( p_customer_id NUMBER,
504 p_currency_code VARCHAR2,
505 p_customer_site_use_id NUMBER
506 ) RETURN NUMBER is
507 l_overall_cr_limit NUMBER;
508 cursor c_overall IS
509 SELECT overall_credit_limit
510 FROM hz_cust_profile_amts
511 WHERE cust_account_id = p_customer_id
512 AND currency_code = p_currency_code
513 AND decode( p_customer_site_use_id,
514 NULL, -10,
515 p_customer_site_use_id ) = NVL( site_use_id, -10 );
516 BEGIN
517 l_overall_cr_limit := 0;
518 OPEN c_overall;
519 FETCH c_overall INTO l_overall_cr_limit;
520 CLOSE c_overall;
521 RETURN( l_overall_cr_limit);
522 END;
523
524 --
525 FUNCTION arxvamai_order_cr_limit ( p_customer_id NUMBER,
526 p_currency_code VARCHAR2,
527 p_customer_site_use_id NUMBER
528 ) RETURN NUMBER is
529 l_order_cr_limit NUMBER;
530 CURSOR c_order IS
531 SELECT trx_credit_limit
532 FROM hz_cust_profile_amts
533 WHERE cust_account_id = p_customer_id
534 AND currency_code = p_currency_code
535 AND DECODE( p_customer_site_use_id,
536 NULL, -10, p_customer_site_use_id ) =
537 NVL( site_use_id, -10 );
538 BEGIN
539 l_order_cr_limit := 0;
540 OPEN c_order;
541 FETCH c_order INTO l_order_cr_limit;
542 CLOSE c_order;
543 RETURN( l_order_cr_limit );
544 END;
545
546 --
547 FUNCTION get_primary_billto_site (
548 p_customer_id IN hz_cust_accounts.cust_account_id%type
549 )
550 RETURN NUMBER is
551
552 v_billto_site_use_id hz_cust_site_uses.site_use_id%type;
553
554 /* Bug 2625779 - declaring cursor */
555 CURSOR c_site IS
556 SELECT su.site_use_id
557 FROM hz_cust_site_uses su,
558 hz_cust_acct_sites acct_site
559 WHERE su.site_use_code = 'BILL_TO'
560 and su.cust_acct_site_id = acct_site.cust_acct_site_id
561 and acct_site.cust_account_id = p_customer_id
562 and su.primary_flag = 'Y'
563 ORDER BY su.status, su.site_use_id DESC;
564
565 BEGIN
566
567 IF g_site_use_id_tab.EXISTS(p_customer_id) THEN
568 v_billto_site_use_id := g_site_use_id_tab( p_customer_id );
569 ELSE
570 BEGIN
571
572 /* Bug 2625779 - replacement logic */
573 OPEN c_site;
574 FETCH c_site INTO v_billto_site_use_id;
575
576 -- mimicking EXCEPTION - no primary, return NULL
577 IF c_site%NOTFOUND THEN
578 v_billto_site_use_id := NULL;
579 END IF;
580
581 CLOSE c_site;
582 END;
583
584 g_site_use_id_tab(p_customer_id) := v_billto_site_use_id;
585
586 END IF;
587
588 return v_billto_site_use_id;
589
590 EXCEPTION
591
592 WHEN OTHERS THEN
593 raise;
594
595 END;
596 --
597 --
598 END hzp_cust_pkg;
599