[Home] [Help]
PACKAGE BODY: APPS.ARP_CLAS_PKG
Source
1 PACKAGE BODY arp_clas_pkg as
2 /* $Header: AROCLASB.pls 120.8.12010000.2 2008/11/19 11:36:31 ankuagar ship $ */
3 --
4 --
5 -- PROCEDURE
6 -- check_unique _inv_location
7 --
8 -- DESCRIPTION
9 -- This procedure ensures an inventory_location is only assigned to
10 -- one custoemr ship-to site.
11 --
12 -- SCOPE - PUBLIC
13 --
14 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
15 --
16 -- ARGUMENTS : IN:
17 -- -- p_inventory_location_id
18 --
19 -- OUT:
20 --
21 -- NOTES
22 --
23 --
24 --
25 procedure check_unique_inv_location ( p_inventory_location_id in number, x_return_status out nocopy varchar2,
26 x_msg_count out nocopy number, x_msg_data out nocopy varchar2, l_org_id in number
27 ) is
28 --
29 dummy number;
30 --
31 begin
32 select count(1)
33 into dummy
34 from po_location_associations_all
35 where location_id = p_inventory_location_id
36 and org_id = l_org_id;
37 --
38 if ( dummy >= 1 ) then
39 fnd_message.set_name('AR','AR_CUST_DUP_INTERNAL_LOCATION');
40 --app_exception.raise_exception;
41 FND_MSG_PUB.ADD;
42 x_return_status := FND_API.G_RET_STS_ERROR;
43 x_msg_count := 1;
44 x_msg_data := 'AR_CUST_DUP_INTERNAL_LOCATION';
45 return;
46 end if;
47 end check_unique_inv_location;
48 --
49 --
50 procedure check_unique_inv_location ( p_inventory_location_id in number ) is
51 --
52 dummy number;
53 --
54 begin
55 select count(1)
56 into dummy
57 from po_location_associations_all
58 where location_id = p_inventory_location_id;
59 --
60 if ( dummy >= 1 ) then
61 fnd_message.set_name('AR','AR_CUST_DUP_INTERNAL_LOCATION');
62 app_exception.raise_exception;
63 end if;
64 end check_unique_inv_location;
65 --
66 --
67
68 --
69 -- PROCEDURE
70 -- insert_po_loc_associations
71 --
72 -- DESCRIPTION
73 -- This procedure inserts rows into the table po_location_associations
74 -- This table is simple implements a foreign key from ra_site_use.site_use_id
75 -- to hr_locations.
76 --
77 -- SCOPE - PUBLIC
78 --
79 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
80 --
81 -- ARGUMENTS : IN:
82 -- -- p_inventory_location_id
83 --
84 -- OUT:
85 --
86 -- NOTES
87 --
88 --
89 --
90 procedure insert_po_loc_associations ( p_inventory_location_id in number,
91 p_inventory_organization_id in number,
92 p_customer_id in number,
93 p_address_id in number,
94 p_site_use_id in number,
95 x_return_status out nocopy varchar2,
96 x_msg_count out nocopy number,
97 x_msg_data out nocopy varchar2
98 ) is
99 --
100
101 l_org_id number;
102 l_return_status VARCHAR2(1);
103 begin
104
105 BEGIN
106 SELECT org_id
107 INTO l_org_id
108 FROM HZ_CUST_ACCT_SITES_ALL
109 WHERE cust_acct_site_id
110 = p_address_id;
111 EXCEPTION
112 WHEN NO_DATA_FOUND THEN
113 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
114 FND_MESSAGE.SET_TOKEN( 'RECORD', 'customer account site' );
115 FND_MESSAGE.SET_TOKEN( 'VALUE',
116 NVL( TO_CHAR(
117 l_org_id ), 'null' ) );
118 FND_MSG_PUB.ADD;
119 RAISE FND_API.G_EXC_ERROR;
120 END;
121
122 --
123 check_unique_inv_location( p_inventory_location_id => p_inventory_location_id, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, l_org_id => l_org_id );
124 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
125 return;
126 end if;
127 --
128
129 BEGIN
130 MO_GLOBAL.validate_orgid_pub_api( l_org_id,'N',l_return_status);
131 EXCEPTION
132 WHEN OTHERS
133 THEN
134 RAISE FND_API.G_EXC_ERROR;
135 END;
136 insert into po_location_associations (
137 location_id,
138 last_update_date,
139 last_updated_by,
140 last_update_login,
141 creation_date,
142 created_by,
143 customer_id,
144 address_id,
145 site_use_id,
146 organization_id,
147 org_id
148 ) values (
149 p_inventory_location_id,
150 sysdate,
151 fnd_global.user_id,
152 fnd_global.Login_id,
153 sysdate,
154 fnd_global.user_id,
155 p_customer_id,
156 p_address_id,
157 p_site_use_id,
158 p_inventory_organization_id,
159 l_org_id
160 );
161 --
162 end insert_po_loc_associations;
163 --
164 --
165
166 procedure insert_po_loc_associations ( p_inventory_location_id in number,
167 p_inventory_organization_id in number,
168 p_customer_id in number,
169 p_address_id in number,
170 p_site_use_id in number
171 ) is
172 --
173 begin
174 --
175 check_unique_inv_location( p_inventory_location_id => p_inventory_location_id );
176 --
177 insert into po_location_associations (
178 location_id,
179 last_update_date,
180 last_updated_by,
181 last_update_login,
182 creation_date,
183 created_by,
184 customer_id,
185 address_id,
186 site_use_id,
187 organization_id,
188 org_id
189 ) values (
190 p_inventory_location_id,
191 sysdate,
192 fnd_global.user_id,
193 fnd_global.Login_id,
194 sysdate,
195 fnd_global.user_id,
196 p_customer_id,
197 p_address_id,
198 p_site_use_id,
199 p_inventory_organization_id,
200 arp_standard.sysparm.org_id
201 );
202 --
203 end insert_po_loc_associations;
204 --
205
206
207 procedure update_po_loc_associations ( p_site_use_id in number,
208 p_address_id in number,
209 p_customer_id in number,
210 p_inventory_organization_id in number,
211 p_inventory_location_id in number,
212 x_return_status out nocopy varchar2,
213 x_msg_count out nocopy number,
214 x_msg_data out nocopy varchar2
215 ) is
216 --
217 l_dummy number;
218 l_inventory_location_id number;
219 l_inventory_organization_id number;
220 --
221 begin
222 --
223 --
224 /* Bug3246371 : Added variable l_inventory_organization_id
225 and modified the select condition to get organization_id
226 Modified the 'if' condition to check whether
227 organization_id is same*/
228 begin
229 select location_id,organization_id
230 into l_inventory_location_id,l_inventory_organization_id
231 from po_location_associations
232 where site_use_id = p_site_use_id;
233 exception
234 when NO_DATA_FOUND then
235 null;
236 end;
237 -- next 2 commented lines are fix for 5741810
238 -- if ( l_inventory_location_id = p_inventory_location_id ) and
239 -- ( l_inventory_organization_id = p_inventory_organization_id) then
240 IF (l_inventory_location_id IS NOT NULL OR l_inventory_organization_id IS NOT NULL)
241 AND (NVL(l_inventory_location_id,-9999) = NVL(p_inventory_location_id,-9999) AND
242 NVL(l_inventory_organization_id,-9999) = NVL(p_inventory_organization_id,-9999))
243 THEN
244
245 return;
246 else
247 --
248 SELECT count(1)
249 INTO l_dummy
250 FROM po_requisition_lines porl
251 WHERE porl.deliver_to_location_id = l_inventory_location_id
252 AND nvl(porl.source_type_code, 'VENDOR') = 'INVENTORY';
253 --
254 if ( l_dummy >= 1 ) then
255 fnd_message.set_name('AR','AR_CUST_INT_REQ_LINES_EXIST');
256 FND_MSG_PUB.ADD;
257 --app_exception.raise_exception;
258 x_return_status := FND_API.G_RET_STS_ERROR;
259 x_msg_count := 1;
260 x_msg_data := 'AR_CUST_INT_REQ_LINES_EXIST';
261 return;
262 end if;
263 --
264 delete from po_location_associations
265 where site_use_id = p_site_use_id;
266 --
267 end if;
268 --
269 if ( p_inventory_location_id is not null ) then
270 --
271 insert_po_loc_associations( p_inventory_location_id => p_inventory_location_id,
272 p_inventory_organization_id => p_inventory_organization_id,
273 p_customer_id => p_customer_id,
274 p_address_id => p_address_id,
275 p_site_use_id => p_site_use_id,
276 x_return_status => x_return_status,
277 x_msg_count => x_msg_count,
278 x_msg_data => x_msg_data
279 );
280 --
281 end if;
282 --
283
284 end update_po_loc_associations;
285 --
286 --
287
288 procedure update_po_loc_associations ( p_site_use_id in number,
289 p_address_id in number,
290 p_customer_id in number,
291 p_inventory_organization_id in number,
292 p_inventory_location_id in number ) is
293 --
294 l_dummy number;
295 l_inventory_location_id number;
296 l_inventory_organization_id number;
297 --
298 begin
299 --
300 --
301 /* Bug3246371 : Added variable l_inventory_organization_id
302 and modified the select condition to get organization_id
303 Modified the 'if' condition to check whether
304 organization_id is same*/
305 begin
306 select location_id,organization_id
307 into l_inventory_location_id,l_inventory_organization_id
308 from po_location_associations
309 where site_use_id = p_site_use_id;
310 exception
311 when NO_DATA_FOUND then
312 null;
313 end;
314 --
315 if ( l_inventory_location_id = p_inventory_location_id ) and
316 ( l_inventory_organization_id = p_inventory_organization_id) then
317 return;
318 else
319 --
320 SELECT count(1)
321 INTO l_dummy
322 FROM po_requisition_lines porl
323 WHERE porl.deliver_to_location_id = l_inventory_location_id
324 AND nvl(porl.source_type_code, 'VENDOR') = 'INVENTORY';
325 --
326 if ( l_dummy >= 1 ) then
327 fnd_message.set_name('AR','AR_CUST_INT_REQ_LINES_EXIST');
328 app_exception.raise_exception;
329 end if;
330 --
331 delete from po_location_associations
332 where site_use_id = p_site_use_id;
333 --
334 end if;
335 --
336 --
337 if ( p_inventory_location_id is not null ) then
338 --
339 insert_po_loc_associations( p_inventory_location_id => p_inventory_location_id,
340 p_inventory_organization_id => p_inventory_organization_id,
341 p_customer_id => p_customer_id,
342 p_address_id => p_address_id,
343 p_site_use_id => p_site_use_id
344 );
345 --
346 end if;
347 --
348
349 end update_po_loc_associations;
350 --
351 --
352
353
354 --
355 END arp_clas_pkg;