[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY HH:MM:SS')
INTO l_ts
FROM DUAL;
SELECT w.wip_entity_id wip_entity_id
, w.quantity_backordered requested_quantity
, w.inventory_item_id inventory_item_id
, w.repetitive_schedule_id
, w.operation_seq_num
, w.wip_issue_flag
, w.subinventory_code
, w.locator_id
FROM wip_material_shortages_v w, mtl_txn_request_lines l
WHERE l.lpn_id = l_lpn_id
AND l.line_id = NVL(p_move_order_line_id, l.line_id) -- added for ATF_J
AND l.organization_id = l_org_id
AND w.organization_id = l_org_id
AND NVL(l.project_id, -999) = NVL(w.project_id, -999)
AND NVL(l.task_id, -999) = NVL(w.task_id, -999)
AND w.inventory_item_id = l.inventory_item_id
AND l.backorder_delivery_detail_id IS NULL
AND(l.quantity_detailed IS NULL
OR l.quantity_delivered IS NULL
OR l.quantity_delivered = 0) ;
wms_task_dispatch_put_away.crdk_wip_info_table.DELETE;
SELECT 1
INTO l_count
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM wip_material_shortages_v b, mtl_txn_request_lines l
WHERE l.lpn_id = l_lpn_id
AND l.line_id = NVL(p_move_order_line_id, l.line_id) -- added for ATF_J
AND l.organization_id = l_org_id
AND b.organization_id = l.organization_id
AND NVL(l.project_id, -999) = NVL(b.project_id, -999)
AND NVL(l.task_id, -999) = NVL(b.task_id, -999)
AND b.inventory_item_id = l.inventory_item_id
AND l.backorder_delivery_detail_id IS NULL
AND(l.quantity_detailed IS NULL
OR l.quantity_detailed = 0));
SELECT 1
INTO l_insp_cnt
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM mtl_txn_request_lines
WHERE lpn_id = l_lpn_id
AND line_id = NVL(p_move_order_line_id, line_id) -- added for ATF_J
AND organization_id = l_org_id
AND NVL(inspection_status, 2) = 3);
SELECT default_crossdock_subinventory
, default_crossdock_locator_id
INTO l_to_sub
, l_to_loc
FROM mtl_parameters
WHERE organization_id = l_org_id;
SELECT default_stage_subinventory
, default_stage_locator_id
INTO l_to_sub
, l_to_loc
FROM wsh_shipping_parameters
WHERE organization_id = l_org_id;
SELECT mol.organization_id
, mol.inventory_item_id
, mol.quantity
, mol.uom_code
, mol.lot_number
, mol.revision
, mol.project_id
, mol.task_id
, mol.REFERENCE
, mol.reference_type_code
, mol.reference_id
, mol.header_id
, mol.txn_source_id
, mol.transaction_type_id
, mol.transaction_source_type_id
, mol.from_subinventory_code
, mol.from_locator_id
, mol.inspection_status
, mol.line_id
, msi.primary_uom_code
INTO l_org_id
, l_inventory_item_id
, l_qty
, l_uom
, l_lot_number
, l_revision
, l_project_id
, l_task_id
, l_reference
, l_reference_type_code
, l_reference_id
, l_header_id
, l_txn_source_id
, l_transaction_type_id
, l_transaction_source_type_id
, l_sub
, l_loc
, l_inspection_status
, l_line_id
, l_primary_uom
FROM mtl_txn_request_lines mol, mtl_system_items msi
WHERE lpn_id = l_lpn_id
AND line_id = NVL(p_move_order_line_id, line_id) -- added for ATF_J
AND mol.organization_id = l_org_id
AND mol.inventory_item_id = l_wip_item
AND mol.backorder_delivery_detail_id IS NULL
AND mol.inventory_item_id = msi.inventory_item_id
AND mol.organization_id = msi.organization_id
AND ROWNUM = 1;
UPDATE mtl_txn_request_lines
SET quantity = l_quantity_allocated
, backorder_delivery_detail_id = l_wip_id
, to_subinventory_code = l_to_sub
, to_locator_id = l_to_loc
, crossdock_type = 2
WHERE line_id = l_line_id;
UPDATE mtl_txn_request_lines
SET backorder_delivery_detail_id = l_wip_id
, quantity = l_quantity_allocated
, to_subinventory_code = l_to_sub
, to_locator_id = l_to_loc
, crossdock_type = 2
WHERE line_id = l_line_id;
mydebug(' update old mol ');
UPDATE mtl_txn_request_lines
SET backorder_delivery_detail_id = l_wip_id
, to_subinventory_code = l_to_sub
, to_locator_id = l_to_loc
, crossdock_type = 2
WHERE line_id = l_line_id;
UPDATE mtl_txn_request_lines
SET backorder_delivery_detail_id = l_wip_id
, quantity = l_quantity_allocated
, to_subinventory_code = l_to_sub
, to_locator_id = l_to_loc
, crossdock_type = 2
WHERE line_id = l_line_id;
UPDATE mtl_txn_request_lines
SET backorder_delivery_detail_id = l_wip_id
, to_subinventory_code = l_to_sub
, to_locator_id = l_to_loc
, crossdock_type = 2
WHERE line_id = l_line_id;
l_update_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
SELECT fm_serial_number
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_serial_temp_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_temp_id
FROM dual;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_txn_hdr_id
FROM dual;
SELECT move_order_line_id
, DECODE(wip_supply_type, 1, 'Y', 'N')
, demand_source_header_id
, repetitive_line_id
, operation_seq_num
, NVL(primary_quantity, 0)
, NVL(transaction_quantity, 0)
, lpn_id
, content_lpn_id
, transfer_lpn_id
INTO line_id
, l_wip_issue_flag
, l_wip_entity_id
, l_repetitive_schedule_id
, l_operation_seq_num
, l_primary_quantity
, l_transaction_quantity
, l_lpn_id
, l_content_lpn_id
, l_transfer_lpn_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_temp_id
AND ROWNUM < 2;
mydebug('wip isuue - before Insert into MMTT..');
insert_new_mmtt_row_like(p_txn_temp_id => l_temp_id, x_new_temp_id => l_new_temp_id, x_new_hdr_id => l_new_txn_hdr_id);
mydebug('after Insert into MMTT..');
mydebug('calling wms_wip_integration.update_mmtt_for_wip without ');
wms_wip_integration.update_mmtt_for_wip(
p_transaction_temp_id => l_new_temp_id
, p_wip_entity_id => l_wip_entity_id
, p_operation_seq_num => l_operation_seq_num
, p_repetitive_schedule_id => l_repetitive_schedule_id
, p_transaction_type_id => inv_globals.g_type_xfer_order_wip_issue
);
mydebug('wms_wip_integration.update_mmtt_for_wip failed ');
mydebug('after calling wms_wip_integration.update_mmtt_for_wip');
UPDATE mtl_material_transactions_temp
SET move_order_line_id = NULL
, transaction_status = 1
, primary_quantity = l_primary_quantity
, transaction_quantity = l_transaction_quantity
--, lpn_id = l_lpn_id
--, content_lpn_id = l_content_lpn_id
--, transfer_lpn_id = l_transfer_lpn_id
, wms_task_type = NULL -- bug fix 3233053
WHERE transaction_temp_id = l_new_temp_id;
mydebug('update - complete');
SELECT 1
INTO l_bflow_exist
FROM mfg_lookups
WHERE lookup_type = 'WMS_BUSINESS_FLOW'
AND lookup_code = 37;
UPDATE mtl_material_transactions_temp
SET lpn_id = l_lpn_id
, content_lpn_id = l_content_lpn_id
, transfer_lpn_id = l_transfer_lpn_id
WHERE transaction_temp_id = l_new_temp_id;
mydebug('After insert into MMTT');
SELECT 1
INTO l_bflow_exist
FROM mfg_lookups
WHERE lookup_type = 'WMS_BUSINESS_FLOW'
AND lookup_code = 37;
PROCEDURE insert_new_mmtt_row_like(p_txn_temp_id IN NUMBER, x_new_temp_id OUT NOCOPY NUMBER, x_new_hdr_id OUT NOCOPY NUMBER) IS
SUBTYPE mmtt_type IS mtl_material_transactions_temp%ROWTYPE;
SELECT *
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = txn_tmp_id;
SELECT *
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = txn_tmp_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO new_txn_temp_id
FROM DUAL;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_txn_hdr_id
FROM DUAL;
SELECT *
INTO mmtt_row
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_txn_temp_id;
SELECT lot_control_code
, serial_number_control_code
INTO v_lot_control_code
, v_serial_control_code
FROM mtl_system_items
WHERE inventory_item_id = mmtt_row.inventory_item_id
AND organization_id = mmtt_row.organization_id;
SELECT allocate_serial_flag
INTO v_allocate_serial_flag
FROM mtl_parameters
WHERE organization_id = mmtt_row.organization_id;
inv_rcv_common_apis.insert_mtlt(lot_row);
inv_rcv_common_apis.insert_msnt(ser_row);
inv_rcv_common_apis.insert_mtlt(lot_row);
SELECT mtl_material_transactions_s.NEXTVAL
INTO ser_transaction_temp_id
FROM DUAL;
SELECT mtl_material_transactions_s.NEXTVAL
INTO ser_row.transaction_temp_id
FROM DUAL;
inv_rcv_common_apis.insert_msnt(ser_row);
inv_rcv_common_apis.insert_mtlt(lot_row);
mydebug(' inserting the new row into mmtt using ' || 'wms_task_dispatch_engine.insert_mmtt ');
wms_task_dispatch_engine.insert_mmtt(l_mmtt_rec => mmtt_row);
END insert_new_mmtt_row_like;