1 PACKAGE BODY POR_AUTOSOURCE_UTIL_PKG AS
2 /* $Header: PORSRCUB.pls 120.7 2006/08/18 22:08:58 jinwang noship $ */
3
4 -- Logging Static Variables
5 G_CURRENT_RUNTIME_LEVEL NUMBER;
6 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
7 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
8 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
10 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
12 G_MODULE_NAME CONSTANT VARCHAR2(50) := 'ICX.PLSQL.POR_AUTOSOURCE_UTIL_PKG.';
13
14 /* Check if the item is internally orderable in the destination organization */
15 FUNCTION is_internal_orderable(p_item_id IN NUMBER,
16 p_organization_id IN NUMBER
17 ) RETURN NUMBER
18
19 IS
20
21 l_internal_order_enabled_flag VARCHAR(1) := 'N';
22
23 BEGIN
24
25 select internal_order_enabled_flag
26 into l_internal_order_enabled_flag
27 from mtl_system_items
28 where inventory_item_id = p_item_id
29 and organization_id = p_organization_id;
30
31 IF l_internal_order_enabled_flag = 'Y' THEN
32 RETURN 1;
33 END IF;
34
35 RETURN 0;
36
37 EXCEPTION
38 WHEN OTHERS THEN
39 RETURN 0;
40
41 END is_internal_orderable;
42
43
44 FUNCTION is_item_purchasable(p_item_id IN NUMBER,
45 p_organization_id IN NUMBER
46 ) RETURN NUMBER
47
48 IS
49
50 l_purchasable_flag VARCHAR(1) := 'N';
51
52 BEGIN
53
54 IF fnd_profile.value('REQUISITION_TYPE') = 'INTERNAL' THEN
55 RETURN 0;
56 END IF;
57
58 select purchasing_enabled_flag
59 into l_purchasable_flag
60 from mtl_system_items
61 where inventory_item_id = p_item_id
62 and organization_id = p_organization_id;
63
64 IF l_purchasable_flag = 'Y' THEN
65 RETURN 1;
66 END IF;
67
68 RETURN 0;
69
70 EXCEPTION
71 WHEN OTHERS THEN
72 RETURN 0;
73
74 END is_item_purchasable;
75
76 /* Used to get the unit of issue of the destination org */
77 FUNCTION get_unit_of_issue(p_item_id IN NUMBER,
78 p_organization_id IN NUMBER
79 ) RETURN VARCHAR
80
81 IS
82
83 l_unit_of_issue VARCHAR(25);
84
85 BEGIN
86 select nvl(msi.unit_of_issue,msi.primary_unit_of_measure)
87 into l_unit_of_issue
88 from mtl_system_items msi
89 where msi.inventory_item_id = p_item_id
90 and msi.organization_id = p_organization_id;
91
92 RETURN l_unit_of_issue;
93
94 EXCEPTION
95 WHEN OTHERS THEN
96 RETURN NULL;
97
98 END get_unit_of_issue;
99
100 /* Used to determine if the item flags are correctly in the source org $ */
101 FUNCTION is_item_shippable(p_item_id IN NUMBER,
102 p_organization_id IN NUMBER
103 ) RETURN NUMBER
104
105 IS
106
107 l_oe_transactable_flag VARCHAR(1);
108 l_shippable_flag VARCHAR(1);
109 l_mtl_transactable_flag VARCHAR(1);
110 l_stockable_flag VARCHAR(1);
111
112 BEGIN
113
114 -- check the shippable, stockable, oe transactable and mtl_transactable flags
115 select shippable_item_flag, so_transactions_flag, stock_enabled_flag, mtl_transactions_enabled_flag
116 into l_shippable_flag, l_oe_transactable_flag, l_stockable_flag, l_mtl_transactable_flag
117 from mtl_system_items
118 where inventory_item_id = p_item_id
119 and organization_id = p_organization_id;
120
121 IF l_shippable_flag='Y' and l_oe_transactable_flag='Y' and l_stockable_flag='Y' and l_mtl_transactable_flag='Y' THEN
122 RETURN 1;
123 END IF;
124
125 RETURN 0;
126
127 EXCEPTION
128 WHEN OTHERS THEN
129 RETURN 0;
130
131 END is_item_shippable;
132
133 /* Check if there is a valid shipping network between the source org and the destination org */
134 FUNCTION is_valid_shipping_network(p_from_organization_id IN NUMBER,
135 p_to_organization_id IN NUMBER
136 ) RETURN NUMBER
137
138 IS
139
140 l_is_valid_shipping_network VARCHAR(1);
141
142 BEGIN
143
144 IF p_from_organization_id = p_to_organization_id THEN
145 RETURN 1;
146 END IF;
147
148 select 1 into l_is_valid_shipping_network
149 from mtl_interorg_parameters
150 where from_organization_id = p_from_organization_id
151 and to_organization_id = p_to_organization_id;
152
153 IF l_is_valid_shipping_network = '1' THEN
154 RETURN 1;
155 END IF;
156
157 RETURN 0;
158
159 EXCEPTION
160 WHEN OTHERS THEN
161 RETURN 0;
162
163 END is_valid_shipping_network;
164
165 /* Make sure the item is properly assigned to the source org */
166 FUNCTION is_item_assigned(p_item_id IN NUMBER,
167 p_source_organization_id IN NUMBER
168 ) RETURN NUMBER
169
170 IS
171
172 l_is_item_assigned VARCHAR(1);
173
174 BEGIN
175
176 SELECT 1 into l_is_item_assigned
177 FROM mtl_system_items msi
178 WHERE msi.inventory_item_id = p_item_id
179 and msi.organization_id = p_source_organization_id;
180
181 IF l_is_item_assigned = '1' THEN
182 RETURN 1;
183 END IF;
184
185 RETURN 0;
186
187 EXCEPTION
188 WHEN OTHERS THEN
189 RETURN 0;
190
191 END is_item_assigned;
192
193 /* The main autosource API that is called by Autosource.java and SourceInfo.java. */
194 /* Contains all of the autosource logic to pick the correct source based on the sourcing */
195 /* rules and make sure the item can be sourced from that organization. If the item can */
196 /* be sourced from the organization, then it will return the source org id, the subinventory */
197 /* and the cost price. If there are any errors, then the api will return what error msg code */
198 /* should be displayed. */
199 FUNCTION autosource(p_item_id IN NUMBER,
200 p_category_id IN NUMBER,
201 p_dest_organization_id IN NUMBER,
202 p_dest_subinventory IN VARCHAR2,
203 p_vendor_id IN NUMBER,
204 p_vendor_site_id IN NUMBER,
205 p_not_purchasable_override IN VARCHAR2,
206 p_unit_of_issue IN OUT NOCOPY VARCHAR2,
207 p_source_organization_id OUT NOCOPY NUMBER,
208 p_source_subinventory OUT NOCOPY VARCHAR2,
209 p_sourcing_type OUT NOCOPY VARCHAR2,
210 p_cost_price OUT NOCOPY NUMBER,
211 p_error_msg_code OUT NOCOPY VARCHAR2
212 ) RETURN BOOLEAN
213
214 IS
215
216 l_custom_package_flag BOOLEAN;
217 l_is_purchasable_flag VARCHAR2(1) := 'N';
218 l_set_id NUMBER;
219 l_avail_quantity NUMBER;
220 l_source_type NUMBER;
221 l_source_organization_id NUMBER;
222 l_source_subinventory VARCHAR2(10);
223 l_sourcing_rule_exist_err BOOLEAN := TRUE;
224 l_is_item_assigned_err BOOLEAN := TRUE;
225 l_is_ship_network_assigned_err BOOLEAN := TRUE;
226 --bug 2986842
227 l_is_item_shippable_err BOOLEAN := TRUE;
228 l_count NUMBER := 0;
229 l_first_source_org_id NUMBER := -9999;
230
231 l_vendor_id NUMBER;
232 l_vendor_site_id NUMBER;
233
234 l_procedure_name VARCHAR2(10) := 'AUTOSOURCE';
235
236 cursor c_sourcing is
237 SELECT ALL_SOURCES_V.SOURCE_ORGANIZATION_ID,
238 ALL_SOURCES_V.SOURCE_TYPE,
239 ALL_SOURCES_V.VENDOR_ID,
240 ALL_SOURCES_V.VENDOR_SITE_ID
241 FROM
242 (
243 SELECT SOURCE_ORG.SOURCE_ORGANIZATION_ID,
244 SOURCE_ORG.SOURCE_TYPE,
245 SOURCE_ORG.VENDOR_ID,
246 SOURCE_ORG.VENDOR_SITE_ID,
247 SOURCE_ORG.RANK,
248 SOURCE_ORG.ALLOCATION_PERCENT,
249 3 SOURCING_LEVEL
250 FROM MRP_SR_ASSIGNMENTS MSRA,
251 MTL_ITEM_CATEGORIES CAT,
252 MRP_SOURCING_RULES MSR,
253 MRP_SR_RECEIPT_ORG RECEIPT_ORG,
254 MRP_SR_SOURCE_ORG SOURCE_ORG,
255 MTL_INTERORG_SHIP_METHODS SHIP
256 WHERE CAT.CATEGORY_ID = MSRA.CATEGORY_ID
257 AND CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID
258 AND CAT.ORGANIZATION_ID = NVL(SHIP.TO_ORGANIZATION_ID, CAT.ORGANIZATION_ID)
259 AND SHIP.FROM_ORGANIZATION_ID (+) = SOURCE_ORG.SOURCE_ORGANIZATION_ID
260 AND SHIP.SHIP_METHOD (+) = SOURCE_ORG.SHIP_METHOD
261 AND SOURCE_ORG.SR_RECEIPT_ID = RECEIPT_ORG.SR_RECEIPT_ID
262 AND RECEIPT_ORG.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
263 AND CAT.ORGANIZATION_ID = NVL(RECEIPT_ORG.RECEIPT_ORGANIZATION_ID, CAT.ORGANIZATION_ID)
264 AND MSR.STATUS = 1
265 AND MSR.SOURCING_RULE_ID = MSRA.SOURCING_RULE_ID
266 AND MSRA.ASSIGNMENT_TYPE = 5
267 AND MSRA.ASSIGNMENT_SET_ID = l_set_id
268 AND MSRA.CATEGORY_ID = p_category_id
269 AND CAT.ORGANIZATION_ID = p_dest_organization_id
270 AND (SOURCE_ORG.SOURCE_TYPE = 1 OR
271 (SOURCE_ORG.SOURCE_TYPE = 3 AND
272 'Y' = l_is_purchasable_flag AND
273 SOURCE_ORG.VENDOR_ID = p_vendor_id AND
274 NVL(SOURCE_ORG.VENDOR_SITE_ID, p_vendor_site_id) = p_vendor_site_id))
275 AND SYSDATE BETWEEN RECEIPT_ORG.EFFECTIVE_DATE and NVL(RECEIPT_ORG.DISABLE_DATE, SYSDATE+1)
276
277 UNION ALL
278
279 SELECT SOURCE_ORGANIZATION_ID, SOURCE_TYPE, VENDOR_ID, VENDOR_SITE_ID,
280 RANK, ALLOCATION_PERCENT, SOURCING_LEVEL
281 FROM MRP_ITEM_SOURCING_LEVELS_V
282 WHERE ASSIGNMENT_TYPE <> 5
283 AND INVENTORY_ITEM_ID = p_item_id
284 AND ORGANIZATION_ID = p_dest_organization_id
285 AND ASSIGNMENT_SET_ID = l_set_id
286 AND (SOURCE_TYPE = 1 OR
290 NVL(VENDOR_SITE_ID, p_vendor_site_id) = p_vendor_site_id))
287 (SOURCE_TYPE = 3 AND
288 'Y' = l_is_purchasable_flag AND
289 VENDOR_ID = p_vendor_id AND
291 AND SYSDATE BETWEEN EFFECTIVE_DATE AND NVL(DISABLE_DATE, SYSDATE+1)
292 ) ALL_SOURCES_V
293 ORDER BY ALL_SOURCES_V.SOURCING_LEVEL ASC,
294 ALL_SOURCES_V.ALLOCATION_PERCENT DESC,
295 NVL(ALL_SOURCES_V.RANK, 9999) ASC;
296
297 cursor c_avail_quantity is
298 SELECT msub.secondary_inventory_name,
299 (nvl(mos.total_qoh,0) - sum(nvl(mrs.primary_reservation_quantity,0))) avail_quantity
300 FROM mtl_secondary_inventories msub,
301 mtl_onhand_sub_v mos,
302 mtl_reservations mrs,
303 mtl_system_items msi
304 WHERE msub.organization_id = l_source_organization_id -- bug 5470125, do not bind p_source_organization_id
305 and msi.organization_id = l_source_organization_id
306 and msi.inventory_item_id = p_item_id
307 and (trunc(sysdate) < nvl(msub.disable_date, trunc(sysdate + 1)))
308 and msub.quantity_tracked = 1
309 and msub.secondary_inventory_name = mos.subinventory_code (+)
310 and mos.inventory_item_id = mrs.inventory_item_id (+)
311 and mos.organization_id = mrs.organization_id (+)
312 and mos.subinventory_code = mrs.subinventory_code (+)
313 and mos.inventory_item_id (+) = p_item_id
314 and mos.organization_id (+) = l_source_organization_id
315 and (msi.restrict_subinventories_code = 2
316 or (msi.restrict_subinventories_code = 1
317 and exists (select null
318 from mtl_item_sub_inventories mis
319 where mis.organization_id = msi.organization_id
320 and mis.inventory_item_id = msi.inventory_item_id
321 and mis.secondary_inventory = mos.subinventory_code)
322 )
323 )
324 and msub.reservable_type=1 -- bug 2986842 need to restrict to reservable subinventory
325 -- otherwise po creation will fail
326 GROUP BY msub.secondary_inventory_name, mos.total_qoh
327 ORDER BY avail_quantity DESC, msub.secondary_inventory_name ASC;
328
329 BEGIN
330
331 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
332
333 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
334 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name, 'Start Autosource');
335 END IF;
336
337 p_error_msg_code := '';
338
339 if is_internal_orderable(p_item_id,p_dest_organization_id) = 0 then
340 p_error_msg_code := 'ICX_POR_NOT_ORDERABLE_ERROR';
341 return FALSE;
342 end if;
343
344 l_custom_package_flag := por_autosource_custom_pkg.autosource(p_item_id,
345 p_category_id,
346 p_dest_organization_id,
347 p_dest_subinventory,
348 p_vendor_id,
349 p_vendor_site_id,
350 p_not_purchasable_override,
351 p_unit_of_issue,
352 p_source_organization_id,
353 p_source_subinventory,
354 p_sourcing_type,
355 p_cost_price,
356 p_error_msg_code);
357 -- check if the autosource logic was customized
358 if l_custom_package_flag = TRUE then
359
360 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
361 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name, 'Return Custom');
362 END IF;
363
364 return TRUE;
365 end if;
366
367 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
368 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME ||
369 l_procedure_name, 'Item ID: ' || to_char(p_item_id));
370 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME ||
371 l_procedure_name, 'Category ID: ' || to_char(p_category_id));
372 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME ||
373 l_procedure_name, 'Dest Org ID: ' || to_char(p_dest_organization_id));
374 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME ||
375 l_procedure_name, 'Dest Subinv: ' || p_dest_subinventory);
376 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME ||
377 l_procedure_name, 'Vendor ID: ' || to_char(p_vendor_id));
378 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME ||
379 l_procedure_name, 'Vendor Site ID: ' || to_char(p_vendor_site_id));
380 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME ||
381 l_procedure_name, 'Not Purchasable Override: ' || p_not_purchasable_override);
382 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME ||
383 l_procedure_name, 'Unit of Issue: ' || p_unit_of_issue);
384 END IF;
385
386 -- first find the MRP: Assignment Set profile value
387 l_set_id := to_number(fnd_profile.value('MRP_DEFAULT_ASSIGNMENT_SET'));
388
389 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
390 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME ||
394 -- get the unit of issue of the destination org
391 l_procedure_name, 'Assignment Set ID: ' || to_char(l_set_id));
392 END IF;
393
395 p_unit_of_issue := get_unit_of_issue(p_item_id,p_dest_organization_id);
396
397 if l_set_id is null then
398 p_error_msg_code := 'ICX_POR_SRC_SETUP_INC';
399 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
400 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name, 'Return F');
401 END IF;
402 return FALSE;
403 end if;
404
405 -- check if the item is purchasable
406 -- if the not purchasable override flag is set to true, then the item cannot be purchasable.
407 if p_not_purchasable_override = 'N' and is_item_purchasable(p_item_id, p_dest_organization_id) = 1 then
408 l_is_purchasable_flag := 'Y';
409 else
410 l_is_purchasable_flag := 'N';
411 end if;
412
413 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
414 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME ||
415 l_procedure_name, 'Is Purchasable Flag: ' || l_is_purchasable_flag);
416 END IF;
417
418 -- run the sourcing query
419 OPEN c_sourcing;
420 loop fetch c_sourcing into
421 l_source_organization_id,
422 l_source_type,
423 l_vendor_id,
424 l_vendor_site_id;
425 exit when c_sourcing%NOTFOUND;
426
427 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
428 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME ||
429 l_procedure_name, 'Source Org ID: ' || to_char(l_source_organization_id));
430 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME ||
431 l_procedure_name, 'Source Type: ' || l_source_type);
432 END IF;
433
434 if PO_ASL_SV.check_asl_action('2_SOURCING',l_vendor_id,
435 l_vendor_site_id, p_item_id, -1,
436 p_dest_organization_id ) <> 0 then
437
438 -- if the sourcing rules say to pick the supplier, return supplier
439 if l_source_type = 3 then
440 p_sourcing_type := 'SUPPLIER';
441 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
442 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME ||
443 l_procedure_name, 'Return T ' || p_sourcing_type);
444 END IF;
445 return true;
446 end if;
447
448 l_sourcing_rule_exist_err := FALSE;
449
450 -- check if the item is assigned to this particular source org
451 if is_item_assigned(p_item_id, l_source_organization_id) = 1 then
452
453 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
454 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME ||
455 l_procedure_name, 'Is Item Assigned: 1');
456 END IF;
457
458 l_is_item_assigned_err := FALSE;
459 -- check if there is a valid shipping network betweem the destination org and this particular source org
460 if is_valid_shipping_network(l_source_organization_id, p_dest_organization_id) = 1 then
461
462 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
463 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME ||
464 l_procedure_name, 'Is Valid Shipping Network: 1');
465 END IF;
466
467 l_is_ship_network_assigned_err := FALSE;
468 -- check if the item flags are correctly set in this particular source org
469 if is_item_shippable(p_item_id, l_source_organization_id) = 1 then
470
471 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
472 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME ||
473 l_procedure_name, 'Is Item Shippable: 1');
474 END IF;
475
476 l_is_item_shippable_err := FALSE;
477
478 PO_REQ_LINES_SV1.get_cost_price(p_item_id,l_source_organization_id,p_unit_of_issue,p_cost_price);
479
480 -- Explicitly rounding the price as UI automatically rounds it to 10 digits. This is treated as a
481 -- price change and charge account is regenerated. Hence, rounding it off to 10 to prevent the same
482 p_cost_price := round(p_cost_price,10);
483
484 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
485 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME ||
486 l_procedure_name, 'Cost Price:' || to_char(p_cost_price));
487 END IF;
488
489 OPEN c_avail_quantity;
490 fetch c_avail_quantity into
491 l_source_subinventory,
492 l_avail_quantity;
493 close c_avail_quantity;
494
495 -- if the available quantity is greather than 0
496 -- default to this source org and subinventory
497 -- otherwise keep search the sourcing rule
498
499 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
500 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME ||
501 l_procedure_name, 'Avail Qty:' || to_char(l_avail_quantity));
502 END IF;
503
504 if l_avail_quantity > 0 then
505 p_sourcing_type := 'INTERNAL';
506 p_source_organization_id := l_source_organization_id;
507 p_source_subinventory := l_source_subinventory;
508 close c_sourcing;
512 END IF;
509 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
510 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME ||
511 l_procedure_name, 'Return T ' || p_sourcing_type);
513 return true;
514 end if;
515
516 --save the first record so if the avail qty is 0 in all subinv do not
517 --populate the subinv and default the first source org if there is no error
518
519 if ( (l_count = 0) and not (l_sourcing_rule_exist_err or l_is_item_assigned_err or l_is_ship_network_assigned_err or l_is_item_shippable_err) ) then
520
521 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
522 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME ||
523 l_procedure_name, 'Count = 0');
524 END IF;
525
526 l_first_source_org_id := l_source_organization_id ;
527 end if;
528 l_count := l_count + 1;
529 end if;
530 end if;
531 end if;
532 end if;
533 end loop;
534 if c_sourcing%ISOPEN then
535 close c_sourcing;
536 end if;
537
538 -- if l_count > 0 and l_first_source_org_id is not -9999 then
539 -- it means that a source org was returned but no sub inv is there
540 if ( ( l_count > 0) and (l_first_source_org_id <> -9999) ) then
541 p_sourcing_type := 'INTERNAL';
542 p_source_organization_id := l_first_source_org_id;
543 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
544 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME ||
545 l_procedure_name, 'Return T ' || p_sourcing_type);
546 END IF;
547 return TRUE;
548 end if;
549
550 -- if we didn't find an internal source and the item is purchasable, return Supplier
551 if (l_is_purchasable_flag = 'Y') then
552 p_sourcing_type := 'SUPPLIER';
553 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
554 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME ||
555 l_procedure_name, 'Return T ' || p_sourcing_type);
556 END IF;
557 return TRUE;
558 end if;
559
560 p_sourcing_type := null;
561
562 -- if we didn't find an internal source and the item is strictly internally
563 -- orderable, select error msg code and return false
564 if l_sourcing_rule_exist_err then
565 p_error_msg_code := 'ICX_POR_SRC_RULE_NOEXIST';
566 elsif l_is_item_assigned_err then
567 p_error_msg_code := 'ICX_POR_NO_INT_SOURCES_ERR';
568 elsif l_is_ship_network_assigned_err then
569 p_error_msg_code := 'ICX_POR_NO_INT_SOURCES_ERR';
570 elsif l_is_item_shippable_err then
571 p_error_msg_code := 'ICX_POR_SRC_ITEM_SHIP_ERR';
572 else
573 p_error_msg_code := 'ICX_POR_NO_INT_SOURCES_ERR';
574 end if;
575
576 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
577 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME ||
578 l_procedure_name, 'Return F');
579 END IF;
580
581 return FALSE;
582
583 EXCEPTION
584 WHEN OTHERS THEN
585 p_error_msg_code := 'Exception';
586 if c_sourcing%ISOPEN then
587 close c_sourcing;
588 end if;
589 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
590 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME ||
591 l_procedure_name, 'Exception Return F');
592 END IF;
593 RETURN FALSE;
594
595 END autosource;
596
597 END POR_AUTOSOURCE_UTIL_PKG; -- package
[Home] [Help]