[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(SUM(QUANTITY),0)
FROM oe_lot_serial_numbers
WHERE (line_id = p_oe_order_line_id
OR line_set_id IN
(SELECT line_set_id
FROM oe_order_lines_all
WHERE line_id = p_oe_order_line_id
AND header_id = p_oe_order_header_id))
AND lot_number = v_lot_no;
SELECT primary_quantity , primary_unit_of_measure
FROM rcv_transactions
WHERE transaction_id = l_parent_trx_id ;
SELECT count(LOT_NUMBER)
INTO l_count_lots
FROM MTL_TRANSACTION_LOTS_TEMP
WHERE PRODUCT_TRANSACTION_ID =p_rti_id
AND PRODUCT_CODE = 'RCV' ;
UPDATE mtl_transaction_lots_temp
SET secondary_quantity = l_lot_secondary_quantity
WHERE rowid = p_mtlt_rowid ;
SELECT ENFORCE_RMA_LOT_NUM
INTO l_enforce_rma_lot_value
FROM rcv_parameters
WHERE organization_id=p_to_organization_id;
/* for ROI , 'U' and 'W' are same as we shall not insert any
error in po_interface_errors for 'W' (as we do not ask any question from the user) */
/*check whether lot is specified in the RMA for that Receipt Line.. */
IF g_debug = 1 THEN
print_debug('Before calling INV_ROI_INTEGRATION_GRP.Inv_Rma_lot_info_exists:' || l_progress, 1);
SELECT unit_of_measure
INTO l_rma_unit_of_measure
FROM oe_order_lines_all ,mtl_units_of_measure
WHERE header_id = p_oe_order_header_id
AND line_id = p_oe_order_line_id
AND uom_code = order_quantity_uom;
SELECT PRIMARY_UNIT_OF_MEASURE
INTO l_pri_unit_of_measure
FROM mtl_system_items_b
WHERE INVENTORY_ITEM_ID = p_item_id
AND organization_id = p_to_organization_id;
SELECT transaction_type, destination_type_code, auto_transact_code, parent_transaction_id
INTO l_transaction_type, l_destination_type_code, l_auto_transact_code, l_parent_transaction_id
FROM rcv_transactions_interface
WHERE interface_transaction_id = p_rti_id;
SELECT transaction_type, destination_type_code
INTO l_parent_transaction_type, l_parent_destination_type_code
FROM rcv_transactions WHERE transaction_id = l_parent_transaction_id;
SELECT PARENT_TRANSACTION_ID
INTO l_parent_trx_id
FROM RCV_TRANSACTIONS_INTERFACE
WHERE INTERFACE_TRANSACTION_ID = p_rti_id;
/*update the out variable*/
x_lot_secondary_quantity :=l_lot_secondary_quantity ;
UPDATE mtl_transaction_lots_temp
SET secondary_quantity = l_lot_secondary_quantity
WHERE rowid = p_mtlt_rowid ;
print_debug('updated MTLT with the defaulted secondary quantity: ' || l_progress, 9);
/* No need to update MTLI as rows are deleted from there after moving them to MTLT */
l_progress := '017' ;
print_debug('UPDATE mtl_transaction_lots_temp with not null lot secondary quantity failed for an existing lot' || l_progress, 1);
/*update the out variable*/
x_lot_secondary_quantity :=l_lot_secondary_quantity ;
UPDATE mtl_transaction_lots_temp
SET secondary_quantity = l_lot_secondary_quantity
WHERE rowid = p_mtlt_rowid ;
print_debug('updated MTLT with the defaulted secondary quantity: ' || l_progress, 9);
/* No need to update MTLI as rows are deleted from there after moving them to MTLT */
l_progress := '017' ;
print_debug('UPDATE mtl_transaction_lots_temp with not null lot secondary quantity failed for an existing lot' || l_progress, 1);
/*update the out variable*/
x_lot_secondary_quantity :=l_lot_secondary_quantity ;
UPDATE mtl_transaction_lots_temp
SET secondary_quantity = l_lot_secondary_quantity
WHERE rowid = p_mtlt_rowid ;
print_debug('updated MTLT with NULL secondary quantity: ' || l_progress, 9);
print_debug('UPDATE mtl_transaction_lots_temp with null lot secondary quantity failed for an existing lot' || l_progress, 1);
/*update the out variable*/
x_lot_secondary_quantity :=l_lot_secondary_quantity ;
UPDATE mtl_transaction_lots_temp
SET secondary_quantity = l_lot_secondary_quantity
WHERE rowid = p_mtlt_rowid ;
print_debug('updated MTLT with the defaulted secondary quantity: ' || l_progress, 9);
/* No need to update MTLI as rows are deleted from there after moving them to MTLT */
l_progress := '017' ;
print_debug('UPDATE mtl_transaction_lots_temp with not null lot secondary quantity failed for an
existing lot' || l_progress, 1);
/*update the out variable*/
x_lot_secondary_quantity :=l_lot_secondary_quantity ;
UPDATE mtl_transaction_lots_temp
SET secondary_quantity = l_lot_secondary_quantity
WHERE rowid = p_mtlt_rowid ;
print_debug('updated MTLT with the defaulted secondary quantity: ' || l_progress, 9);
/* No need to update MTLI as rows are deleted from there after moving them to MTLT */
EXCEPTION
WHEN OTHERS THEN
IF g_debug = 1 THEN
print_debug('UPDATE mtl_transaction_lots_temp with not null lot secondary quantity failed for a new lot'|| l_progress, 4);
/*update the out variable*/
x_lot_secondary_quantity := l_lot_secondary_quantity ;
UPDATE mtl_transaction_lots_temp
SET secondary_quantity = l_lot_secondary_quantity
WHERE rowid = p_mtlt_rowid ;
print_debug('updated MTLT with NULL secondary quantity: ' || l_progress, 9);
print_debug('update table with NULL secondary quantity:' || l_progress, 1);
print_debug('UPDATE mtl_transaction_lots_temp with null lot secondary quantity failed for a new lot'|| l_progress, 1);
# Create the new lot and then update MTLT with the new lot attributes.
#
# Create lot specific conversions for :
# Primary UOM and Secondary UOM
#
# DESIGN REFERENCES:
# http://files.oraclecorp.com/content/AllPublic/Workspaces/
# Inventory%20Convergence-Public/Design/Oracle%20Purchasing/TDD/PO_ROI_TDD.zip
#
# MODIFICATION HISTORY
# 10-AUG-2004 Punit Kumar Created
# 01-SEP-2004 Punit Kumar Changed the way l_lot_rec was getting populated
#
#########################################################################*/
PROCEDURE INV_New_lot(
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_api_version IN NUMBER DEFAULT 1.0 ,
p_init_msg_lst IN VARCHAR2 := FND_API.G_FALSE ,
p_source_document_code IN VARCHAR2 ,
p_item_id IN NUMBER ,
p_from_organization_id IN NUMBER ,
p_to_organization_id IN NUMBER ,
p_lot_number IN VARCHAR2 ,
p_lot_quantity IN NUMBER ,
p_lot_secondary_quantity IN NUMBER ,
p_line_secondary_quantity IN NUMBER ,
p_primary_unit_of_measure IN VARCHAR2 ,
p_secondary_unit_of_measure IN VARCHAR2 ,
p_uom_code IN VARCHAR2 ,
p_secondary_uom_code IN VARCHAR2 ,
p_reason_id IN NUMBER ,
P_MLN_REC IN mtl_lot_numbers%ROWTYPE ,
p_mtlt_rowid IN ROWID
)
IS
/* copy all values from mtl_lot_numbers for inter org transfer */
CURSOR C_MLN (l_lot_number VARCHAR2,
l_item_id NUMBER,
l_from_organization_id NUMBER
) IS
SELECT *
FROM mtl_lot_numbers
WHERE lot_number = l_lot_number
AND inventory_item_id=l_item_id
AND organization_id = l_from_organization_id;
l_qty_update_tbl MTL_LOT_UOM_CONV_PUB.quantity_update_rec_type ;
SELECT GRADE_CONTROL_FLAG
INTO l_grade_controlled_flag
FROM mtl_system_items_b
WHERE INVENTORY_ITEM_ID = p_item_id
AND ORGANIZATION_ID = p_to_organization_id;
print_debug('inv_new_lot::SELECT GRADE_CONTROL_FLAG has failed with a Unexpected exception'|| l_progress, 1);
select msi.lot_control_code
into l_from_lot_ctrl
from mtl_system_items msi
where msi.inventory_item_id = p_item_id
and msi.organization_id = p_from_organization_id;
l_lot_rec.last_update_date :=SYSDATE;
print_debug('l_lot_rec.LAST_UPDATE_DATE:'|| l_lot_rec.LAST_UPDATE_DATE||':'|| l_progress, 1);
print_debug('l_lot_rec.LAST_UPDATED_BY:'|| l_lot_rec.LAST_UPDATED_BY||':'|| l_progress, 1);
print_debug('l_lot_rec.LAST_UPDATE_LOGIN:'||l_lot_rec.LAST_UPDATE_LOGIN ||':'|| l_progress, 1);
print_debug('l_lot_rec.PROGRAM_UPDATE_DATE:'|| l_lot_rec.PROGRAM_UPDATE_DATE ||':'|| l_progress, 1);
print_debug('x_lot_rec.LAST_UPDATE_DATE:'|| x_lot_rec.LAST_UPDATE_DATE||':'|| l_progress, 1);
print_debug('x_lot_rec.LAST_UPDATED_BY:'|| x_lot_rec.LAST_UPDATED_BY||':'|| l_progress, 1);
print_debug('x_lot_rec.LAST_UPDATE_LOGIN:'||x_lot_rec.LAST_UPDATE_LOGIN ||':'|| l_progress, 1);
print_debug('x_lot_rec.PROGRAM_UPDATE_DATE:'|| x_lot_rec.PROGRAM_UPDATE_DATE ||':'|| l_progress, 1);
UPDATE mtl_transaction_lots_temp
SET
lot_expiration_date = x_lot_rec.expiration_date ,
attribute_category = x_lot_rec.attribute_category ,
lot_attribute_category = x_lot_rec.lot_attribute_category ,
grade_code = x_lot_rec.grade_code ,
origination_date = x_lot_rec.origination_date ,
date_code = x_lot_rec.date_code ,
status_id = x_lot_rec.status_id ,
change_date = x_lot_rec.change_date ,
age = x_lot_rec.age ,
retest_date = x_lot_rec.retest_date ,
maturity_date = x_lot_rec.maturity_date ,
item_size = x_lot_rec.item_size ,
color = x_lot_rec.color ,
volume = x_lot_rec.volume ,
volume_uom = x_lot_rec.volume_uom ,
place_of_origin = x_lot_rec.place_of_origin ,
best_by_date = x_lot_rec.best_by_date ,
LENGTH = x_lot_rec.LENGTH ,
length_uom = x_lot_rec.length_uom ,
recycled_content = x_lot_rec.recycled_content ,
thickness = x_lot_rec.thickness ,
thickness_uom = x_lot_rec.thickness_uom ,
width = x_lot_rec.width ,
width_uom = x_lot_rec.width_uom ,
territory_code = x_lot_rec.territory_code ,
supplier_lot_number = x_lot_rec.supplier_lot_number ,
vendor_name = x_lot_rec.vendor_name ,
creation_date = SYSDATE ,
created_by = x_lot_rec.created_by ,
last_update_date = SYSDATE ,
last_updated_by = x_lot_rec.last_updated_by ,
parent_lot_number = x_lot_rec.parent_lot_number ,
origination_type = x_lot_rec.origination_type ,
expiration_action_code = x_lot_rec.expiration_action_code ,
expiration_action_date = x_lot_rec.expiration_action_date ,
hold_date = x_lot_rec.hold_date ,
DESCRIPTION = x_lot_rec.DESCRIPTION ,
CURL_WRINKLE_FOLD = x_lot_rec.CURL_WRINKLE_FOLD ,
VENDOR_ID = x_lot_rec.VENDOR_ID
WHERE ROWID = p_mtlt_rowid ;
print_debug('Update MTLT with the out record type parameter x_lot_rec:' || l_progress, 1);
SELECT CREATE_LOT_UOM_CONVERSION
INTO l_permission_value
FROM mtl_parameters
WHERE Organization_id = p_to_organization_id;
SELECT distinct(uom_class)
INTO l_primary_uom_class
FROM MTL_UNITS_OF_MEASURE
WHERE unit_of_measure = P_PRIMARY_UNIT_OF_MEASURE;
SELECT distinct(uom_class)
INTO l_secondary_uom_class
FROM MTL_UNITS_OF_MEASURE
WHERE unit_of_measure = p_secondary_unit_of_measure;
SELECT secondary_default_ind
INTO l_secondary_default_ind
FROM mtl_system_items_b
WHERE inventory_item_id =p_item_id
AND organization_id =p_to_organization_id;
p_lot_uom_conv_rec.last_updated_by := fnd_global.user_id ;
p_lot_uom_conv_rec.last_update_date := SYSDATE ;
p_lot_uom_conv_rec.last_update_login := fnd_global.login_id ;
p_lot_uom_conv_rec.program_update_date := NULL ;
print_debug('p_lot_uom_conv_rec.last_updated_by:'||p_lot_uom_conv_rec.last_updated_by, 1);
print_debug('p_lot_uom_conv_rec.last_update_date:'||p_lot_uom_conv_rec.last_update_date, 1);
print_debug('p_lot_uom_conv_rec.last_update_login:'||p_lot_uom_conv_rec.last_update_login, 1);
print_debug('p_lot_uom_conv_rec.program_update_date:'||p_lot_uom_conv_rec.program_update_date, 1);
p_action_type =>'I' /*Database action type ('I' for insert or 'U' for update)*/ ,
p_update_type_indicator =>5 ,
p_reason_id =>p_reason_id ,
p_batch_id =>NULL /*(Since we are not updating batch quantities)*/ ,
p_process_data =>'Y', -- Bug 4019726 FND_API.G_TRUE ,
p_lot_uom_conv_rec =>p_lot_uom_conv_rec ,
p_qty_update_tbl =>l_qty_update_tbl ,
x_return_status =>l_return_status ,
x_msg_count =>l_msg_count ,
x_msg_data =>l_msg_data ,
x_sequence =>l_sequence
);
/* p_update_type_indicator Indicates if there is a quantity change associated with the lot uom conversion change and if so,
what kind of change
(0 for Update On-Hand Balances,
1 for Recalculate Batch Primary Quantity,
2 for Recalculate Batch Secondary Quantity,
3 for Recalculate On-Hand Primary Quantity,
4 for Recalculate On-Hand Secondary Quantity,
5 for No Quantity Updates)
*/
l_progress := '035';
# Sum it up and update the Source Doc primary quantity with this.
#
# 2) For lot controlled and dual uom controlled items ,loop through each
# record in MTLT and sum the lot_secondary_quantity and update the
# secondary receipt line quantity with this.
#
# DESIGN REFERENCES:
# http://files.oraclecorp.com/content/AllPublic/Workspaces/
# Inventory%20Convergence-Public/Design/Oracle%20Purchasing/TDD/PO_ROI_TDD.zip
#
# MODIFICATION HISTORY
# 13-SEP-2004 Punit Kumar Created
#
#########################################################################*/
PROCEDURE INV_Synch_Quantities(
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_data OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_sum_sourcedoc_quantity OUT NOCOPY NUMBER ,
x_sum_rti_secondary_quantity OUT NOCOPY NUMBER ,
p_api_version IN NUMBER DEFAULT 1.0 ,
p_init_msg_lst IN VARCHAR2 := FND_API.G_FALSE ,
p_inventory_item_id IN NUMBER ,
p_to_organization_id IN NUMBER ,
p_lot_number IN VARCHAR2 ,
p_transaction_unit_of_measure IN VARCHAR2 ,
p_sourcedoc_unit_of_meaure IN VARCHAR2 ,
p_lot_quantity IN NUMBER ,
p_line_secondary_quantity IN NUMBER ,
p_secondary_unit_of_measure IN VARCHAR2 ,
p_lot_secondary_quantity IN NUMBER
)
IS
/*local variables declaration*/
l_api_name VARCHAR2(30) := 'INV_Synch_Quantities';
SELECT distinct(uom_class)
INTO l_recv_uom_class
FROM MTL_UNITS_OF_MEASURE
WHERE UNIT_OF_MEASURE = p_transaction_unit_of_measure;
SELECT distinct(uom_class)
INTO l_sourcedoc_uom_class
FROM MTL_UNITS_OF_MEASURE
WHERE UNIT_OF_MEASURE = p_sourcedoc_unit_of_meaure;
/*Logic is to get the sum of above quantity here in this procedure and update RTI
at the the end of validate_lot_serial_info (where the loop for all MTLT lots ends).*/
IF g_debug = 1 THEN
print_debug('x_sum_sourcedoc_quantity :' ||x_sum_sourcedoc_quantity , 1);
/*Here also logic is to get the sum of above quantity here in this procedure and update
RTI at the the end of validate_lot_serial_info (where the loop for all MTLT lots ends).*/
IF g_debug = 1 THEN
print_debug('x_sum_rti_secondary_quantity :' ||x_sum_rti_secondary_quantity , 1);
SELECT count (*)
FROM oe_lot_serial_numbers
WHERE (line_id = p_oe_order_line_id
OR line_set_id IN
(SELECT line_set_id
FROM oe_order_lines_all
WHERE line_id = p_oe_order_line_id
AND header_id = p_oe_order_header_id)) ;
SELECT SUM(mtln.primary_quantity)
FROM mtl_material_transactions mmt ,
mtl_transaction_lot_numbers mtln
WHERE mmt.trx_source_line_id IN
(SELECT line_id
FROM oe_order_lines_all
WHERE line_set_id = l_line_set_id)
AND mmt.transaction_source_type_id = 12
AND mmt.transaction_action_id in (1,27)
AND mmt.transaction_type_id in (15,36,37)
AND mmt.inventory_item_id = p_item_id
AND mmt.organization_id = p_to_organization_id
AND nvl(mtln.lot_number,-1) = nvl(p_lot_number,-1) --bug8786451,lot number missing when fetch the received qty
AND mtln.TRANSACTION_ID = mmt.transaction_id;
SELECT SUM(mtln.primary_quantity)
FROM mtl_material_transactions mmt ,
mtl_transaction_lot_numbers mtln
WHERE mmt.trx_source_line_id = p_oe_order_line_id
AND mmt.transaction_source_type_id = 12
and mmt.transaction_action_id in (1,27)
and mmt.transaction_type_id in (15,36,37)
and mmt.inventory_item_id = p_item_id
and mmt.organization_id = p_to_organization_id
and nvl(mtln.lot_number,-1) = nvl(p_lot_number,-1) --bug8786451,lot number missing when fetch the received qty
and mtln.TRANSACTION_ID = mmt.transaction_id;
SELECT SUM(transaction_quantity)
FROM mtl_transaction_lots_temp
WHERE product_code = 'RCV'
AND product_transaction_id = p_rti_id
AND lot_number= p_lot_number;
SELECT SUM(QUANTITY)
FROM oe_lot_serial_numbers
WHERE (line_id = p_oe_order_line_id
OR line_set_id IN
(SELECT line_set_id
FROM oe_order_lines_all
WHERE line_id = p_oe_order_line_id
AND header_id = p_oe_order_header_id))
AND lot_number = p_lot_number;
SELECT line_set_id
INTO l_line_set_id
FROM oe_order_lines_all
WHERE line_id = p_oe_order_line_id
AND header_id = p_oe_order_header_id;
SELECT unit_of_measure
INTO l_rma_lot_unit_of_measure
FROM oe_order_lines_all ,mtl_units_of_measure
WHERE header_id = p_oe_order_header_id
AND line_id = p_oe_order_line_id
AND uom_code = order_quantity_uom;
SELECT PRIMARY_UNIT_OF_MEASURE
INTO l_lot_recv_unit_of_measure
FROM mtl_system_items_b
WHERE INVENTORY_ITEM_ID = p_item_id
AND organization_id = p_to_organization_id;
SELECT lot_control_code,
child_lot_flag
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;