1 PACKAGE BODY arp_cust_alt_match_pkg as
2 /* $Header: ARCUANMB.pls 115.5 2003/10/10 14:23:46 mraymond ship $ */
3
4 --
5 --
6 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
7
8 PROCEDURE delete_match( p_customer_id in number,
9 p_site_use_id in number,
10 p_alt_name in varchar2
11 ) IS
12 BEGIN
13
14 IF p_alt_name is NOT NULL then
15 IF p_site_use_id is null then
16 DELETE ar_customer_alt_names
17 WHERE customer_id = p_customer_id
18 and alt_name = p_alt_name;
19
20 ELSE
21 DELETE ar_customer_alt_names
22 WHERE customer_id = p_customer_id
23 AND site_use_id = p_site_use_id
24 AND alt_name = p_alt_name;
25 END IF;
26 ELSE
27 IF p_site_use_id is null then
28 DELETE ar_customer_alt_names
29 WHERE customer_id = p_customer_id;
30 ELSE
31 DELETE ar_customer_alt_names
32 WHERE customer_id = p_customer_id
33 AND site_use_id = p_site_use_id;
34 END IF;
35 END IF;
36 EXCEPTION
37 WHEN OTHERS THEN
38 IF PG_DEBUG in ('Y', 'C') THEN
39 arp_standard.debug('EXCEPTION: arp_cust_alt_match_pkg.delete_match');
40 END IF;
41 RAISE;
42 END delete_match;
43 --
44 --
45 PROCEDURE insert_match( p_alt_name in VARCHAR2,
46 p_customer_id in NUMBER,
47 p_site_use_id in NUMBER,
48 p_term_id in NUMBER
49 ) IS
50
51 l_alt_name_id NUMBER;
52 l_dummy NUMBER;
53 l_user_id NUMBER;
54
55 CURSOR c1 IS
56 SELECT 1
57 FROM ar_customer_alt_names
58 WHERE customer_id = p_customer_id
59 AND alt_name = p_alt_name
60 AND decode(site_use_id,'',-1,site_use_id) = decode(p_site_use_id,'',-1,p_site_use_id);
61
62 BEGIN
63
64 l_user_id := fnd_global.user_id;
65
66 open c1;
67 fetch c1 into l_dummy;
68
69 IF not c1%FOUND THEN
70
71 SELECT ar_customer_alt_names_s.nextval
72 INTO l_alt_name_id
73 FROM dual;
74
75 INSERT INTO ar_customer_alt_names (
76 alt_name_id,
77 alt_name,
78 customer_id,
79 site_use_id,
80 term_id,
81 created_by,
82 creation_date,
83 last_update_date,
84 last_update_login,
85 last_updated_by
86 ) values (
87 l_alt_name_id,
88 p_alt_name,
89 p_customer_id,
90 p_site_use_id,
91 p_term_id,
92 l_user_id,
93 sysdate,
94 sysdate,
95 l_user_id,
96 l_user_id );
97 END IF;
98
99 close c1;
100
101 EXCEPTION
102 WHEN OTHERS THEN
103 IF PG_DEBUG in ('Y', 'C') THEN
104 arp_standard.debug('EXCEPTION: arp_cust_alt_match_pkg.insert_match');
105 END IF;
106 RAISE;
107
108 END insert_match;
109 --
110 --
111 PROCEDURE update_pay_term_id( p_customer_id in number,
112 p_site_use_id in number,
113 p_term_id in number
114 ) IS
115 l_alt_name_id NUMBER;
116 l_user_id NUMBER;
117
118 CURSOR c1 IS
119 SELECT alt_name_id
120 FROM ar_customer_alt_names
121 WHERE customer_id = p_customer_id
122 AND decode(site_use_id,'',-1,site_use_id) = decode(p_site_use_id,'',-1,p_site_use_id)
123 FOR UPDATE;
124
125 BEGIN
126
127 l_user_id := fnd_global.user_id;
128
129 open c1;
130 fetch c1 into l_alt_name_id;
131
132 IF c1%FOUND THEN
133
134 UPDATE ar_customer_alt_names
135 SET term_id = p_term_id,
136 last_update_date = sysdate,
137 last_update_login = l_user_id,
138 last_updated_by = l_user_id
139 WHERE alt_name_id = l_alt_name_id;
140
141 END IF;
142 close c1;
143
144 EXCEPTION
145 WHEN OTHERS THEN
146 IF PG_DEBUG in ('Y', 'C') THEN
147 arp_standard.debug('update_pay_term_id: ' || 'EXCEPTION: arp_cust_alt_match_pkg.update_pay_term');
148 END IF;
149
150 END update_pay_term_id;
151 --
152 PROCEDURE lock_match( p_customer_id in number,
153 p_site_use_id in number,
154 p_status out NOCOPY number
155 ) IS
156 BEGIN
157
158 p_status := 0;
159
160 IF p_site_use_id is null then
161 SELECT 1
162 INTO p_status
163 FROM ar_customer_alt_names
164 WHERE customer_id = p_customer_id
165 FOR UPDATE OF alt_name_id NOWAIT;
166
167 ELSE
168 SELECT 1
169 INTO p_status
170 FROM ar_customer_alt_names
171 WHERE customer_id = p_customer_id
172 AND site_use_id = p_site_use_id
173 FOR UPDATE OF alt_name_id NOWAIT;
174
175 END IF;
176
177 EXCEPTION
178 WHEN OTHERS THEN
179 IF PG_DEBUG in ('Y', 'C') THEN
180 arp_standard.debug('EXCEPTION: arp_cust_alt_match_pkg.lock_match');
181 END IF;
182 END lock_match;
183 --
184 --
185
186 END arp_cust_alt_match_pkg;
[Home] [Help]