[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT(*) l_num_rows
, mmtt.cartonization_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_header_id = p_mo_hdr_id
AND mmtt.cartonization_id IS NOT NULL
GROUP BY mmtt.cartonization_id;
SELECT mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_header_id = p_mo_hdr_id
AND mmtt.parent_line_id IS NULL
AND EXISTS
( SELECT 'x'
FROM wms_user_task_type_attributes wutta
WHERE wutta.organization_id = mmtt.organization_id
AND wutta.user_task_type_id = mmtt.standard_operation_id
AND wutta.honor_case_pick_flag = 'Y'
)
UNION ALL
-- Bulk pick parent tasks
SELECT mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id IN
( SELECT DISTINCT mmtt2.parent_line_id
FROM mtl_material_transactions_temp mmtt2
WHERE mmtt2.move_order_header_id = p_mo_hdr_id
AND mmtt2.parent_line_id IS NOT NULL
AND mmtt2.transaction_temp_id <> mmtt2.parent_line_id
)
AND EXISTS
( SELECT 'x'
FROM wms_user_task_type_attributes wutta
WHERE wutta.organization_id = mmtt.organization_id
AND wutta.user_task_type_id = mmtt.standard_operation_id
AND wutta.honor_case_pick_flag = 'Y'
);
SELECT COUNT(*) l_num_rows
, mmtt.inventory_item_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_header_id = p_mo_hdr_id
GROUP BY mmtt.inventory_item_id;
SELECT COUNT(*) l_num_rows
, mmtt.inventory_item_id
FROM mtl_material_transactions_temp mmtt
WHERE ( mmtt.move_order_header_id = p_mo_hdr_id
AND mmtt.parent_line_id IS NULL)
OR ( mmtt.transaction_temp_id IN
( SELECT DISTINCT mmtt2.parent_line_id
FROM mtl_material_transactions_temp mmtt2
WHERE mmtt2.move_order_header_id = p_mo_hdr_id
AND mmtt2.parent_line_id IS NOT NULL
AND mmtt2.transaction_temp_id <> mmtt2.parent_line_id
)
)
GROUP BY mmtt.inventory_item_id;
INSERT INTO wms_pr_workers( batch_id
, worker_mode
, processed_flag
, organization_id
, mo_header_id
, cartonization_id
, detailed_count
)
VALUES ( p_batch_id
, 'CRTN_LBL' -- Carton label
, 'N'
, p_organization_id
, p_mo_header_id
, l_crt_ids(ii)
, l_counts(ii)
);
print_debug( 'Inserted ' || l_num_sub_batches ||
' worker records for cartonization label printing.'
, l_api_name);
INSERT INTO wms_pr_workers( batch_id
, worker_mode
, processed_flag
, organization_id
, mo_header_id
, transaction_batch_id
, detailed_count
)
VALUES ( p_batch_id
, 'CSPK_LBL' -- Case pick label
, 'N'
, p_organization_id
, p_mo_header_id
, mtl_material_transactions_s.nextval
, l_detail_count
)
RETURNING transaction_batch_id INTO l_sub_batch_id;
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.transaction_batch_id = l_sub_batch_id
WHERE mmtt.transaction_temp_id = l_tmp_ids(ii);
INSERT INTO wms_pr_workers( batch_id
, worker_mode
, processed_flag
, organization_id
, mo_header_id
, transaction_batch_id
, detailed_count
)
VALUES ( p_batch_id
, p_mode
, 'N'
, p_organization_id
, p_mo_header_id
, mtl_material_transactions_s.nextval
, l_counts(ii)
)
RETURNING transaction_batch_id BULK COLLECT INTO l_sub_ids;
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.transaction_batch_id = l_sub_ids(jj)
WHERE mmtt.inventory_item_id = l_itm_ids(jj)
AND ( ( mmtt.move_order_header_id = p_mo_header_id
AND mmtt.parent_line_id IS NULL)
OR ( p_mode = 'TTA'
AND mmtt.transaction_temp_id IN
( SELECT DISTINCT mmtt2.parent_line_id
FROM mtl_material_transactions_temp mmtt2
WHERE mmtt2.move_order_header_id = p_mo_header_id
AND mmtt2.parent_line_id IS NOT NULL
AND mmtt2.transaction_temp_id <> mmtt2.parent_line_id
)
)
);
print_debug('Done inserting worker records for ' || p_mode, l_api_name);
SELECT 'x' FROM dual
WHERE EXISTS
( SELECT 'x'
FROM wms_rules rules
, wms_op_plans_b wop
WHERE rules.organization_id IN (p_org_id,-1)
AND rules.type_code = 7
AND rules.enabled_flag = 'Y'
AND rules.type_hdr_id = wop.operation_plan_id
AND wop.activity_type_id = 2 -- Outbound
AND wop.enabled_flag = 'Y'
);
UPDATE mtl_material_transactions_temp
SET operation_plan_id = l_op_plan_id
WHERE move_order_header_id = p_mo_header_id;
SELECT 'x' FROM dual
WHERE EXISTS
( SELECT 'x'
FROM wms_rules rules
WHERE rules.organization_id IN (p_org_id,-1)
AND rules.type_code = 3
AND rules.enabled_flag = 'Y'
);
UPDATE mtl_material_transactions_temp
SET standard_operation_id = l_ttype_id
WHERE move_order_header_id = p_mo_header_id;
SELECT 'x' FROM dual
WHERE EXISTS
( SELECT 'x'
FROM mtl_material_transactions_temp mmtt1
WHERE mmtt1.move_order_header_id = p_mo_header_id
AND mmtt1.cartonization_id IS NOT NULL
)
OR EXISTS
( SELECT 'x'
FROM mtl_material_transactions_temp mmtt2
WHERE mmtt2.move_order_header_id = p_mo_header_id
AND EXISTS
( SELECT 'x'
FROM wms_user_task_type_attributes wutta1
WHERE wutta1.organization_id = mmtt2.organization_id
AND wutta1.user_task_type_id = mmtt2.standard_operation_id
AND wutta1.honor_case_pick_flag = 'Y'
)
)
OR EXISTS
( SELECT 'x'
FROM mtl_material_transactions_temp mmtt3
WHERE mmtt3.transaction_temp_id IN
( SELECT DISTINCT mmtt4.parent_line_id
FROM mtl_material_transactions_temp mmtt4
WHERE mmtt4.move_order_header_id = p_mo_header_id
AND mmtt4.parent_line_id IS NOT NULL
AND mmtt4.transaction_temp_id <> mmtt4.parent_line_id
)
AND EXISTS
( SELECT 'x'
FROM wms_user_task_type_attributes wutta2
WHERE wutta2.organization_id = mmtt3.organization_id
AND wutta2.user_task_type_id = mmtt3.standard_operation_id
AND wutta2.honor_case_pick_flag = 'Y'
)
);
UPDATE mtl_material_transactions_temp
SET transaction_batch_id = NULL
, lock_flag = NULL -- newly added
WHERE move_order_header_id = p_mo_header_id;
UPDATE mtl_material_transactions_temp
SET transaction_batch_id = NULL,
lock_flag = NULL -- bug 9130704
WHERE transaction_temp_id IN
( SELECT DISTINCT mmtt2.parent_line_id
FROM mtl_material_transactions_temp mmtt2
WHERE move_order_header_id = p_mo_header_id
AND parent_line_id IS NOT NULL
);
DELETE wms_pr_workers
WHERE batch_id = p_batch_id
AND organization_id = p_org_id;
UPDATE mtl_material_transactions_temp
SET wms_task_status = 1
WHERE move_order_header_id = p_mo_header_id;
UPDATE mtl_material_transactions_temp
SET wms_task_status = 1
WHERE transaction_temp_id IN
( SELECT DISTINCT mmtt2.parent_line_id
FROM mtl_material_transactions_temp mmtt2
WHERE move_order_header_id = p_mo_header_id
AND parent_line_id IS NOT NULL
);
wms_postalloc_pvt.insert_device_requests
( p_organization_id => p_organization_id
, p_mo_header_id => p_mo_header_id
, x_return_status => l_api_return_status
);
print_debug('Error status from wms_postalloc_pvt.insert_device_requests: '
|| l_api_return_status, l_api_name);
SELECT mmtt.transaction_temp_id
, mmtt.parent_line_id
, msi.lot_control_code
, msi.serial_number_control_code
FROM mtl_material_transactions_temp mmtt
, mtl_system_items msi
WHERE mmtt.move_order_header_id = p_mo_header_id
AND mmtt.parent_line_id IS NOT NULL
AND msi.inventory_item_id = mmtt.inventory_item_id
AND msi.organization_id = mmtt.organization_id;
SELECT mtrh.header_id
, mtrh.grouping_rule_id
, DECODE(mp.mo_pick_confirm_required,1,'N','Y')
INTO l_mo_header_id
, l_grouping_rule_id
, l_auto_pick_confirm
FROM mtl_txn_request_headers mtrh
, mtl_parameters mp
WHERE mtrh.request_number = p_pickrel_batch
AND mtrh.organization_id = p_organization_id
AND mtrh.move_order_type = inv_globals.g_move_order_pick_wave
AND mp.organization_id = p_organization_id;
SELECT 'x' INTO l_dummy
FROM dual
WHERE EXISTS
( SELECT 'x'
FROM wms_pr_workers
WHERE organization_id = p_organization_id
AND (batch_id = l_batch_id
OR worker_mode = 'WMSBLKPR')
);
SELECT 'x' INTO l_dummy
FROM dual
WHERE EXISTS
( SELECT 'x'
FROM mtl_material_transactions_temp
WHERE move_order_header_id = l_mo_header_id
AND wms_task_status = 8 -- Unreleased
);
SELECT 'x' INTO l_dummy
FROM dual
WHERE EXISTS
( SELECT 'x'
FROM mtl_material_transactions_temp
WHERE move_order_header_id = l_mo_header_id
AND wms_task_status = 1 -- Released
);
SELECT 'x' INTO l_dummy FROM dual
WHERE EXISTS
( SELECT 'x' FROM mtl_material_transactions_temp
WHERE move_order_header_id = l_mo_header_id
AND parent_line_id IS NOT NULL
);
UPDATE mtl_material_transactions_temp
SET parent_line_id = NULL
WHERE transaction_temp_id = l_child_task_id(ll);
inv_trx_util_pub.update_parent_mmtt
( x_return_status => l_api_return_status
, p_parent_line_id => l_parent_task_id(ii)
, p_child_line_id => l_child_task_id(ii)
, p_lot_control_code => l_lot_control_code(ii)
, p_serial_control_code => l_serial_control_code(ii)
);
print_debug('Error status from inv_trx_util_pub.update_parent_mmtt: '
|| l_api_return_status, l_api_name);
l_lot_control_code.DELETE;
l_serial_control_code.DELETE;
UPDATE mtl_material_transactions_temp
SET cartonization_id = NULL
, container_item_id = NULL
WHERE move_order_header_id = l_mo_header_id
AND cartonization_id IS NOT NULL;
DELETE wms_packaging_hist
WHERE header_id = l_mo_header_id
AND packaging_mode = wms_cartnzn_pub.pr_pkg_mode;