1 PACKAGE BODY inv_utilities AS
2 /* $Header: INVUTILB.pls 120.15.12020000.3 2013/01/29 18:50:00 avrose ship $ */
3 PROCEDURE do_sql(p_sql_stmt IN VARCHAR2) IS
4 cursor_id INTEGER;
5 return_val INTEGER;
6 sql_stmt VARCHAR2(8192);
7 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
8 BEGIN
9 -- set sql statement
10 sql_stmt := p_sql_stmt;
11 -- open a cursor
12 cursor_id := DBMS_SQL.open_cursor;
13 -- parse sql statement
14 DBMS_SQL.parse(cursor_id, sql_stmt, DBMS_SQL.v7);
15 -- execute statement
16 return_val := DBMS_SQL.EXECUTE(cursor_id);
17 -- close cursor
18 DBMS_SQL.close_cursor(cursor_id);
19 END do_sql;
20
21 /*********************************** DEPRECATED *************************************
22 *********************************** DEPRECATED *************************************
23 *********************************** DEPRECATED *************************************
24 * Procedure OBSOLETED. Use INV_PICK_SLIP_REPORT.RUN_DETAIL_ENGINE for Future use. *
25 * THIS PROCEDURE WILL NOT BE SUPPORTED ANY MORE *
26 *********************************** DEPRECATED *************************************
27 *********************************** DEPRECATED *************************************
28 *********************************** DEPRECATED *************************************/
29 --Added NOCOPY hint to p_detail_status OUT parameter
30 --to comply with GSCC File.Sql.39 standard Bug:4410848
31 PROCEDURE run_detail_engine(
32 p_detail_status OUT NOCOPY VARCHAR2
33 , p_org_id IN NUMBER
34 , p_move_order_type IN NUMBER
35 , p_transfer_order IN VARCHAR2
36 , p_source_subinv IN VARCHAR2
37 , p_source_locid IN NUMBER
38 , p_dest_subinv IN VARCHAR2
39 , p_dest_locid IN NUMBER
40 , p_requested_by IN NUMBER
41 , p_plan_tasks IN BOOLEAN
42 , p_pick_slip_group_rule_id IN NUMBER
43 ) IS
44 v_line_id NUMBER := 0;
45 v_num_of_rows NUMBER := 0;
46 v_detailed_qty NUMBER := 0;
47 v_secondary_detailed_qty NUMBER := NULL; --INVCONV
48 v_return_status VARCHAR2(10);
49 v_msg VARCHAR2(2000);
50 v_count NUMBER;
51 v_rev VARCHAR2(100) := NULL;
52 v_from_loc_id NUMBER := 0;
53 v_to_loc_id NUMBER := 0;
54 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
55 v_lot_number VARCHAR2(80);
56 v_expiration_date DATE;
57 v_transaction_temp_id NUMBER;
58 v_header_id NUMBER;
59 l_serial_flag VARCHAR2(1) := 'F';
60 serial_control_code NUMBER;
61 v_inventory_item_id NUMBER;
62 l_move_order_type NUMBER;
63 l_max_batch NUMBER;
64 l_batch_size NUMBER;
65 /* FP-J PAR Replenishment Count: 3 new variables declared */
66 v_pick_slip_no NUMBER;
67 v_err_msg VARCHAR2(1000);
68 mtrh_header_change_track NUMBER := 0;
69 l_req_msg VARCHAR2(30) := NULL;
70 /* Tracking variable to check the change in the header_id from the set of mtrls
71 fetched. If header_id changes, then update GROUPING_RULE_ID for the header
72 and continue the FOR loop until header_id changes again. */
73
74 /* Bug #2060360
75 * Added NVL for from_subinventory_code to allocate lines
76 * where from sub is not specified.
77 */
78 --bug 2307649
79 --type codes have changed, so we need to handle situation
80 -- where p_move_order_type = 99 (all lines)
81
82 -- kkoothan Bug Fix:2352405
83 -- Added one more column to_account_id
84 -- in the cursor below which is later used to update
85 -- the distribution_account_id of MMTT
86
87 /* Restructured the Following Cursor SQL as part of
88 Performance Fix: 2853526.
89 Removed NVLs around from and to Subinventory Codes and
90 used base tables mtl_txn_request_headers and
91 mtl_txn_request_lines instead of the View mtl_txn_request_lines_v*/
92 /* FP-J PAR Replenishment Counts: Introduced 3 more columns to be fetched
93 viz., header_id, project_id and task_id. Also, the cursor is now ordered by
94 mtrl.header_id so that update of GROUPING_RULE_ID of mtrh (for header_id) is
95 done efficiently knowing the fact that the cursor may fetch multiple lines
96 from same header and across headers. column header_id is used to update GROUPINNG_RULE_ID
97 of MTRH, project_id and task_id are used as input parameters for the new call
98 INV_PR_PICK_SLIP_NUMBER.GET_PICK_SLIP_NUMBER() to generate the pick slip number.*/
99 CURSOR c_move_order_lines IS
100 SELECT mtrl.line_id
101 , mtrl.inventory_item_id
102 , mtrh.move_order_type
103 , mtrl.to_account_id
104 , mtrl.header_id
105 , mtrl.project_id
106 , mtrl.task_id
107 FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh
108 WHERE mtrl.line_status IN(3, 7)
109 AND mtrl.header_id = mtrh.header_id
110 AND mtrl.organization_id = p_org_id
111 AND(
112 (p_move_order_type IN(1, 2)
113 AND mtrh.move_order_type = p_move_order_type)
114 OR(p_move_order_type = 99
115 AND mtrh.move_order_type IN(1, 2))
116 )
117 AND mtrl.quantity > NVL(mtrl.quantity_detailed, 0)
118 AND mtrh.request_number = NVL(p_transfer_order, mtrh.request_number)
119 AND mtrl.created_by = NVL(p_requested_by, mtrl.created_by)
120 AND(p_source_subinv IS NULL
121 OR mtrl.from_subinventory_code = p_source_subinv)
122 AND(p_dest_subinv IS NULL
123 OR mtrl.to_subinventory_code = p_dest_subinv)
124 ORDER BY mtrl.header_id;
125
126 CURSOR c_mmtt IS
127 SELECT transaction_temp_id
128 , subinventory_code
129 , locator_id
130 , transfer_subinventory
131 , transfer_to_location
132 , revision
133 FROM mtl_material_transactions_temp
134 WHERE move_order_line_id = v_line_id
135 AND pick_slip_number IS NULL;
136
137 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
138 BEGIN
139 IF p_org_id IS NULL
140 OR p_move_order_type IS NULL THEN
141 RETURN;
142 END IF;
143
144 --only allocate requisition and replenishment move orders
145 --99 is "All"
146 IF p_move_order_type NOT IN(1, 2, 99) THEN
147 RETURN;
148 END IF;
149
150 l_max_batch := TO_NUMBER(fnd_profile.VALUE('INV_PICK_SLIP_BATCH_SIZE'));
151
152 IF (l_debug = 1) THEN
153 inv_log_util.TRACE('max batch: ' || l_max_batch, 'INV_UTILITIES', 9);
154 END IF;
155
156 IF l_max_batch IS NULL
157 OR l_max_batch <= 0 THEN
158 l_max_batch := 20;
159
160 IF (l_debug = 1) THEN
161 inv_log_util.TRACE('using default batch size', 'INV_UTILITIES', 9);
162 END IF;
163 END IF;
164
165 l_batch_size := 0;
166
167 IF (l_debug = 1) THEN
168 inv_log_util.TRACE('Pick Slip Grouping Rule Id ' || TO_CHAR(p_pick_slip_group_rule_id), 'INV_UTILITIES', 9);
169 END IF;
170
171 --device integration starts
172 IF (inv_install.adv_inv_installed(p_org_id) = TRUE) THEN --for WMS org
173 IF wms_device_integration_pvt.wms_call_device_request IS NULL THEN
174 wms_device_integration_pvt.is_device_set_up(p_org_id,wms_device_integration_pvt.WMS_BE_MO_TASK_ALLOC,v_return_status);
175 END IF;
176 END IF;
177 --device integration end
178
179
180 FOR move_ord_rec IN c_move_order_lines LOOP
181 l_batch_size := l_batch_size + 1;
182 v_line_id := move_ord_rec.line_id;
183 l_move_order_type := move_ord_rec.move_order_type;
184
185 /* FP-J PAR Replenishment Count: Code block to update GROUPING_RULE_ID of mtrh
186 only once per header_id change in the set of mtrls fetched in the cursor.
187 This approach works because cursor is ordered by mtrl.header_id.
188 Implied inline branching between I and J here is to check whether
189 p_pick_slip_group_rule_id IS NULL or not. In FP-I, concurrent program
190 cannot pass p_pick_slip_group_rule_id(hence default NULL). */
191 IF move_ord_rec.header_id <> mtrh_header_change_track
192 AND p_pick_slip_group_rule_id IS NOT NULL THEN
193 mtrh_header_change_track := move_ord_rec.header_id;
194
195 IF (l_debug = 1) THEN
196 inv_log_util.TRACE('Updating MTRH Id ' || TO_CHAR(mtrh_header_change_track), 'INV_UTILITIES', 9);
197 END IF;
198
199 UPDATE mtl_txn_request_headers
200 SET grouping_rule_id = p_pick_slip_group_rule_id
201 WHERE header_id = move_ord_rec.header_id;
202
203 -- Flushing out the Cached Pick Slip Numbers.
204 inv_pr_pick_slip_number.delete_wip_ps_tbl;
205 END IF;
206
207 SELECT serial_number_control_code
208 INTO serial_control_code
209 FROM mtl_system_items
210 WHERE inventory_item_id = move_ord_rec.inventory_item_id
211 AND organization_id = p_org_id;
212
213 IF serial_control_code <> 1 THEN
214 l_serial_flag := 'T';
215 END IF;
216
217 SELECT mtl_material_transactions_s.NEXTVAL
218 INTO v_header_id
219 FROM DUAL;
220
221 inv_replenish_detail_pub.line_details_pub(
222 p_line_id => v_line_id
223 , x_number_of_rows => v_num_of_rows
224 , x_detailed_qty => v_detailed_qty
225 , x_detailed_qty2 => v_secondary_detailed_qty --INVCONV
226 , x_return_status => v_return_status
227 , x_msg_count => v_count
228 , x_msg_data => v_msg
229 , x_revision => v_rev
230 , x_locator_id => v_from_loc_id
231 , x_transfer_to_location => v_to_loc_id
232 , x_lot_number => v_lot_number
233 , x_expiration_date => v_expiration_date
234 , x_transaction_temp_id => v_transaction_temp_id
235 , p_transaction_header_id => v_header_id
236 , p_transaction_mode => NULL
237 , p_move_order_type => l_move_order_type
238 , p_serial_flag => l_serial_flag
239 , p_plan_tasks => p_plan_tasks
240 );
241
242 --INVCONV Added secondary qty
243 UPDATE mtl_txn_request_lines
244 SET quantity_detailed = (nvl(quantity_delivered,0) + v_detailed_qty) -- against bug : 4155230
245 ,secondary_quantity_detailed = DECODE(v_secondary_detailed_qty,0,NULL,v_secondary_detailed_qty)
246 WHERE line_id = v_line_id
247 AND organization_id = p_org_id;
248
249 /* FP-J PAR Replenishment Counts: Implied inline branching b/w I and J is to check
250 if p_pick_slip_group_rule_id is NULL or not. In FP-I, concurrent program cannot pass
251 p_pick_slip_group_rule_id (hence default null) */
252 IF p_pick_slip_group_rule_id IS NOT NULL THEN
253 -- Looping for each allocation of the MO Line for which Pick Slip Number is not stamped.
254 FOR v_mmtt IN c_mmtt LOOP
255 inv_pr_pick_slip_number.get_pick_slip_number(
256 p_pick_grouping_rule_id => p_pick_slip_group_rule_id
257 , p_org_id => p_org_id
258 , p_wip_entity_id => NULL
259 , p_rep_schedule_id => NULL
260 , p_operation_seq_num => NULL
261 , p_dept_id => NULL
262 , p_push_or_pull => NULL
263 , p_supply_subinventory => v_mmtt.transfer_subinventory
264 , p_supply_locator_id => v_mmtt.transfer_to_location
265 , p_project_id => move_ord_rec.project_id
266 , p_task_id => move_ord_rec.task_id
267 , p_src_subinventory => v_mmtt.subinventory_code
268 , p_src_locator_id => v_mmtt.locator_id
269 , p_inventory_item_id => move_ord_rec.inventory_item_id
270 , p_revision => v_mmtt.revision
271 , p_lot_number => NULL
272 , x_pick_slip_number => v_pick_slip_no
273 , x_api_status => v_return_status
274 , x_error_message => v_err_msg
275 );
276 UPDATE mtl_material_transactions_temp
277 SET pick_slip_number = v_pick_slip_no
278 WHERE transaction_temp_id = v_mmtt.transaction_temp_id;
279 END LOOP;
280 END IF;
281
282 -- kkoothan Bug Fix:2352405
283 -- Update the distribution_account_id of MMTT
284 -- from to_account_id of mtl_txn_request_lines_v
285 -- since this was not done previously.After this fix,
286 -- MOs allocated using MO Pick Slip Report too,
287 -- along with manually allocated MO will populate
288 -- the distribution_account_id of MMTT.
289 IF move_ord_rec.to_account_id IS NOT NULL THEN
290 UPDATE mtl_material_transactions_temp
291 SET distribution_account_id = move_ord_rec.to_account_id
292 WHERE move_order_line_id = v_line_id;
293 END IF;
294
295 IF l_batch_size >= l_max_batch THEN
296 COMMIT;
297
298 IF (l_debug = 1) THEN
299 inv_log_util.TRACE('commit', 'INV_UTILITIES', 9);
300 END IF;
301
302 l_batch_size := 0;
303 END IF;
304 END LOOP;
305
306 -- Call Device Integration API to send the details of this
307 -- PickRelease Wave to devices, if it is a WMS organization.
308 -- Note: We don't check for the return condition of this API as
309 -- we let the Move Order Allocation process succeed
310 -- irrespective of DeviceIntegration succeed or fail.
311 if (WMS_INSTALL.check_install
312 (
313 x_return_status => v_return_status,
314 x_msg_count => v_count,
315 x_msg_data => v_msg,
316 p_organization_id => p_org_id
317 ) = TRUE ) then
318 WMS_DEVICE_INTEGRATION_PVT.device_request
319 (p_bus_event => WMS_DEVICE_INTEGRATION_PVT.WMS_BE_MO_TASK_ALLOC,
320 p_call_ctx => WMS_Device_integration_pvt.DEV_REQ_AUTO,
321 p_task_trx_id => NULL,
322 x_request_msg => l_req_msg,
323 x_return_status => v_return_status,
324 x_msg_count => v_count,
325 x_msg_data => v_msg
326 );
327
328 IF (l_debug = 1) THEN
329 inv_log_util.TRACE('Device_API: return stat:'||v_return_status, 'INV_UTILITIES', 9);
330 END IF;
331
332 end if;
333
334
335
336 COMMIT;
337 EXCEPTION
338 WHEN NO_DATA_FOUND THEN
339 v_return_status := 'E';
340 END run_detail_engine;
341
342 --Added NOCOPY hint to table_of_strings OUT parameter to comply
343 --with GSCC File.Sql.39 standard .Bug:4410848
344 PROCEDURE parse_vector(vector_in IN VARCHAR2, delimiter IN VARCHAR2, table_of_strings OUT NOCOPY vector_tabtype) IS
345 delimiter_index NUMBER;
346 string_in VARCHAR2(32767);
347 counter NUMBER;
348 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
349 BEGIN
350 --parse the input vector of strings
351 --by separating the strings that are delimitted by commas
352 string_in := vector_in;
353 delimiter_index := INSTR(string_in, delimiter, 1);
354 counter := 0;
355
356 --dbms_output.put_line('string_in '||string_in);
357 --dbms_output.put_line('delimiter '||delimiter);
358 --dbms_output.put_line('index '||delimiter_index);
359 WHILE delimiter_index > 0 LOOP
360 table_of_strings(counter) := SUBSTR(string_in, 1, delimiter_index - 1);
361 string_in := SUBSTR(string_in, delimiter_index + 1);
362 delimiter_index := INSTR(string_in, delimiter, 1);
363 counter := counter + 1;
364 END LOOP;
365
366 --add last element of string to table
367 table_of_strings(counter) := string_in;
368 END parse_vector;
369
370 -- Bug 12746059: LC Calculation By Item Category
371 -- Function name : get_LCMTrackingFlag
372 -- Type : Group
373 -- Function : Check whether a given item is trackable in LCM.
374 -- Returns 'N' if not trackable.
375 --
376 -- Pre-reqs : None
377 -- Parameters :
378 -- IN : p_inventory_item_id IN NUMBER
379 -- p_organization_id IN NUMBER
380 --
381 -- Version : Current version 1.0
382 --
383 -- Notes :
384 FUNCTION get_LCMTrackingFlag (p_inventory_item_id IN NUMBER,
385 p_organization_id IN NUMBER) RETURN VARCHAR2 IS
386
387 l_lcm_tracking_flag VARCHAR2(1);
388 l_prof_category_set_id NUMBER;
389 l_count_item_category_set NUMBER;
390
391 BEGIN
392
393 l_prof_category_set_id := FND_PROFILE.VALUE('INL_ITEM_CATEGORY_SET');
394
395 IF l_prof_category_set_id IS NOT NULL THEN
396 SELECT COUNT(*)
397 INTO l_count_item_category_set
398 FROM mtl_item_categories
399 WHERE inventory_item_id = p_inventory_item_id
400 AND organization_id = p_organization_id
401 AND category_set_id = l_prof_category_set_id;
402
403 IF NVL(l_count_item_category_set,0) > 0 THEN
404 l_lcm_tracking_flag := 'Y';
405 ELSE
406 l_lcm_tracking_flag := 'N';
407 END IF;
408 ELSE
409 l_lcm_tracking_flag := 'Y';
410 END IF;
411
412 RETURN l_lcm_tracking_flag;
413
414 EXCEPTION
415 WHEN OTHERS THEN
416 RETURN 'N' ;
417 END get_LCMTrackingFlag;
418 -- End Bug 12746059: LC Calculation By Item Category
419
420 /*
421 Added for bug No 7440217
422 PO API for LCM
423 */
424 FUNCTION inv_check_lcm(
425 p_inventory_item_id IN NUMBER,
426 p_ship_to_org_id IN NUMBER,
427 p_consigned_flag IN VARCHAR2,
428 p_outsource_assembly_flag IN VARCHAR2,
429 p_vendor_id IN NUMBER,
430 p_vendor_site_id IN NUMBER,
431 p_po_line_location_id IN NUMBER --Bug#10279800
432 )
433 RETURN VARCHAR2 IS
434 v_stock_enabled_flag VARCHAR2(1);
435 v_lcm_enabled_flag VARCHAR2(1);
436 v_inv_asset_flag VARCHAR2(1);
437 v_vs_ou_id NUMBER; -- bug 9767031
438 v_org_ou_id NUMBER; -- bug 9767031
439 v_drop_ship_flag VARCHAR2(1); --Bug#10279800
440 v_lcm_tracking_flag VARCHAR2(1) := 'N'; -- Bug 12746059: LC Calculation By Item Category
441
442 --13825283
443 v_all_expense_flag varchar2(1) := 'Y';
444
445 --13927039
446 v_emp_flag varchar2(1);
447
448 BEGIN
449 IF NVL(p_consigned_flag, 'N') = 'Y'
450 OR NVL(p_outsource_assembly_flag, 'N') = 'Y'
451 OR NVL(p_inventory_item_id, -9999) = -9999
452 OR NVL(p_ship_to_org_id,-9999) = -9999
453 THEN
454 RETURN 'N';
455 ELSE
456 SELECT NVL(LCM_ENABLED_FLAG, 'N')
457 INTO v_lcm_enabled_flag
458 FROM MTL_PARAMETERS
459 WHERE ORGANIZATION_ID = p_ship_to_org_id;
460
461 IF v_lcm_enabled_flag = 'N' THEN
462 RETURN v_lcm_enabled_flag;
463 END IF;
464 /*Bug#10279800 Add to check drop_ship_flag, return 'N' if
465 drop_ship_flag = 'Y'*/
466 IF nvl(p_po_line_location_id,-1) <> -1 THEN
467 SELECT NVL(DROP_SHIP_FLAG, 'N')
468 INTO v_drop_ship_flag
469 FROM PO_LINE_LOCATIONS_ALL
470 WHERE LINE_LOCATION_ID = p_po_line_location_id;
471
472 IF v_drop_ship_flag = 'Y' THEN
473 RETURN 'N';
474 END IF;
475
476 --13825283
477 begin
478
479 SELECT 'N' INTO v_all_expense_flag
480 FROM dual
481 WHERE EXISTS ( SELECT 1
482 FROM po_distributions_all pod
483 WHERE pod.destination_type_code <> 'EXPENSE'
484 AND pod.line_location_id = p_po_line_location_id);
485
486 exception when others then
487 if v_all_expense_flag = 'Y' then
488 return 'N';
489 end if;
490 end;
491
492
493 END IF;
494 /*Bug#10279800*/
495 SELECT STOCK_ENABLED_FLAG,INVENTORY_ASSET_FLAG
496 INTO v_stock_enabled_flag,v_inv_asset_flag
497 FROM MTL_SYSTEM_ITEMS
498 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
499 AND ORGANIZATION_ID = p_ship_to_org_id;
500
501
502
503
504 --13927039 employee supplier return 'N'
505 IF p_vendor_id IS NOT NULL
506 THEN
507
508 begin
509 v_emp_flag := 'Y';
510 SELECT 'N'
511 INTO v_emp_flag
512 FROM po_vendors
513 WHERE vendor_type_lookup_code = 'EMPLOYEE'
514 AND vendor_id = p_vendor_id;
515
516 IF v_emp_flag = 'N' THEN
517 RETURN 'N';
518 END IF;
519
520 EXCEPTION WHEN OTHERS THEN
521 NULL;
522 END;
523
524
525 END IF;
526
527 IF (NVL(v_stock_enabled_flag, 'N') = 'Y' and NVL(v_inv_asset_flag,'N') = 'Y')
528 THEN
529 -- bug 9767031
530 IF p_vendor_site_id IS NOT NULL THEN
531 SELECT nvl(ORG_ID,-1)
532 INTO v_vs_ou_id
533 FROM PO_VENDOR_SITES_ALL
534 WHERE VENDOR_SITE_ID = p_vendor_site_id;
535
536 SELECT To_number(hoi2.org_information3)
537 INTO v_org_ou_id
538 FROM hr_organization_information hoi2
539 WHERE organization_id = p_ship_to_org_id
540 AND ( hoi2.org_information_context || '' ) = 'Accounting Information';
541
542 IF v_vs_ou_id = v_org_ou_id THEN
543 -- RETURN 'Y'; -- Bug 12746059: LC Calculation By Item Category
544 v_lcm_enabled_flag := 'Y'; -- Bug 12746059: LC Calculation By Item Category
545 ELSE
546 RETURN 'N';
547 END IF;
548 ELSE
549 -- RETURN 'Y'; -- Bug 12746059: LC Calculation By Item Category
550 v_lcm_enabled_flag := 'Y'; -- Bug 12746059: LC Calculation By Item Category
551 END IF;
552 ELSE
553 /* bug 9849579 fixed. Return 'N' if stockable_flag or inv_asset_flag is no */
554 RETURN 'N';
555 END IF;
556 END IF;
557
558 -- Bug 12746059: LC Calculation By Item Category
559
560 IF (v_lcm_enabled_flag = 'Y') THEN
561
562 v_lcm_tracking_flag := get_LCMTrackingFlag(
563 p_inventory_item_id => p_inventory_item_id,
564 p_organization_id => p_ship_to_org_id);
565
566 IF (v_lcm_tracking_flag = 'Y') THEN
567 RETURN 'Y';
568 ELSE
569 RETURN 'N';
570 END IF;
571 ELSE
572 RETURN 'N';
573 END IF;
574 -- End Bug 12746059: LC Calculation By Item Category
575
576 EXCEPTION
577 WHEN OTHERS THEN
578 RETURN 'N' ;
579 -- end of bug 9767031
580 END inv_check_lcm;
581 /*
582 END for bug No 7440217
583 PO API for LCM
584 */
585
586
587
588 FUNCTION get_conc_segments(x_org_id IN NUMBER, x_loc_id IN NUMBER)
589 RETURN VARCHAR2 IS
590 x_conc_segs VARCHAR2(2000) := NULL;
591 v_loc_str VARCHAR2(2000) := NULL;
592 v_proj_name VARCHAR2(50) := NULL;
593 v_task_name VARCHAR2(50) := NULL;
594 v_append VARCHAR2(1000) := NULL;
595 v_parse_str VARCHAR2(3000) := NULL;
596 v_num NUMBER;
597 v_cnt NUMBER := 0;
598 v_proj_ref_enabled NUMBER := NULL;
599 v_flex_code VARCHAR2(5) := 'MTLL';
600 v_flex_num NUMBER;
601 v_seg19_f BOOLEAN := FALSE;
602 v_seg20_f BOOLEAN := FALSE;
603 v_delim VARCHAR2(1) := NULL;
604 dsql_cur NUMBER;
605 rows_processed NUMBER;
606 str1 VARCHAR2(15) := NULL;
607 d_data_str VARCHAR2(1000) := NULL;
608
609 CURSOR cur1(flex_code VARCHAR2) IS
610 SELECT a.application_column_name
611 FROM fnd_id_flex_segments_vl a
612 WHERE a.application_id = 401
613 AND a.id_flex_code = flex_code
614 AND a.id_flex_num = (SELECT id_flex_num
615 FROM fnd_id_flex_structures
616 WHERE id_flex_code = flex_code)
617 AND a.enabled_flag = 'Y'
618 AND a.display_flag = 'Y'
619 ORDER BY a.segment_num;
620
621 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
622 BEGIN
623 SELECT id_flex_num
624 INTO v_flex_num
625 FROM fnd_id_flex_structures
626 WHERE id_flex_code = 'MTLL';
627
628 SELECT project_reference_enabled
629 INTO v_proj_ref_enabled
630 FROM mtl_parameters
631 WHERE organization_id = x_org_id;
632
633 v_delim := fnd_flex_ext.get_delimiter('INV', v_flex_code, v_flex_num);
634 str1 := '||''' || v_delim || '''||';
635
636 FOR cur2 IN cur1(v_flex_code) LOOP
637 IF v_proj_ref_enabled = 1
638 AND(cur2.application_column_name = 'SEGMENT19'
639 OR cur2.application_column_name = 'SEGMENT20') THEN
640 IF cur2.application_column_name = 'SEGMENT19' THEN
641 BEGIN
642 v_seg19_f := TRUE;
643
644 -- bug 4662395 set the profile mfg_organization_id so
645 -- the call to MTL_PROJECT_V will return data.
646
647 FND_PROFILE.put('MFG_ORGANIZATION_ID',x_org_id);
648
649 SELECT DISTINCT project_number
650 INTO v_proj_name
651 FROM mtl_project_v
652 WHERE project_id = (SELECT NVL(TO_NUMBER(segment19), 0)
653 FROM mtl_item_locations
654 WHERE inventory_location_id = x_loc_id
655 AND organization_id = x_org_id);
656 EXCEPTION
657 WHEN OTHERS THEN
658 v_proj_name := NULL;
659 END;
660 ELSIF cur2.application_column_name = 'SEGMENT20' THEN
661 BEGIN
662 v_seg20_f := TRUE;
663
664 SELECT DISTINCT a.task_number
665 INTO v_task_name
666 FROM mtl_task_v a
667 WHERE a.task_id = (SELECT NVL(TO_NUMBER(segment20), 0)
668 FROM mtl_item_locations
669 WHERE inventory_location_id = x_loc_id
670 AND organization_id = x_org_id)
671 AND a.project_id = (SELECT NVL(TO_NUMBER(segment19), a.project_id)
672 FROM mtl_item_locations
673 WHERE inventory_location_id = x_loc_id
674 AND organization_id = x_org_id);
675 EXCEPTION
676 WHEN OTHERS THEN
677 v_task_name := NULL;
678 END;
679 END IF;
680 END IF;
681 END LOOP;
682
683 FOR cur2 IN cur1(v_flex_code) LOOP
684 IF v_loc_str IS NOT NULL THEN
685 v_append := v_loc_str || str1;
686 ELSE
687 v_append := NULL;
688 END IF;
689
690 /*Bug#4278601
691 v_loc_str needs to contain the locator segments in the order it was defined,
692 including project and task segments.*/
693 IF (CUR2.APPLICATION_COLUMN_NAME = 'SEGMENT19') THEN
694 v_loc_str := v_append||''''||v_proj_name||'''';
695 ELSIF (CUR2.APPLICATION_COLUMN_NAME = 'SEGMENT20') THEN
696 v_loc_str := v_append||''''||v_task_name||'''';
697 ELSIF (cur2.application_column_name <> 'SEGMENT19'
698 AND cur2.application_column_name <> 'SEGMENT20') THEN
699 v_loc_str := v_append || cur2.application_column_name;
700 END IF;
701 END LOOP;
702
703 IF v_loc_str IS NOT NULL THEN
704 v_parse_str :=
705 'select ' || v_loc_str || ' from mtl_item_locations where inventory_location_id = :loc_id ' || ' and organization_id = :org_id';
706 dsql_cur := DBMS_SQL.open_cursor;
707 DBMS_SQL.parse(dsql_cur, v_parse_str, DBMS_SQL.native);
708 DBMS_SQL.define_column(dsql_cur, 1, d_data_str, 800);
709 DBMS_SQL.bind_variable(dsql_cur, 'loc_id', x_loc_id);
710 DBMS_SQL.bind_variable(dsql_cur, 'org_id', x_org_id);
711 rows_processed := DBMS_SQL.EXECUTE(dsql_cur);
712
713 LOOP
714 IF (DBMS_SQL.fetch_rows(dsql_cur) > 0) THEN
715 DBMS_SQL.column_value(dsql_cur, 1, d_data_str);
716 ELSE
717 -- No more rows in cursor
718 DBMS_SQL.close_cursor(dsql_cur);
719 EXIT;
720 END IF;
721 END LOOP;
722
723 IF DBMS_SQL.is_open(dsql_cur) THEN
724 DBMS_SQL.close_cursor(dsql_cur);
725 END IF;
726 END IF;
727
728 /*Bug 4278601
729 Comment out this section because now d_data_str will have the complete locator information
730 in the order it was defined.
731 IF v_seg19_f
732 AND v_seg20_f THEN
733 x_conc_segs := d_data_str || v_delim || v_proj_name || v_delim || v_task_name;
734 ELSIF v_seg19_f THEN
735 x_conc_segs := d_data_str || v_delim || v_proj_name;
736 ELSIF v_seg20_f THEN
737 x_conc_segs := d_data_str || v_delim || v_task_name;
738 ELSE
739 x_conc_segs := d_data_str;
740 END IF;*/
741
742 x_conc_segs := d_data_str; --Bug#4278601
743
744 RETURN x_conc_segs;
745 EXCEPTION
746 WHEN OTHERS THEN
747 x_conc_segs := NULL;
748 RETURN x_conc_segs;
749 END get_conc_segments;
750
751 /*
752 Added for bug No :2326247.
753 Calculates the item cost based on costing.
754 */
755 --Added NOCOPY hint to v_item_cost OUT parameter to comply
756 --with GSCC standard File.Sql.39. Bug:4410848
757 PROCEDURE get_item_cost(v_org_id IN NUMBER, v_item_id IN NUMBER, v_locator_id IN NUMBER, v_item_cost OUT NOCOPY NUMBER) IS
758 -- For standard costed orgs, get the item cost with the common
759 -- cost group ID = 1. For average costed orgs, use the org's
760 -- default cost group ID
761 -- Bug # 2180251: All primary costing methods not equal to 1 should
762 -- also be considered as an average costed org
763 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
764 --Forward port bug7037252 of bugs 6349028 and 6343400
765 --Added DECODE on wms_enabled_flag in the 2nd SELECT stmt.
766 --For WMS enabled average costing org, the org level CG may not be same as the onhand CG because you can have CG rules.
767 l_wms_enabled_flag VARCHAR2(1) := 'N';
768 BEGIN
769 --Bug7037252/6349028/6343400. Added following SELECT
770 SELECT NVL(mp.wms_enabled_flag,'N')
771 INTO l_wms_enabled_flag
772 FROM MTL_PARAMETERS mp
773 WHERE mp.organization_id=v_org_id ;
774 SELECT NVL(ccicv.item_cost, 0)
775 INTO v_item_cost
776 FROM cst_cg_item_costs_view ccicv, mtl_parameters mp
777 WHERE v_locator_id IS NULL
778 AND ccicv.organization_id = v_org_id
779 AND ccicv.inventory_item_id = v_item_id
780 AND ccicv.organization_id = mp.organization_id
781 AND ccicv.cost_group_id = DECODE(mp.primary_cost_method, 1, 1, NVL(mp.default_cost_group_id, 1))
782 UNION ALL
783 SELECT NVL(ccicv.item_cost, 0)
784 FROM mtl_item_locations mil, cst_cg_item_costs_view ccicv, mtl_parameters mp
785 WHERE v_locator_id IS NOT NULL
786 AND mil.organization_id = v_org_id
787 AND mil.inventory_location_id = v_locator_id
788 AND mil.project_id IS NULL
789 AND ccicv.organization_id = mil.organization_id
790 AND ccicv.inventory_item_id = v_item_id
791 AND ccicv.organization_id = mp.organization_id
792 AND ccicv.cost_group_id = DECODE(mp.primary_cost_method, 1, 1, DECODE(l_wms_enabled_flag,'Y',ccicv.cost_group_id, NVL(mp.default_cost_group_id, 1)))
793 UNION ALL
794 SELECT NVL(ccicv.item_cost, 0)
795 FROM mtl_item_locations mil, mrp_project_parameters mrp, cst_cg_item_costs_view ccicv, mtl_parameters mp
796 WHERE v_locator_id IS NOT NULL
797 AND mil.organization_id = v_org_id
798 AND mil.inventory_location_id = v_locator_id
799 AND mil.project_id IS NOT NULL
800 AND mrp.organization_id = mil.organization_id
801 AND mrp.project_id = mil.project_id
802 AND ccicv.organization_id = mil.organization_id
803 AND ccicv.inventory_item_id = v_item_id
804 AND ccicv.organization_id = mp.organization_id
805 AND ccicv.cost_group_id = DECODE(mp.primary_cost_method, 1, 1, NVL(mrp.costing_group_id, 1));
806 EXCEPTION
807 WHEN NO_DATA_FOUND THEN
808 v_item_cost := -999;
809 WHEN TOO_MANY_ROWS THEN --Bug --Forward port 7037252/6349028/6343400
810 IF (l_wms_enabled_flag = 'Y' ) THEN
811 --For WMS org with average costing, there may be more than one rows in cst_cg_item_costs_view.
812 --In this case, the correct cost will be stamped from INV_COST_GROUP_PVT after obtaining exact cost group.
813 v_item_cost := 1; --This is hard-coded so that we can retrieve the percent value in INV_COST_GROUP_PVT.
814 ELSE
815 v_item_cost := -999; --This should result in error for non-wms orgs.
816 END IF;
817 END get_item_cost;
818
819 PROCEDURE get_sales_order_id (
820 p_sales_order_number NUMBER ,
821 p_sales_order_type VARCHAR2,
822 p_sales_order_source VARCHAR2,
823 p_concatenated_segments OUT NOCOPY VARCHAR2,
824 p_source_id OUT NOCOPY NUMBER) IS
825 /*
826 ||==============================================================================||
827 || Created By : Nalin Kumar ||
828 || Created On : 03-May-2005 ||
829 || Purpose : This procedure will get called from TMO. This procedure will ||
830 || return the Sales Order ID and Concatenated Segments. Created ||
831 || as part of Depot Repair Enh. Bug# 4346443 ||
832 || ||
833 || Known limitations, enhancements or remarks : ||
834 || Change History : ||
835 || Who When What ||
836 || (reverse chronological order - newest change first) ||
837 ||==============================================================================||
838 */
839 l_delimiter VARCHAR2(1) := NULL;
840 l_segment_array FND_FLEX_EXT.SegmentArray;
841 l_n_segments NUMBER;
842 l_val BOOLEAN;
843 BEGIN
844 p_source_id := NULL;
845 --Get the Delimiter...
846 l_delimiter := fnd_flex_apis.get_segment_delimiter(
847 x_application_id => 401,
848 x_id_flex_code => 'MKTS',
849 x_id_flex_num => '101');
850
851 l_segment_array(1) := p_sales_order_number;
852 l_segment_array(2) := p_sales_order_type;
853 l_segment_array(3) := p_sales_order_source;
854 l_n_segments := 3;
855
856 --Get Concatenated Segments...
857 p_concatenated_segments := fnd_flex_ext.concatenate_segments(l_n_segments, l_segment_array, l_delimiter);
858
859 --Check for the combination...
860 l_val := fnd_flex_keyval.validate_segs(
861 operation => 'FIND_COMBINATION',
862 appl_short_name => 'INV',
863 key_flex_code => 'MKTS',
864 structure_number => '101',
865 concat_segments => p_concatenated_segments,
866 validation_date => SYSDATE);
867
868 --Get the combination id (source_id)...
869 IF l_val THEN
870 p_source_id := fnd_flex_keyval.combination_id;
871 END IF;
872 END get_sales_order_id ;
873
874 /*
875 This API was created as a part of MUOM fulfillment ER.
876 This will accept source_line_id as input paramter and will return the fulfillment_base
877 by calling API OE_DUAL_UOM_UTIL.get_fulfillment_base.
878 */
879 PROCEDURE get_inv_fulfillment_base(
880 p_source_line_id IN NUMBER,
881 p_demand_source_type_id IN NUMBER,
882 p_org_id IN NUMBER,
883 x_fulfillment_base OUT NOCOPY VARCHAR2) IS
884
885 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
886 l_is_wms_enabled VARCHAR2 (2) := 'N';
887 BEGIN
888
889 IF (inv_cache.set_org_rec(p_org_id)) THEN
890 l_is_wms_enabled := inv_cache.org_rec.WMS_ENABLED_FLAG;
891 IF (l_debug = 1) THEN
892 inv_log_util.TRACE('WMS enabled? : '||l_is_wms_enabled , 'INV_UTILITIES', 9);
893 END IF;
894 END IF;
895
896 IF l_is_wms_enabled = 'Y' THEN
897 IF p_demand_source_type_id IN (2,8) THEN
898 x_fulfillment_base := Nvl(OE_DUAL_UOM_UTIL.get_fulfillment_base(p_source_line_id), 'P');
899 IF (l_debug = 1) THEN
900 inv_log_util.TRACE('The fulfillment Base: '||x_fulfillment_base , 'INV_UTILITIES', 9);
901 END IF;
902 ELSE
903 x_fulfillment_base := 'P';
904 END IF;
905 ELSE
906 x_fulfillment_base := 'P';
907 END IF;
908 END get_inv_fulfillment_base;
909
910 END inv_utilities;
[Home] [Help]