[Home] [Help]
PACKAGE BODY: APPS.MRP_SOURCING_GRP
Source
1 PACKAGE BODY MRP_Sourcing_GRP AS
2 /* $Header: MRPGSRCB.pls 120.1 2006/03/08 04:52:38 davashia noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'MRP_Sourcing_GRP';
5
6 FUNCTION validate_item(
7 arg_item_id IN NUMBER,
8 arg_org_id IN NUMBER,
9 arg_sub_inv IN VARCHAR2 DEFAULT NULL)
10 RETURN BOOLEAN IS
11
12 var_stk VARCHAR2(10);
13 var_restrict NUMBER;
14 BEGIN
15
16 SELECT stock_enabled_flag, restrict_subinventories_code
17 INTO var_stk,
18 var_restrict
19 FROM mtl_system_items
20 WHERE organization_id = arg_org_id
21 AND inventory_item_id = arg_item_id;
22
23 if var_stk <> 'Y'
24 then
25 return FALSE;
26 end if;
27
28 if arg_sub_inv is not null AND var_restrict = 1
29 then
30 BEGIN
31 SELECT 'Y'
32 into var_stk
33 FROM mtl_item_sub_inventories
34 WHERE organization_id = arg_org_id
35 AND inventory_item_id = arg_item_id
36 AND secondary_inventory = arg_sub_inv;
37
38 EXCEPTION
39 WHEN no_data_found THEN
40 RETURN FALSE;
41 END;
42 end if;
43 RETURN TRUE;
44 END validate_item;
45
46 PROCEDURE Get_Source(
47 p_api_version IN NUMBER,
48 x_return_status OUT NOCOPY VARCHAR2,
49 p_mode IN VARCHAR2,
50 p_item_id IN NUMBER,
51 p_commodity_id IN NUMBER,
52 p_dest_organization_id IN NUMBER,
53 p_dest_subinventory IN VARCHAR2,
54 p_autosource_date IN DATE,
55 x_vendor_id OUT NOCOPY NUMBER,
56 x_vendor_site_code OUT NOCOPY VARCHAR2,
57 x_source_organization_id IN OUT NOCOPY NUMBER,
58 x_source_subinventory IN OUT NOCOPY VARCHAR2,
59 x_sourcing_rule_id OUT NOCOPY NUMBER,
60 x_error_message OUT NOCOPY VARCHAR2)
61 IS
62
63 l_api_name CONSTANT VARCHAR2(30) := 'Get_Source';
64 l_api_version CONSTANT NUMBER := 1.0;
65
66 var_set_id NUMBER;
67 var_vendor_id NUMBER;
68 var_site_id NUMBER;
69 var_new_site_code VARCHAR2(30);
70 var_source_org NUMBER;
71 var_alloc_percent NUMBER;
72 var_rank NUMBER;
73 var_sr_id NUMBER;
74 var_source_sub VARCHAR2(30);
75
76 found_org BOOLEAN;
77 found_ven BOOLEAN;
78
79 cursor sourcing( p_source_organization_id NUMBER) is
80 SELECT misl.vendor_id,
81 misl.vendor_site_id,
82 misl.source_organization_id,
83 misl.allocation_percent,
84 NVL(misl.rank, 9999),
85 misl.sourcing_rule_id
86 FROM mrp_item_sourcing_levels_v misl
87 WHERE misl.source_type in (1,3)
88 AND misl.inventory_item_id = p_item_id
89 AND misl.organization_id = p_dest_organization_id
90 AND misl.assignment_set_id = var_set_id
91 AND p_autosource_date between misl.effective_date AND
92 NVL(disable_date, to_date(2634525, 'J'))
93 AND PO_ASL_SV.check_asl_action('2_SOURCING',
94 misl.vendor_id, misl.vendor_site_id, p_item_id, -1
95 , p_dest_organization_id ) <> 0
96 AND nvl(nvl(p_source_organization_id,
97 misl.source_organization_id), -23453)
98 = nvl(misl.source_organization_id, -23453)
99 ORDER BY misl.sourcing_level ASC,
100 allocation_percent DESC, NVL(misl.rank, 9999) ASC;
101 BEGIN
102
103 -- Standard Start of API savepoint
104 SAVEPOINT Get_Source_GRP;
105
106 -- Standard call to check for call compatibility.
107 IF NOT fnd_api.compatible_api_call (l_api_version,
111 )
108 p_api_version,
109 l_api_name,
110 g_pkg_name
112 THEN
113 RAISE fnd_api.g_exc_unexpected_error;
114 END IF;
115
116 -- Initialize API return status to success
117 x_return_status := fnd_api.g_ret_sts_success;
118
119 IF x_source_organization_id IS NOT NULL AND
120 x_source_subinventory IS NOT NULL THEN
121 x_return_status := fnd_api.g_ret_sts_success;
122 RETURN;
123 END IF;
124
125
126 found_org := FALSE;
127 found_ven := FALSE;
128 x_error_message := NULL;
129 x_vendor_id := NULL;
130 x_vendor_site_code := NULL;
131
132 /*------------------------------------------------------------+
133 | First check the item-sub level if the mode is Inventory |
134 | OR both |
135 +------------------------------------------------------------*/
136 IF p_mode = 'INVENTORY' OR p_mode = 'BOTH'
137 THEN
138 BEGIN
139
140 SELECT misi.source_organization_id,
141 misi.source_subinventory
142 INTO var_source_org,
143 var_source_sub
144 FROM mtl_item_sub_inventories misi,
145 org_organization_definitions ood,
146 financials_system_parameters fsp
147 WHERE misi.organization_id = p_dest_organization_id
148 AND misi.inventory_item_id = p_item_id
149 AND misi.secondary_inventory = p_dest_subinventory
150 AND misi.organization_id = ood.organization_id
151 AND ood.set_of_books_id = fsp.set_of_books_id
152 AND ood.operating_unit = fsp.org_id -- bug 4968383
153 AND NVL(x_source_organization_id,
154 misi.source_organization_id) = misi.source_organization_id;
155
156 /*--------------------------------------+
157 | Validate the item in the source org |
158 +--------------------------------------*/
159 IF validate_item(p_item_id, var_source_org, var_source_sub)
160 = TRUE
161 THEN
162 x_source_organization_id := var_source_org;
163 x_source_subinventory := var_source_sub;
164 found_org := TRUE;
165 /*---------------------------------------+
166 | If mode is inventory you are done |
167 +---------------------------------------*/
168 IF p_mode = 'INVENTORY'
169 THEN
170 x_return_status := fnd_api.g_ret_sts_success;
171 RETURN;
172 END IF;
173 END IF;
174 EXCEPTION
175 WHEN NO_DATA_FOUND THEN
176 NULL;
177 END;
178 END IF;
179 /*------------------------------------------------------------+
180 | THEN check the sub level if the mode is Inventory |
181 | OR both |
182 +------------------------------------------------------------*/
183 IF p_mode = 'INVENTORY' OR p_mode = 'BOTH'
184 THEN
185 BEGIN
186
187 SELECT msi.source_organization_id,
188 msi.source_subinventory
189 INTO var_source_org,
190 var_source_sub
191 FROM mtl_secondary_inventories msi,
192 org_organization_definitions ood,
193 financials_system_parameters fsp
194 WHERE msi.organization_id = p_dest_organization_id
195 AND msi.secondary_inventory_name = p_dest_subinventory
196 AND msi.organization_id = ood.organization_id
197 AND ood.set_of_books_id = fsp.set_of_books_id
198 AND ood.operating_unit = fsp.org_id -- bug 4968383
199 AND NVL(x_source_organization_id,
200 msi.source_organization_id) = msi.source_organization_id;
201
202
203 /*--------------------------------------+
204 | Validate the item in the source org |
205 +--------------------------------------*/
206 IF validate_item(p_item_id, var_source_org, var_source_sub)
207 = TRUE
208 THEN
209 x_source_organization_id := var_source_org;
210 x_source_subinventory := var_source_sub;
211 found_org := TRUE;
212 /*---------------------------------------+
213 | If mode is inventory you are done |
214 +---------------------------------------*/
215 IF p_mode = 'INVENTORY'
216 THEN
217 x_return_status := fnd_api.g_ret_sts_success;
218 RETURN;
219 END IF;
220 END IF;
221 EXCEPTION
222 WHEN NO_DATA_FOUND THEN
223 NULL;
224 END;
225 END IF;
226
227
228 /*------------------------------------------------------------+
229 | THEN check the item-org level if the mode is Inventory |
230 | OR both |
231 +------------------------------------------------------------*/
235 SELECT msi.source_organization_id,
232 IF p_mode = 'INVENTORY' OR p_mode = 'BOTH'
233 THEN
234 BEGIN
236 msi.source_subinventory
237 INTO var_source_org,
238 var_source_sub
239 FROM mtl_system_items msi,
240 org_organization_definitions ood,
241 financials_system_parameters fsp
242 WHERE msi.organization_id = p_dest_organization_id
243 AND msi.inventory_item_id = p_item_id
244 AND msi.organization_id = ood.organization_id
245 AND ood.operating_unit = fsp.org_id -- bug 4968383
246 AND ood.set_of_books_id = fsp.set_of_books_id;
247
248 /*--------------------------------------+
249 | Validate the item in the source org |
250 +--------------------------------------*/
251 IF validate_item(p_item_id, var_source_org, var_source_sub)
252 = TRUE
253 THEN
254 x_source_organization_id := var_source_org;
255 x_source_subinventory := var_source_sub;
256 found_org := TRUE;
257 /*---------------------------------------+
258 | If mode is inventory you are done |
259 +---------------------------------------*/
260 IF p_mode = 'INVENTORY'
261 THEN
262 x_return_status := fnd_api.g_ret_sts_success;
263 RETURN;
264 END IF;
265 END IF;
266 EXCEPTION
267 WHEN NO_DATA_FOUND THEN
268 NULL;
269 END;
270 END IF;
271 /* end of item-org check */
272
273 /*------------------------------------------------------------+
274 | THEN check the Org level if the mode is Inventory |
275 | OR both |
276 +------------------------------------------------------------*/
277 IF p_mode = 'INVENTORY' OR p_mode = 'BOTH'
278 THEN
279 BEGIN
280 SELECT mp.source_organization_id,
281 mp.source_subinventory
282 INTO var_source_org,
283 var_source_sub
284 FROM mtl_parameters mp,
285 org_organization_definitions ood,
286 financials_system_parameters fsp
287 WHERE mp.organization_id = p_dest_organization_id
288 AND mp.organization_id = ood.organization_id
289 AND ood.operating_unit = fsp.org_id -- bug 4968383
290 AND ood.set_of_books_id = fsp.set_of_books_id;
291
292 /*--------------------------------------+
293 | Validate the item in the source org |
294 +--------------------------------------*/
295 IF validate_item(p_item_id, var_source_org, var_source_sub)
296 = TRUE
297 THEN
298 x_source_organization_id := var_source_org;
299 x_source_subinventory := var_source_sub;
300 found_org := TRUE;
301 /*---------------------------------------+
302 | If mode is inventory you are done |
303 +---------------------------------------*/
304 IF p_mode = 'INVENTORY'
305 THEN
306 x_return_status := fnd_api.g_ret_sts_success;
307 RETURN;
308 END IF;
309 END IF;
310 EXCEPTION
311 WHEN NO_DATA_FOUND THEN
312 NULL;
313 END;
314 end if;
315 /* end of Org check */
316
317 /*-----------------------------------------+
318 | before checking MRP sources get the |
319 | default assignment set id |
320 +-----------------------------------------*/
321 var_set_id :=
322 TO_NUMBER(FND_PROFILE.VALUE('MRP_DEFAULT_ASSIGNMENT_SET'));
323
324 IF var_set_id IS NULL
325 THEN
326 x_return_status := FND_API.G_RET_STS_ERROR ;
327 fnd_message.set_name('MRP', 'MRCONC-CANNOT GET PROFILE');
328 fnd_message.set_token('PROFILE', 'MRP_DEFAULT_ASSIGNMENT_SET');
329 RETURN;
330 END IF;
331
332
333 BEGIN
334 OPEN sourcing(x_source_organization_id);
335
336 /*------------------------------------------------+
337 | Loop through all the levels looking for first |
338 | valid source |
339 +------------------------------------------------*/
340 LOOP FETCH sourcing INTO
341 var_vendor_id,
342 var_site_id,
343 var_source_org,
344 var_alloc_percent,
345 var_rank,
346 var_sr_id;
347 EXIT WHEN sourcing%NOTFOUND;
348
349 var_new_site_code := '-23453' ;
350
351 IF var_vendor_id IS NOT NULL THEN
352 SELECT site.vendor_site_code
353 INTO var_new_site_code
354 FROM
355 po_vendor_sites_all site,
356 po_vendors ven
357 WHERE NVL(ven.enabled_flag, 'N') = 'Y'
358 AND SYSDATE BETWEEN NVL(ven.start_date_active, SYSDATE -1)
362 AND site.vendor_site_id(+) = var_site_id
359 AND NVL(ven.end_date_active, sysdate+1)
360 AND SYSDATE < NVL(site.inactive_date, SYSDATE + 1)
361 AND ven.vendor_id = site.vendor_id(+)
363 AND ven.vendor_id = var_vendor_id ;
364 END IF;
365
366 IF (var_vendor_id IS NULL OR var_new_site_code <> '-23453' OR
367 (var_new_site_code IS NULL AND var_vendor_id IS NOT NULL)) THEN
368
369 IF (found_org = FALSE AND
370 (p_mode = 'INVENTORY' OR p_mode = 'BOTH')
371 AND var_source_org IS NOT NULL)
372 THEN
373 /*--------------------------------------+
374 | Validate the item in the source org |
375 +--------------------------------------*/
376 IF validate_item(p_item_id, var_source_org) = TRUE
377 THEN
378 found_org := TRUE;
379 x_source_organization_id := var_source_org;
380 IF (p_mode = 'INVENTORY')
381 THEN
382 x_return_status := fnd_api.g_ret_sts_success;
383 RETURN;
384 END IF;
385 END IF;
386 END IF;
387 IF (found_ven = FALSE AND
388 (p_mode = 'VENDOR' OR p_mode = 'BOTH')AND
389 var_vendor_id IS NOT NULL)
390 THEN
391 found_ven := TRUE;
392 x_vendor_id := var_vendor_id;
393 x_vendor_site_code := var_new_site_code;
394 if (p_mode = 'VENDOR')
395 THEN
396 x_return_status := fnd_api.g_ret_sts_success;
397 RETURN;
398 END IF;
399 END IF;
400 IF (found_org = TRUE AND found_ven = TRUE)
401 THEN
402 x_return_status := fnd_api.g_ret_sts_success;
403 RETURN;
404 END IF;
405 END IF;
406 END LOOP;
407 if (((p_mode = 'INVENTORY' OR p_mode = 'BOTH')AND
408 found_org = FALSE) OR
409 ((p_mode = 'VENDOR' OR p_mode = 'BOTH')AND
410 found_ven = FALSE))
411 THEN
412 x_return_status := FND_API.G_RET_STS_ERROR ;
413 fnd_message.set_name('MRP', 'GEN-CANNOT SELECT');
414 fnd_message.set_token('SELECT', 'EC_SOURCE', TRUE);
415 fnd_message.set_token('ROUTINE', 'MRP_SOURCING', FALSE);
416 RETURN;
417 END IF;
418 EXCEPTION
419 WHEN NO_DATA_FOUND THEN
420 x_return_status := FND_API.G_RET_STS_ERROR ;
421 fnd_message.set_name('MRP', 'GEN-NO ROWS SELECTED');
422 fnd_message.set_token('TABLE', 'mrp_sources_v');
423 RETURN;
424 END;
425 END Get_Source;
426
427 END MRP_Sourcing_GRP;