1 PACKAGE BODY inv_utilities AS
2 /* $Header: INVUTILB.pls 120.6.12010000.5 2009/01/15 09:04:17 hjogleka 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
371 /*
372 Added for bug No 7440217
373 PO API for LCM
374 */
375 FUNCTION inv_check_lcm(
376 p_inventory_item_id IN NUMBER,
377 p_ship_to_org_id IN NUMBER,
378 p_consigned_flag IN VARCHAR2,
379 p_outsource_assembly_flag IN VARCHAR2,
380 p_vendor_id IN NUMBER,
381 p_vendor_site_id IN NUMBER
382 )
383 RETURN VARCHAR2 IS
384 v_stock_enabled_flag VARCHAR2(1);
385 v_lcm_enabled_flag VARCHAR2(1);
386 v_inv_asset_flag VARCHAR2(1);
387 BEGIN
388 IF NVL(p_consigned_flag, 'N') = 'Y'
389 OR NVL(p_outsource_assembly_flag, 'N') = 'Y'
390 OR NVL(p_inventory_item_id, -9999) = -9999
391 THEN
392 RETURN 'N';
393 ELSE
394 SELECT STOCK_ENABLED_FLAG,INVENTORY_ASSET_FLAG
395 INTO v_stock_enabled_flag,v_inv_asset_flag
396 FROM MTL_SYSTEM_ITEMS
397 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
398 AND ORGANIZATION_ID = p_ship_to_org_id;
399
400 IF (NVL(v_stock_enabled_flag, 'N') = 'Y' and NVL(v_inv_asset_flag,'N') = 'Y')
401 THEN
402 SELECT NVL(LCM_ENABLED_FLAG, 'N')
403 INTO v_lcm_enabled_flag
404 FROM MTL_PARAMETERS
405 WHERE ORGANIZATION_ID = p_ship_to_org_id;
406
407 RETURN v_lcm_enabled_flag;
408 ELSE
409 RETURN 'N';
410 END IF;
411 END IF;
412 END inv_check_lcm;
413 /*
414 END for bug No 7440217
415 PO API for LCM
416 */
417
418
419
420 FUNCTION get_conc_segments(x_org_id IN NUMBER, x_loc_id IN NUMBER)
421 RETURN VARCHAR2 IS
422 x_conc_segs VARCHAR2(2000) := NULL;
423 v_loc_str VARCHAR2(2000) := NULL;
424 v_proj_name VARCHAR2(50) := NULL;
425 v_task_name VARCHAR2(50) := NULL;
426 v_append VARCHAR2(1000) := NULL;
427 v_parse_str VARCHAR2(3000) := NULL;
428 v_num NUMBER;
429 v_cnt NUMBER := 0;
430 v_proj_ref_enabled NUMBER := NULL;
431 v_flex_code VARCHAR2(5) := 'MTLL';
432 v_flex_num NUMBER;
433 v_seg19_f BOOLEAN := FALSE;
434 v_seg20_f BOOLEAN := FALSE;
435 v_delim VARCHAR2(1) := NULL;
436 dsql_cur NUMBER;
437 rows_processed NUMBER;
438 str1 VARCHAR2(15) := NULL;
439 d_data_str VARCHAR2(1000) := NULL;
440
441 CURSOR cur1(flex_code VARCHAR2) IS
442 SELECT a.application_column_name
443 FROM fnd_id_flex_segments_vl a
444 WHERE a.application_id = 401
445 AND a.id_flex_code = flex_code
446 AND a.id_flex_num = (SELECT id_flex_num
447 FROM fnd_id_flex_structures
448 WHERE id_flex_code = flex_code)
449 AND a.enabled_flag = 'Y'
450 AND a.display_flag = 'Y'
451 ORDER BY a.segment_num;
452
453 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
454 BEGIN
455 SELECT id_flex_num
456 INTO v_flex_num
457 FROM fnd_id_flex_structures
458 WHERE id_flex_code = 'MTLL';
459
460 SELECT project_reference_enabled
461 INTO v_proj_ref_enabled
462 FROM mtl_parameters
463 WHERE organization_id = x_org_id;
464
465 v_delim := fnd_flex_ext.get_delimiter('INV', v_flex_code, v_flex_num);
466 str1 := '||''' || v_delim || '''||';
467
468 FOR cur2 IN cur1(v_flex_code) LOOP
469 IF v_proj_ref_enabled = 1
470 AND(cur2.application_column_name = 'SEGMENT19'
471 OR cur2.application_column_name = 'SEGMENT20') THEN
472 IF cur2.application_column_name = 'SEGMENT19' THEN
473 BEGIN
474 v_seg19_f := TRUE;
475
476 -- bug 4662395 set the profile mfg_organization_id so
477 -- the call to MTL_PROJECT_V will return data.
478
479 FND_PROFILE.put('MFG_ORGANIZATION_ID',x_org_id);
480
481 SELECT DISTINCT project_number
482 INTO v_proj_name
483 FROM mtl_project_v
484 WHERE project_id = (SELECT NVL(TO_NUMBER(segment19), 0)
485 FROM mtl_item_locations
486 WHERE inventory_location_id = x_loc_id
487 AND organization_id = x_org_id);
488 EXCEPTION
489 WHEN OTHERS THEN
490 v_proj_name := NULL;
491 END;
492 ELSIF cur2.application_column_name = 'SEGMENT20' THEN
493 BEGIN
494 v_seg20_f := TRUE;
495
496 SELECT DISTINCT a.task_number
497 INTO v_task_name
498 FROM mtl_task_v a
499 WHERE a.task_id = (SELECT NVL(TO_NUMBER(segment20), 0)
500 FROM mtl_item_locations
501 WHERE inventory_location_id = x_loc_id
502 AND organization_id = x_org_id)
503 AND a.project_id = (SELECT NVL(TO_NUMBER(segment19), a.project_id)
504 FROM mtl_item_locations
505 WHERE inventory_location_id = x_loc_id
506 AND organization_id = x_org_id);
507 EXCEPTION
508 WHEN OTHERS THEN
509 v_task_name := NULL;
510 END;
511 END IF;
512 END IF;
513 END LOOP;
514
515 FOR cur2 IN cur1(v_flex_code) LOOP
516 IF v_loc_str IS NOT NULL THEN
517 v_append := v_loc_str || str1;
518 ELSE
519 v_append := NULL;
520 END IF;
521
522 /*Bug#4278601
523 v_loc_str needs to contain the locator segments in the order it was defined,
524 including project and task segments.*/
525 IF (CUR2.APPLICATION_COLUMN_NAME = 'SEGMENT19') THEN
526 v_loc_str := v_append||''''||v_proj_name||'''';
527 ELSIF (CUR2.APPLICATION_COLUMN_NAME = 'SEGMENT20') THEN
528 v_loc_str := v_append||''''||v_task_name||'''';
529 ELSIF (cur2.application_column_name <> 'SEGMENT19'
530 AND cur2.application_column_name <> 'SEGMENT20') THEN
531 v_loc_str := v_append || cur2.application_column_name;
532 END IF;
533 END LOOP;
534
535 IF v_loc_str IS NOT NULL THEN
536 v_parse_str :=
537 'select ' || v_loc_str || ' from mtl_item_locations where inventory_location_id = :loc_id ' || ' and organization_id = :org_id';
538 dsql_cur := DBMS_SQL.open_cursor;
539 DBMS_SQL.parse(dsql_cur, v_parse_str, DBMS_SQL.native);
540 DBMS_SQL.define_column(dsql_cur, 1, d_data_str, 800);
541 DBMS_SQL.bind_variable(dsql_cur, 'loc_id', x_loc_id);
542 DBMS_SQL.bind_variable(dsql_cur, 'org_id', x_org_id);
543 rows_processed := DBMS_SQL.EXECUTE(dsql_cur);
544
545 LOOP
546 IF (DBMS_SQL.fetch_rows(dsql_cur) > 0) THEN
547 DBMS_SQL.column_value(dsql_cur, 1, d_data_str);
548 ELSE
549 -- No more rows in cursor
550 DBMS_SQL.close_cursor(dsql_cur);
551 EXIT;
552 END IF;
553 END LOOP;
554
555 IF DBMS_SQL.is_open(dsql_cur) THEN
556 DBMS_SQL.close_cursor(dsql_cur);
557 END IF;
558 END IF;
559
560 /*Bug 4278601
561 Comment out this section because now d_data_str will have the complete locator information
562 in the order it was defined.
563 IF v_seg19_f
564 AND v_seg20_f THEN
565 x_conc_segs := d_data_str || v_delim || v_proj_name || v_delim || v_task_name;
566 ELSIF v_seg19_f THEN
567 x_conc_segs := d_data_str || v_delim || v_proj_name;
568 ELSIF v_seg20_f THEN
569 x_conc_segs := d_data_str || v_delim || v_task_name;
570 ELSE
571 x_conc_segs := d_data_str;
572 END IF;*/
573
574 x_conc_segs := d_data_str; --Bug#4278601
575
576 RETURN x_conc_segs;
577 EXCEPTION
578 WHEN OTHERS THEN
579 x_conc_segs := NULL;
580 RETURN x_conc_segs;
581 END get_conc_segments;
582
583 /*
584 Added for bug No :2326247.
585 Calculates the item cost based on costing.
586 */
587 --Added NOCOPY hint to v_item_cost OUT parameter to comply
588 --with GSCC standard File.Sql.39. Bug:4410848
589 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
590 -- For standard costed orgs, get the item cost with the common
591 -- cost group ID = 1. For average costed orgs, use the org's
592 -- default cost group ID
593 -- Bug # 2180251: All primary costing methods not equal to 1 should
594 -- also be considered as an average costed org
595 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
596 --Forward port bug7037252 of bugs 6349028 and 6343400
597 --Added DECODE on wms_enabled_flag in the 2nd SELECT stmt.
598 --For WMS enabled average costing org, the org level CG may not be same as the onhand CG because you can have CG rules.
599 l_wms_enabled_flag VARCHAR2(1) := 'N';
600 BEGIN
601 --Bug7037252/6349028/6343400. Added following SELECT
602 SELECT NVL(mp.wms_enabled_flag,'N')
603 INTO l_wms_enabled_flag
604 FROM MTL_PARAMETERS mp
605 WHERE mp.organization_id=v_org_id ;
606 SELECT NVL(ccicv.item_cost, 0)
607 INTO v_item_cost
608 FROM cst_cg_item_costs_view ccicv, mtl_parameters mp
609 WHERE v_locator_id IS NULL
610 AND ccicv.organization_id = v_org_id
611 AND ccicv.inventory_item_id = v_item_id
612 AND ccicv.organization_id = mp.organization_id
613 AND ccicv.cost_group_id = DECODE(mp.primary_cost_method, 1, 1, NVL(mp.default_cost_group_id, 1))
614 UNION ALL
615 SELECT NVL(ccicv.item_cost, 0)
616 FROM mtl_item_locations mil, cst_cg_item_costs_view ccicv, mtl_parameters mp
617 WHERE v_locator_id IS NOT NULL
618 AND mil.organization_id = v_org_id
619 AND mil.inventory_location_id = v_locator_id
620 AND mil.project_id IS NULL
621 AND ccicv.organization_id = mil.organization_id
622 AND ccicv.inventory_item_id = v_item_id
623 AND ccicv.organization_id = mp.organization_id
624 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)))
625 UNION ALL
626 SELECT NVL(ccicv.item_cost, 0)
627 FROM mtl_item_locations mil, mrp_project_parameters mrp, cst_cg_item_costs_view ccicv, mtl_parameters mp
628 WHERE v_locator_id IS NOT NULL
629 AND mil.organization_id = v_org_id
630 AND mil.inventory_location_id = v_locator_id
631 AND mil.project_id IS NOT NULL
632 AND mrp.organization_id = mil.organization_id
633 AND mrp.project_id = mil.project_id
634 AND ccicv.organization_id = mil.organization_id
635 AND ccicv.inventory_item_id = v_item_id
636 AND ccicv.organization_id = mp.organization_id
637 AND ccicv.cost_group_id = DECODE(mp.primary_cost_method, 1, 1, NVL(mrp.costing_group_id, 1));
638 EXCEPTION
639 WHEN NO_DATA_FOUND THEN
640 v_item_cost := -999;
641 WHEN TOO_MANY_ROWS THEN --Bug --Forward port 7037252/6349028/6343400
642 IF (l_wms_enabled_flag = 'Y' ) THEN
643 --For WMS org with average costing, there may be more than one rows in cst_cg_item_costs_view.
644 --In this case, the correct cost will be stamped from INV_COST_GROUP_PVT after obtaining exact cost group.
645 v_item_cost := 1; --This is hard-coded so that we can retrieve the percent value in INV_COST_GROUP_PVT.
646 ELSE
647 v_item_cost := -999; --This should result in error for non-wms orgs.
648 END IF;
649 END get_item_cost;
650
651 PROCEDURE get_sales_order_id (
652 p_sales_order_number NUMBER ,
653 p_sales_order_type VARCHAR2,
654 p_sales_order_source VARCHAR2,
655 p_concatenated_segments OUT NOCOPY VARCHAR2,
656 p_source_id OUT NOCOPY NUMBER) IS
657 /*
658 ||==============================================================================||
659 || Created By : Nalin Kumar ||
660 || Created On : 03-May-2005 ||
661 || Purpose : This procedure will get called from TMO. This procedure will ||
662 || return the Sales Order ID and Concatenated Segments. Created ||
663 || as part of Depot Repair Enh. Bug# 4346443 ||
664 || ||
665 || Known limitations, enhancements or remarks : ||
666 || Change History : ||
667 || Who When What ||
668 || (reverse chronological order - newest change first) ||
669 ||==============================================================================||
670 */
671 l_delimiter VARCHAR2(1) := NULL;
672 l_segment_array FND_FLEX_EXT.SegmentArray;
673 l_n_segments NUMBER;
674 l_val BOOLEAN;
675 BEGIN
676 p_source_id := NULL;
677 --Get the Delimiter...
678 l_delimiter := fnd_flex_apis.get_segment_delimiter(
679 x_application_id => 401,
680 x_id_flex_code => 'MKTS',
681 x_id_flex_num => '101');
682
683 l_segment_array(1) := p_sales_order_number;
684 l_segment_array(2) := p_sales_order_type;
685 l_segment_array(3) := p_sales_order_source;
686 l_n_segments := 3;
687
688 --Get Concatenated Segments...
689 p_concatenated_segments := fnd_flex_ext.concatenate_segments(l_n_segments, l_segment_array, l_delimiter);
690
691 --Check for the combination...
692 l_val := fnd_flex_keyval.validate_segs(
693 operation => 'FIND_COMBINATION',
694 appl_short_name => 'INV',
695 key_flex_code => 'MKTS',
696 structure_number => '101',
697 concat_segments => p_concatenated_segments,
698 validation_date => SYSDATE);
699
700 --Get the combination id (source_id)...
701 IF l_val THEN
702 p_source_id := fnd_flex_keyval.combination_id;
703 END IF;
704 END get_sales_order_id ;
705
706 END inv_utilities;
[Home] [Help]