[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_select_stmt LONG :=
'SELECT
NVL(MTLI.ATTRIBUTE_CATEGORY , MLN.ATTRIBUTE_CATEGORY ),
NVL(MTLI.ATTRIBUTE1 , MLN.ATTRIBUTE1 ),
NVL(MTLI.ATTRIBUTE2 , MLN.ATTRIBUTE2 ),
NVL(MTLI.ATTRIBUTE3 , MLN.ATTRIBUTE3 ),
NVL(MTLI.ATTRIBUTE4 , MLN.ATTRIBUTE4 ),
NVL(MTLI.ATTRIBUTE5 , MLN.ATTRIBUTE5 ),
NVL(MTLI.ATTRIBUTE6 , MLN.ATTRIBUTE6 ),
NVL(MTLI.ATTRIBUTE7 , MLN.ATTRIBUTE7 ),
NVL(MTLI.ATTRIBUTE8 , MLN.ATTRIBUTE8 ),
NVL(MTLI.ATTRIBUTE9 , MLN.ATTRIBUTE9 ),
NVL(MTLI.ATTRIBUTE10 , MLN.ATTRIBUTE10 ),
NVL(MTLI.ATTRIBUTE11 , MLN.ATTRIBUTE11 ),
NVL(MTLI.ATTRIBUTE12 , MLN.ATTRIBUTE12 ),
NVL(MTLI.ATTRIBUTE13 , MLN.ATTRIBUTE13 ),
NVL(MTLI.ATTRIBUTE14 , MLN.ATTRIBUTE14 ),
NVL(MTLI.ATTRIBUTE15 , MLN.ATTRIBUTE15 ),
NVL(MTLI.DESCRIPTION , MLN.DESCRIPTION ),
NVL(MTLI.VENDOR_NAME , MLN.VENDOR_NAME ),
NVL(MTLI.DATE_CODE , MLN.DATE_CODE ),
NVL(MTLI.CHANGE_DATE , MLN.CHANGE_DATE ),
NVL(MTLI.AGE , MLN.AGE ),
NVL(MTLI.LOT_ATTRIBUTE_CATEGORY , MLN.LOT_ATTRIBUTE_CATEGORY ),
NVL(MTLI.ITEM_SIZE , MLN.ITEM_SIZE ),
NVL(MTLI.COLOR , MLN.COLOR ),
NVL(MTLI.VOLUME , MLN.VOLUME ),
NVL(MTLI.VOLUME_UOM , MLN.VOLUME_UOM ),
NVL(MTLI.PLACE_OF_ORIGIN , MLN.PLACE_OF_ORIGIN ),
NVL(MTLI.BEST_BY_DATE , MLN.BEST_BY_DATE ),
NVL(MTLI.LENGTH , MLN.LENGTH ),
NVL(MTLI.LENGTH_UOM , MLN.LENGTH_UOM ),
NVL(MTLI.RECYCLED_CONTENT , MLN.RECYCLED_CONTENT ),
NVL(MTLI.THICKNESS , MLN.THICKNESS ),
NVL(MTLI.THICKNESS_UOM , MLN.THICKNESS_UOM ),
NVL(MTLI.WIDTH , MLN.WIDTH ),
NVL(MTLI.WIDTH_UOM , MLN.WIDTH_UOM ),
NVL(MTLI.CURL_WRINKLE_FOLD , MLN.CURL_WRINKLE_FOLD ),
NVL(MTLI.C_ATTRIBUTE1 , MLN.C_ATTRIBUTE1 ),
NVL(MTLI.C_ATTRIBUTE2 , MLN.C_ATTRIBUTE2 ),
NVL(MTLI.C_ATTRIBUTE3 , MLN.C_ATTRIBUTE3 ),
NVL(MTLI.C_ATTRIBUTE4 , MLN.C_ATTRIBUTE4 ),
NVL(MTLI.C_ATTRIBUTE5 , MLN.C_ATTRIBUTE5 ),
NVL(MTLI.C_ATTRIBUTE6 , MLN.C_ATTRIBUTE6 ),
NVL(MTLI.C_ATTRIBUTE7 , MLN.C_ATTRIBUTE7 ),
NVL(MTLI.C_ATTRIBUTE8 , MLN.C_ATTRIBUTE8 ),
NVL(MTLI.C_ATTRIBUTE9 , MLN.C_ATTRIBUTE9 ),
NVL(MTLI.C_ATTRIBUTE10 , MLN.C_ATTRIBUTE10 ),
NVL(MTLI.C_ATTRIBUTE11 , MLN.C_ATTRIBUTE11 ),
NVL(MTLI.C_ATTRIBUTE12 , MLN.C_ATTRIBUTE12 ),
NVL(MTLI.C_ATTRIBUTE13 , MLN.C_ATTRIBUTE13 ),
NVL(MTLI.C_ATTRIBUTE14 , MLN.C_ATTRIBUTE14 ),
NVL(MTLI.C_ATTRIBUTE15 , MLN.C_ATTRIBUTE15 ),
NVL(MTLI.C_ATTRIBUTE16 , MLN.C_ATTRIBUTE16 ),
NVL(MTLI.C_ATTRIBUTE17 , MLN.C_ATTRIBUTE17 ),
NVL(MTLI.C_ATTRIBUTE18 , MLN.C_ATTRIBUTE18 ),
NVL(MTLI.C_ATTRIBUTE19 , MLN.C_ATTRIBUTE19 ),
NVL(MTLI.C_ATTRIBUTE20 , MLN.C_ATTRIBUTE20 ),
NVL(MTLI.D_ATTRIBUTE1 , MLN.D_ATTRIBUTE1 ),
NVL(MTLI.D_ATTRIBUTE2 , MLN.D_ATTRIBUTE2 ),
NVL(MTLI.D_ATTRIBUTE3 , MLN.D_ATTRIBUTE3 ),
NVL(MTLI.D_ATTRIBUTE4 , MLN.D_ATTRIBUTE4 ),
NVL(MTLI.D_ATTRIBUTE5 , MLN.D_ATTRIBUTE5 ),
NVL(MTLI.D_ATTRIBUTE6 , MLN.D_ATTRIBUTE6 ),
NVL(MTLI.D_ATTRIBUTE7 , MLN.D_ATTRIBUTE7 ),
NVL(MTLI.D_ATTRIBUTE8 , MLN.D_ATTRIBUTE8 ),
NVL(MTLI.D_ATTRIBUTE9 , MLN.D_ATTRIBUTE9 ),
NVL(MTLI.D_ATTRIBUTE10 , MLN.D_ATTRIBUTE10 ),
NVL(MTLI.N_ATTRIBUTE1 , MLN.N_ATTRIBUTE1 ),
NVL(MTLI.N_ATTRIBUTE2 , MLN.N_ATTRIBUTE2 ),
NVL(MTLI.N_ATTRIBUTE3 , MLN.N_ATTRIBUTE3 ),
NVL(MTLI.N_ATTRIBUTE4 , MLN.N_ATTRIBUTE4 ),
NVL(MTLI.N_ATTRIBUTE5 , MLN.N_ATTRIBUTE5 ),
NVL(MTLI.N_ATTRIBUTE6 , MLN.N_ATTRIBUTE6 ),
NVL(MTLI.N_ATTRIBUTE7 , MLN.N_ATTRIBUTE7 ),
NVL(MTLI.N_ATTRIBUTE8 , MLN.N_ATTRIBUTE8 ),
NVL(MTLI.N_ATTRIBUTE9 , MLN.N_ATTRIBUTE9 ),
NVL(MTLI.N_ATTRIBUTE10 , MLN.N_ATTRIBUTE10 ),
NVL(MTLI.VENDOR_ID , MLN.VENDOR_ID ),
NVL(MTLI.TERRITORY_CODE , MLN.TERRITORY_CODE )
';
SELECT transaction_interface_id
, inventory_item_id
, revision
, organization_id
, transaction_quantity
, primary_quantity
, transaction_uom
, subinventory_code
, locator_id
, transaction_type_id
, transaction_action_id
, acct_period_id
, distribution_account_id
, transfer_subinventory
, transfer_organization
, transfer_locator
, parent_id
, cost_group_id
, transfer_cost_group_id
, lpn_id
, transfer_lpn_id
FROM mtl_transactions_interface
WHERE parent_id = p_parent_id;
SELECT transaction_interface_id
, lot_number
, lot_expiration_date
, transaction_quantity
, primary_quantity
, grade_code
, origination_date
, date_code
, status_id
, change_date
, age
, retest_date
, maturity_date
, lot_attribute_category
, item_size
, color
, volume
, volume_uom
, place_of_origin
, best_by_date
, LENGTH
, length_uom
, recycled_content
, thickness
, thickness_uom
, width
, width_uom
, curl_wrinkle_fold
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute10
, supplier_lot_number
, n_attribute9
, territory_code
, serial_transaction_temp_id
FROM mtl_transaction_lots_interface
WHERE transaction_interface_id = p_transaction_interface_id;
SELECT fm_serial_number
, NVL (to_serial_number, fm_serial_number) to_serial_number
FROM mtl_serial_numbers_interface
WHERE transaction_interface_id = p_serial_transaction_temp_id;
SELECT group_mark_id
, status_id
FROM mtl_serial_numbers
WHERE serial_number = p_serial_number
AND current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT group_mark_id
, status_id
FROM mtl_serial_numbers
WHERE serial_number = p_serial_number
AND current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND lot_number = p_lot_number
AND current_subinventory_code = p_subinventory_code
AND nvl(current_locator_id, -9999) = nvl(p_locator_id, -9999)
AND nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)
AND nvl(revision, '@#') = nvl(p_revision, '@#')
AND current_status in (1,3,6)
AND reservation_id IS NULL;
SELECT serial_number
, status_id
, group_mark_id
FROM mtl_serial_numbers
WHERE lot_number = p_lot_number
AND current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND current_subinventory_code = p_subinventory_code
AND nvl(current_locator_id, -9999) = nvl(p_locator_id, -9999)
AND nvl(lpn_id, -9999) = nvl(p_lpn_id , -9999)
AND nvl(revision, '@#') = nvl(p_revision, '@#')
AND current_status IN (1,3,6)
AND reservation_id IS NULL;
SELECT primary_uom_code
INTO l_start_primary_uom
FROM mtl_system_items
WHERE organization_id = l_mti_csr.organization_id
AND inventory_item_id = l_mti_csr.inventory_item_id;
UPDATE mtl_transactions_interface
SET transaction_quantity = l_transaction_quantity
, primary_quantity = l_primary_quantity
WHERE transaction_interface_id = l_transaction_interface_id;
UPDATE mtl_transaction_lots_interface
SET transaction_quantity = ABS (l_transaction_quantity)
, primary_quantity = ABS (l_primary_quantity)
WHERE transaction_interface_id = l_transaction_interface_id;
SELECT serial_number_control_code
INTO l_serial_code
FROM mtl_system_items
WHERE inventory_item_id = l_mti_csr.inventory_item_id
AND organization_id = l_mti_csr.organization_id;
SELECT column_name
, data_type
, data_length
FROM all_tab_columns
WHERE table_name = UPPER (p_table_name)
AND owner = l_app_owner_schema
AND column_id > 22
ORDER BY column_id; */
l_select_stmt LONG := NULL;
SELECT COUNT (lot_number)
INTO l_lot_num
FROM mtl_lot_numbers mtl
WHERE lot_number = p_lot_number
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
Also, instead of building the SELECT clause here in the loop it is defined in the
global varialble g_select_stmt only once.
*/
/*
FOR l_lot_column_csr IN lot_column_csr ('MTL_TRANSACTION_LOTS_INTERFACE')
LOOP
l_column_idx := l_column_idx + 1;
l_select_stmt :=
l_select_stmt
|| ' NVL(MTLI.'
|| l_lot_attr_tbl (l_column_idx).column_name
|| ', MTL.'
|| l_lot_attr_tbl (l_column_idx).column_name
|| ')';
l_select_stmt :=
l_select_stmt
|| ' , NVL(MTLI.'
|| l_lot_attr_tbl (l_column_idx).column_name
|| ', MTL.'
|| l_lot_attr_tbl (l_column_idx).column_name
|| ')';
l_select_stmt :=
g_select_stmt
|| ' FROM MTL_TRANSACTION_LOTS_INTERFACE MTLI, MTL_TRANSACTIONS_INTERFACE MTI, '
|| ' MTL_LOT_NUMBERS MLN '
|| ' WHERE mtli.transaction_interface_id = :b_interface_id '
|| ' AND mtli.lot_number = :b_lot_number '
|| ' AND mtli.transaction_interface_id = mti.transaction_interface_id '
|| ' AND mln.lot_number = mtli.lot_number (+)'
|| ' AND mln.inventory_item_id = mti.inventory_item_id (+)'
|| ' AND mln.organization_id = mti.organization_id (+)';
l_select_stmt :=
g_select_stmt
|| ' FROM MTL_TRANSACTION_LOTS_INTERFACE MTLI, MTL_TRANSACTIONS_INTERFACE MTI, '
|| ' MTL_LOT_NUMBERS MLN '
|| ' WHERE mtli.transaction_interface_id = :b_interface_id '
|| ' AND mtli.lot_number = :b_lot_number '
|| ' AND mtli.transaction_interface_id = mti.transaction_interface_id '
|| ' AND mln.lot_number = :b_starting_lot_number'
|| ' AND mln.inventory_item_id = mti.inventory_item_id (+)'
|| ' AND mln.organization_id = mti.organization_id (+)';
DBMS_SQL.parse (l_sql_p, l_select_stmt, DBMS_SQL.native);
PROCEDURE update_lot_attr_record (
p_lot_attr_tbl IN inv_lot_sel_attr.lot_sel_attributes_tbl_type
, p_transaction_interface_id IN NUMBER
, p_lot_number IN VARCHAR2
, p_organization_id IN NUMBER
, p_inventory_item_id IN NUMBER
)
IS
l_lot_attr_tbl inv_lot_sel_attr.lot_sel_attributes_tbl_type;
l_update_stmt LONG := 'UPDATE MTL_TRANSACTION_LOTS_INTERFACE SET ';
print_debug ('Inside update attr', 'Validate_Lot_Split');
print_debug ('Count' || p_lot_attr_tbl.COUNT, 'Update Lot attr');
print_debug ('Lot Number' || p_lot_number, 'Update Lot attr');
EXECUTE IMMEDIATE 'Update mtl_transaction_lots_interface
set '
|| p_lot_attr_tbl (i).column_name
|| ' = :1 '
|| 'where transaction_interface_id = :2 '
USING p_lot_attr_tbl (i).column_value
, p_transaction_interface_id;
EXECUTE IMMEDIATE 'Update Mtl_transaction_lots_interface
SET '
|| p_lot_attr_tbl (i).column_name
|| ' = :1 '
|| 'where transaction_interface_id = :2 '
USING
fnd_date.canonical_to_date
(p_lot_attr_tbl (i).column_value
)
, p_transaction_interface_id;
EXECUTE IMMEDIATE 'Update Mtl_transaction_lots_interface
SET '
|| p_lot_attr_tbl (i).column_name
|| ' = :1 '
|| 'where transaction_interface_id = :2 '
USING p_lot_attr_tbl (i).column_value
, p_transaction_interface_id;
fnd_message.set_name ('WMS', 'WMS_UPDATE_ATTR_ERROR');
END update_lot_attr_record;
SELECT transaction_action_id
INTO l_transaction_action_id
FROM mtl_transaction_types
WHERE transaction_type_id = l_transaction_type_id;
UPDATE mtl_transactions_interface
SET cost_group_id = l_rs_cost_group_id_tbl (i)
WHERE transaction_interface_id = l_rs_interface_id_tbl (i);
SELECT DECODE (serial_number_control_code, 2, 'Y', 5, 'Y', 'N')
INTO l_is_serial_controlled
FROM mtl_system_items
WHERE inventory_item_id = l_st_item_id_tbl (1)
AND organization_id = l_st_org_id_tbl (1);
print_debug ('calling update_lot_attr_record'
, 'validate_lot_split_trx'
);
update_lot_attr_record
(p_lot_attr_tbl => l_lot_attr_tbl
, p_transaction_interface_id => l_rs_interface_id_tbl
(i)
, p_lot_number => l_rs_lot_number_tbl
(i)
, p_organization_id => l_rs_org_id_tbl (i)
, p_inventory_item_id => l_rs_item_id_tbl (i)
);
SELECT transaction_action_id
INTO l_transaction_action_id
FROM mtl_transaction_types
WHERE transaction_type_id = l_transaction_type_id;
UPDATE mtl_transactions_interface
SET cost_group_id = l_rs_cost_group_id_tbl (i)
WHERE transaction_interface_id = l_rs_interface_id_tbl (i);
SELECT DECODE (serial_number_control_code, 2, 'Y', 5, 'Y', 'N')
INTO l_is_serial_controlled
FROM mtl_system_items
WHERE inventory_item_id = l_st_item_id_tbl (1)
AND organization_id = l_st_org_id_tbl (1);
SELECT representative_lot_number
INTO l_represenatative_lot
FROM mtl_transactions_interface
WHERE transaction_interface_id = l_st_interface_id_tbl (1);
print_debug ('callign update_lot_attr_record', 'validate_lot_merge');
update_lot_attr_record
(p_lot_attr_tbl => l_lot_attr_tbl
, p_transaction_interface_id => l_rs_interface_id_tbl
(1)
, p_lot_number => l_rs_lot_number_tbl
(1)
, p_organization_id => l_rs_org_id_tbl (1)
, p_inventory_item_id => l_rs_item_id_tbl (1)
);
SELECT distribution_account_id
INTO l_distribution_account_id
FROM mtl_parameters
WHERE organization_id = l_st_org_id_tbl (1);
SELECT transaction_account_id
INTO l_distribution_account_id
FROM wsm_parameters
WHERE organization_id = l_st_org_id_tbl (1);
UPDATE mtl_transactions_interface
SET distribution_account_id = l_distribution_account_id
WHERE transaction_interface_id IN
(l_st_interface_id_tbl (1), l_rs_interface_id_tbl (1));
SELECT transaction_action_id
INTO l_transaction_action_id
FROM mtl_transaction_types
WHERE transaction_type_id = l_transaction_type_id;
UPDATE mtl_transactions_interface
SET cost_group_id = l_rs_cost_group_id_tbl (i)
, distribution_account_id = l_distribution_account_id
WHERE transaction_interface_id = l_rs_interface_id_tbl (i);
SELECT DECODE (serial_number_control_code, 2, 'Y'
, 5, 'Y',
'N')
INTO l_is_serial_controlled
FROM mtl_system_items
WHERE inventory_item_id = l_st_item_id_tbl (1)
AND organization_id = l_st_org_id_tbl (1);
update_lot_attr_record
(p_lot_attr_tbl => l_lot_attr_tbl
, p_transaction_interface_id => l_rs_interface_id_tbl
(1)
, p_lot_number => l_rs_lot_number_tbl
(1)
, p_organization_id => l_rs_org_id_tbl (1)
, p_inventory_item_id => l_rs_item_id_tbl (1)
);