[Home] [Help]
11: -- API name : cartonize
12: -- Type : group
13: -- Function : populates the cartonization_id, container_item_id columns,
14: -- of rows belonging to a particular move order header id in
15: -- mtl_material_transactions_temp.
16:
17: -- Pre-reqs : Those columns wouldn't be populated if the cartonization_id
18: -- for that row is already populated,
19: -- or if values for organization_id, inventory_item_id ,
76: -- split from it
77:
78: TYPE t_temp_id_rel_rec IS RECORD
79: (
80: orig_temp_id mtl_material_transactions_temp.transaction_temp_id%TYPE ,
81: splt_temp_id mtl_material_transactions_temp.transaction_temp_id%TYPE,
82: primary_quantity NUMBER,
83: secondary_quantity NUMBER,
84: processed VARCHAR2(1) := 'N');
77:
78: TYPE t_temp_id_rel_rec IS RECORD
79: (
80: orig_temp_id mtl_material_transactions_temp.transaction_temp_id%TYPE ,
81: splt_temp_id mtl_material_transactions_temp.transaction_temp_id%TYPE,
82: primary_quantity NUMBER,
83: secondary_quantity NUMBER,
84: processed VARCHAR2(1) := 'N');
85:
98:
99: --for device integration
100: SUBTYPE WDR_ROW IS WMS_DEVICE_REQUESTS%ROWTYPE;
101:
102: SUBTYPE MMTT_ROW_TYPE IS MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE;
103:
104: -- Table that needs to be passed inv_rcv_common_apis for splitting
105: -- mtl_transaction_lots_temp and mtl_serial_numbers_temp
106:
379:
380: CURSOR c1(mo_hdr_id NUMBER) IS
381: /* added the index hint with the suggestion of apps performance team */
382: SELECT /*+index (mmtt,mtl_material_trans_temp_n14)*/ mmtt.* FROM
383: mtl_material_transactions_temp mmtt, mtl_txn_request_lines
384: mtrl WHERE mtrl.header_id = mo_hdr_id AND
385: mmtt.move_order_line_id = mtrl.line_id AND
386: --2513907 fix
387: mmtt.container_item_id IS null
388: and mmtt.cartonization_id is null ; ---(Condition "mmtt.cartonization_id is null" is added for the bug14546443)
389:
390: CURSOR c2(txn_hdr_id NUMBER) IS
391: SELECT mmtt.* FROM
392: mtl_material_transactions_temp mmtt
393: WHERE
394: mmtt.transaction_header_id = txn_hdr_id;
395:
396: -- following cursors added for patchset J bulk picking ---------------
396: -- following cursors added for patchset J bulk picking ---------------
397:
398: CURSOR c11_bulk_all_fast(p_organization_id NUMBER) IS
399: SELECT mmtt.* FROM
400: mtl_material_transactions_temp mmtt,mtl_allocations_gtmp mag
401: WHERE mmtt.wms_task_status = 8 -- unreleased
402: And nvl(mmtt.lock_flag,'$') <> 'Y' --Bug#8546026
403: And mmtt.cartonization_id IS null -- not cartonized
404: And mmtt.organization_id = p_organization_id
418: for update of mmtt.transaction_temp_id; -- to lock the records
419:
420: CURSOR c11_bulk_only_sub_item_fast(p_organization_id NUMBER) IS
421: SELECT mmtt.* FROM
422: mtl_material_transactions_temp mmtt,mtl_allocations_gtmp mag
423: WHERE mmtt.wms_task_status = 8 -- unreleased
424: And nvl(mmtt.lock_flag,'$') <> 'Y' --Bug#8546026
425: And mmtt.cartonization_id IS null -- not cartonized
426: And mmtt.organization_id = p_organization_id
451:
452:
453: CURSOR c11_bulk_all(p_organization_id NUMBER) IS
454: SELECT mmtt.* FROM
455: mtl_material_transactions_temp mmtt,mtl_txn_request_headers moh,
456: wsh_pick_grouping_rules spg
457: WHERE mmtt.wms_task_status = 8 -- unreleased
458: And nvl(mmtt.lock_flag,'$') <> 'Y' --Bug#8546026
459: And mmtt.cartonization_id IS null -- not cartonized
481: for update of mmtt.transaction_temp_id; -- to lock the records
482:
483: CURSOR c11_bulk_only_sub_item(p_organization_id NUMBER) IS
484: SELECT mmtt.* FROM
485: mtl_material_transactions_temp mmtt,mtl_txn_request_headers moh,
486: wsh_pick_grouping_rules spg
487: WHERE mmtt.wms_task_status = 8 -- unreleased
488: And nvl(mmtt.lock_flag,'$') <> 'Y' --Bug#8546026
489: And mmtt.cartonization_id IS null -- not cartonized
523:
524: CURSOR c11_bulk_trip(p_organization_id NUMBER) IS
525: select mmtt.*
526: from wsh_delivery_legs wdl,wsh_delivery_details wdd, wsh_delivery_assignments_v wda,
527: wsh_trip_stops wts, mtl_material_transactions_temp mmtt,mtl_txn_request_headers moh,
528: wsh_pick_grouping_rules spg
529: where wts.trip_id = p_input_for_bulk.trip_id
530: and wdl.pick_up_stop_id = wts.stop_id
531: and wdl.delivery_id = wda.delivery_id
560:
561: CURSOR c11_bulk_trip_sub_item(p_organization_id NUMBER) IS
562: select mmtt.*
563: from wsh_delivery_legs wdl,wsh_delivery_details wdd, wsh_delivery_assignments_v wda,
564: wsh_trip_stops wts, mtl_material_transactions_temp mmtt,mtl_txn_request_headers moh,
565: wsh_pick_grouping_rules spg
566: where wts.trip_id = p_input_for_bulk.trip_id
567: and wdl.pick_up_stop_id = wts.stop_id
568: and wdl.delivery_id = wda.delivery_id
742: if (g_trace_on = 1) then -- log_event('PATCHSET J-- BULK PICKING -- START');
743: log_event('checking the lot and serial control code'); end if;
744: select lot_control_code,serial_number_control_code
745: into l_lot_control_code,l_serial_control_code
746: from mtl_system_items_b msi, mtl_material_transactions_temp mmtt
747: where mmtt.transaction_temp_id = mmtt_rec.transaction_Temp_id
748: and mmtt.organization_id = msi.organization_id
749: and msi.inventory_item_id = mmtt.inventory_item_id;
750: if (g_trace_on = 1) then log_event('transaction_temp_id:'|| mmtt_rec.transaction_Temp_id ||
752: ' serial control code :'||l_serial_control_code);
753: end if;
754: if (g_trace_on = 1) then log_event('nullify the parent_line_id for the child task '); end if;
755:
756: update mtl_material_transactions_temp
757: set parent_line_id = null
758: where transaction_temp_id = mmtt_rec.transaction_Temp_id;
759:
760: -- call update_parent_mmtt to change the qty or delete the parent line --------
3086: -- Current version 1.0
3087: --
3088: -- Notes : calls AssignTT(p_task_id NUMBER)
3089: --
3090: -- This procedure loops through mtl_material_transactions_temp table, assign
3091: -- user defined task type to tasks that have not been assigned a task type
3092: -- for the given Move Order Header.
3093: -- This API is created when doing patchset J bulk picking
3094: -- ****************************************
3221: v_prev_move_order_line_id NUMBER := 0;
3222: space_avail_for NUMBER := 0;
3223: tr_space_avail_for NUMBER := 0;
3224: sec_tr_space_avail_for NUMBER := 0; --invconv kkillams
3225: curr_temp_id mtl_material_transactions_temp.transaction_temp_id%TYPE ;
3226: v_lot_control_code NUMBER := NULL;
3227: v_serial_control_code NUMBER := NULL;
3228: api_table_index NUMBER := 0;
3229: l_current_header_id NUMBER := NULL;
3295: CURSOR OB_LBPRT IS
3296: SELECT DISTINCT mmtt.cartonization_id FROM
3297: --2513907 fix
3298: wms_cartonization_temp mmtt,
3299: --mtl_material_transactions_temp mmtt,
3300: mtl_txn_request_lines mtrl WHERE
3301: mmtt.move_order_line_id = mtrl.line_id
3302: AND mtrl.header_id = p_move_order_header_id
3303: AND mmtt.cartonization_id IS NOT NULL
3306: CURSOR IB_LBPRT IS
3307: SELECT DISTINCT mmtt.cartonization_id FROM
3308: --2513907 fix
3309: wms_cartonization_temp mmtt
3310: --mtl_material_transactions_temp mmtt
3311: WHERE
3312: mmtt.transaction_header_id = p_transaction_header_id
3313: AND mmtt.cartonization_id IS NOT NULL
3314: ORDER BY mmtt.cartonization_id;
3695: p_transaction_header_id => NULL,
3696: p_input_for_bulk =>p_input_for_bulk);
3697:
3698: --INSERT INTO wms_cartonization_temp SELECT mmtt.* FROM
3699: --mtl_material_transactions_temp mmtt, mtl_txn_request_lines
3700: --mtrl WHERE mtrl.header_id = p_move_order_header_id AND
3701: --mmtt.move_order_line_id = mtrl.line_id;
3702:
3703: ELSE --outbound = 'N'
3712: );
3713:
3714: --INSERT INTO wms_cartonization_temp
3715: --SELECT mmtt.* FROM
3716: --mtl_material_transactions_temp mmtt
3717: --WHERE
3718: --mmtt.transaction_header_id = p_transaction_header_id;
3719:
3720: END IF;
5043: --GET PRIMARY QUANTITY
5044: END IF;
5045:
5046:
5047: IF table_name = 'mtl_material_transactions_temp' THEN
5048:
5049: if (g_trace_on = 1) then log_event(' table_name = mtl_material_transactions_temp'); END IF;
5050:
5051:
5045:
5046:
5047: IF table_name = 'mtl_material_transactions_temp' THEN
5048:
5049: if (g_trace_on = 1) then log_event(' table_name = mtl_material_transactions_temp'); END IF;
5050:
5051:
5052: IF p_parent_line_id = -99999 THEN
5053: SELECT parent_line_id INTO par_line_id FROM
5050:
5051:
5052: IF p_parent_line_id = -99999 THEN
5053: SELECT parent_line_id INTO par_line_id FROM
5054: mtl_material_transactions_temp WHERE transaction_temp_id =
5055: p_transaction_temp_id;
5056: ELSE
5057: par_line_id := p_parent_line_id;
5058: END IF;
5062:
5063: IF( p_upd_qty_flag = 'Y') THEN
5064:
5065:
5066: UPDATE mtl_material_transactions_temp SET
5067: primary_quantity = p_primary_quantity,
5068: transaction_quantity = p_transaction_quantity,
5069: secondary_transaction_quantity = p_secondary_quantity, --invconv kkillams
5070: cartonization_id = l_LPN,
5074: last_updated_by = fnd_global.user_id
5075: WHERE
5076: transaction_temp_id = p_transaction_temp_id;
5077: ELSE
5078: UPDATE mtl_material_transactions_temp SET
5079: cartonization_id = l_LPN,
5080: container_item_id = p_container_item_id,
5081: parent_line_id = par_line_id,
5082: last_update_date = Sysdate,
5219: transaction_temp_id = p_transaction_temp_id AND ROWNUM < 2;
5220:
5221: ELSE
5222: --Bug 3296177 code should never come here
5223: if (g_trace_on = 1) THEN log_event('ERROR:INS_MMTT with mtl_material_transactions_temp'); end if;
5224: RAISE fnd_api.g_exc_error;
5225: --SELECT * INTO v1 FROM mtl_material_transactions_temp WHERE
5226: --transaction_temp_id = p_transaction_temp_id;
5227:
5221: ELSE
5222: --Bug 3296177 code should never come here
5223: if (g_trace_on = 1) THEN log_event('ERROR:INS_MMTT with mtl_material_transactions_temp'); end if;
5224: RAISE fnd_api.g_exc_error;
5225: --SELECT * INTO v1 FROM mtl_material_transactions_temp WHERE
5226: --transaction_temp_id = p_transaction_temp_id;
5227:
5228: END IF;
5229:
5274: v1.inventory_item_id := p_item_id;
5275: END IF;
5276:
5277:
5278: IF (table_name = 'mtl_material_transactions_temp') THEN
5279:
5280: RAISE fnd_api.g_exc_error;
5281: --wms_task_dispatch_engine.insert_mmtt
5282: --(l_mmtt_rec => v1);
5403: SELECT
5404: mtlt.lot_number lot_num,
5405: mtlt.transaction_quantity lot_qty,
5406: msnt.fm_serial_number ser_num
5407: FROM mtl_material_transactions_temp mmtt,
5408: mtl_transaction_lots_temp mtlt,
5409: mtl_serial_numbers_temp msnt
5410: WHERE
5411: mmtt.transaction_temp_id = p_transaction_temp_id
5687: SELECT
5688: mtlt.lot_number lot_num,
5689: mtlt.transaction_quantity lot_qty,
5690: msnt.fm_serial_number ser_num
5691: FROM mtl_material_transactions_temp mmtt,
5692: mtl_transaction_lots_temp mtlt,
5693: mtl_serial_numbers_temp msnt
5694: WHERE
5695: mmtt.transaction_temp_id = p_transaction_temp_id
5782:
5783: BEGIN
5784: SELECT 'Y' INTO v_exist FROM dual
5785: WHERE
5786: exists ( SELECT transaction_temp_id FROM mtl_material_transactions_temp
5787: WHERE transaction_temp_id = v1.transaction_temp_id);
5788: EXCEPTION
5789: WHEN no_data_found THEN
5790: v_exist := 'N';
5807: END IF;
5808:
5809: if (g_trace_on = 1) then log_event(' After the cartonization id condition '); END IF;
5810:
5811: table_name := 'mtl_material_transactions_temp';
5812:
5813:
5814: IF v_exist = 'Y' THEN
5815:
5954: v_prev_move_order_line_id NUMBER := -999;
5955: space_avail_for NUMBER := 0;
5956: tr_space_avail_for NUMBER := 0;
5957: sec_tr_space_avail_for NUMBER := 0; --invconv kkillams
5958: curr_temp_id mtl_material_transactions_temp.transaction_temp_id%TYPE ;
5959: v_lot_control_code NUMBER := NULL;
5960: v_serial_control_code NUMBER := NULL;
5961: api_table_index NUMBER := 0;
5962: l_current_header_id NUMBER := NULL;
6047: CURSOR OB_LBPRT IS
6048: SELECT DISTINCT mmtt.cartonization_id FROM
6049: --2513907 fix
6050: wms_cartonization_temp mmtt,
6051: --mtl_material_transactions_temp mmtt,
6052: mtl_txn_request_lines mtrl WHERE
6053: mmtt.move_order_line_id = mtrl.line_id
6054: AND mtrl.header_id = p_move_order_header_id
6055: AND mmtt.cartonization_id IS NOT NULL
6802: CURSOR OB_LBPRT IS
6803: SELECT DISTINCT mmtt.cartonization_id FROM
6804: --2513907 fix
6805: wms_cartonization_temp mmtt,
6806: --mtl_material_transactions_temp mmtt,
6807: mtl_txn_request_lines mtrl WHERE
6808: mmtt.move_order_line_id = mtrl.line_id
6809: AND mtrl.header_id = p_move_order_header_id
6810: AND mmtt.cartonization_id IS NOT NULL
7289: v_prev_move_order_line_id NUMBER := 0;
7290: space_avail_for NUMBER := 0;
7291: tr_space_avail_for NUMBER := 0;
7292: sec_tr_space_avail_for NUMBER := 0; --invconv kkillams
7293: curr_temp_id mtl_material_transactions_temp.transaction_temp_id%TYPE ;
7294: v_lot_control_code NUMBER := NULL;
7295: v_serial_control_code NUMBER := NULL;
7296: api_table_index NUMBER := 0;
7297: l_current_header_id NUMBER := NULL;
7359: CURSOR OB_LBPRT IS
7360: SELECT DISTINCT mmtt.cartonization_id FROM
7361: --2513907 fix
7362: wms_cartonization_temp mmtt,
7363: --mtl_material_transactions_temp mmtt,
7364: mtl_txn_request_lines mtrl WHERE
7365: mmtt.move_order_line_id = mtrl.line_id
7366: AND mtrl.header_id = p_move_order_header_id
7367: AND mmtt.cartonization_id IS NOT NULL
8099: , x_return_status OUT NOCOPY VARCHAR2
8100: )
8101: IS
8102:
8103: TYPE l_cart_tab_type IS TABLE OF mtl_material_transactions_temp.cartonization_id%TYPE INDEX BY BINARY_INTEGER;
8104: TYPE l_trans_temp_tab_type IS TABLE OF mtl_material_transactions_temp.transaction_temp_id%TYPE INDEX BY BINARY_INTEGER;
8105:
8106: l_mmtt_table WMS_CARTONIZATION_USER_PUB.mmtt_type;
8107: l_out_mmtt_table WMS_CARTONIZATION_USER_PUB.mmtt_type;
8100: )
8101: IS
8102:
8103: TYPE l_cart_tab_type IS TABLE OF mtl_material_transactions_temp.cartonization_id%TYPE INDEX BY BINARY_INTEGER;
8104: TYPE l_trans_temp_tab_type IS TABLE OF mtl_material_transactions_temp.transaction_temp_id%TYPE INDEX BY BINARY_INTEGER;
8105:
8106: l_mmtt_table WMS_CARTONIZATION_USER_PUB.mmtt_type;
8107: l_out_mmtt_table WMS_CARTONIZATION_USER_PUB.mmtt_type;
8108: