[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_rcv_lots_supply(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
p_interface_transaction_id IN NUMBER,
p_shipment_line_id IN NUMBER,
p_supply_source_id IN NUMBER,
p_source_type_code IN VARCHAR2,
p_transaction_type IN VARCHAR2
) IS
CURSOR c IS
SELECT rls.ROWID
FROM rcv_lots_supply rls,
rcv_transactions rt
WHERE rt.interface_transaction_id = p_interface_transaction_id
AND rls.transaction_id = rt.transaction_id;
SELECT mtlt.lot_number,
SUM(mtlt.primary_quantity),
SUM(mtlt.transaction_quantity),
rti.shipment_header_id,
rti.item_id,
rti.unit_of_measure,
rti.to_organization_id
FROM mtl_transaction_lots_temp mtlt,
rcv_transactions_interface rti
WHERE product_transaction_id = l_interface_id
AND product_code = 'RCV'
AND rti.interface_transaction_id = mtlt.product_transaction_id
GROUP BY mtlt.lot_number,
rti.shipment_header_id,
rti.shipment_line_id,
rti.item_id,
rti.unit_of_measure,
rti.to_organization_id;
SELECT rls.quantity,
rls.primary_quantity,
rls.shipment_line_id
FROM rcv_lots_supply rls
WHERE rls.lot_num = l_lot_num
AND rls.supply_type_code = 'SHIPMENT'
AND EXISTS(SELECT 1
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_header_id = l_shipment_header_id
AND rsl.item_id = l_item_id
AND rsl.shipment_line_id = rls.shipment_line_id);
l_qty_to_be_updated NUMBER;
l_primary_qty_to_be_updated NUMBER;
* In this case we do not error nor insert. So return.
*/
SELECT COUNT(*)
INTO l_lot_count
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.product_transaction_id = p_interface_transaction_id
AND mtlt.product_code = 'RCV';
/* We need to insert into rcv_lots_supply and
* rcv_serials_supply table only when we come through ROI
* or when we come through desktop and have lpn info.
* We insert lpn_id in rcv_supply. So return if there is
* a value and validation_flag is N.
*/
SELECT NVL(validation_flag, 'N')
INTO l_validation_flag
FROM rcv_transactions_interface
WHERE interface_transaction_id = p_interface_transaction_id;
SELECT NVL(lpn_id, -999)
INTO l_lpn_id
FROM rcv_supply
WHERE supply_source_id = p_supply_source_id;
SELECT transaction_id,
organization_id
INTO l_transaction_id,
l_organization_id
FROM rcv_transactions rt
WHERE rt.interface_transaction_id = p_interface_transaction_id;
asn_debug.put_line('before insert_lot_supply');
insert_lot_supply(p_interface_transaction_id,
'RECEIVING',
p_supply_source_id,
x_return_status
);
asn_debug.put_line('After insert_lot_supply');
* then we need to update the values for the shipment
* supply in rcv_lots_supply.
*/
/* INVCONV , update for process transactions also.
Remove the process specific restriction. Punit Kumar */
-- roi enhacements for OPM.bug# 3061052
-- don't update for OPM transactions.
/* IF (gml_process_flags.check_process_orgn(p_organization_id => l_organization_id) = 0) THEN */
IF ( p_transaction_type = 'RECEIVE'
AND p_source_type_code IN('INVENTORY', 'REQ')) THEN --{
/* Bug 3376348, 3459830.
* It might happen that the original shipment lines are split
* into multiple shipment_lines (when lpn has 2 lots for eg).
* So instead of using shipment_line_id to delete use
* shipment_header_id.
*/
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line('In insert rcv_lots_supply for source type code INVENTORY or REQ');
l_primary_qty_to_be_updated,
l_qty_to_be_updated,
l_shipment_header_id,
l_item_id,
l_txn_uom,
l_to_org_id;
asn_debug.put_line('l_primary_qty_to_be_updated ' || l_primary_qty_to_be_updated);
asn_debug.put_line('l_qty_to_be_updated ' || l_qty_to_be_updated);
SELECT MAX(primary_unit_of_measure)
INTO l_primary_uom
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id = l_item_id
AND mtl_system_items.organization_id = l_to_org_id;
/* l_qty_to_be_updated shd be in
* terms of the parent's uom. For
* shipment supply qty, it must be
* in terms of uom in rsl.
*/
OPEN supply_quantity(l_lot_num,
l_shipment_header_id,
l_item_id
);
OR l_primary_qty_to_be_updated = 0;
SELECT unit_of_measure
INTO l_parent_uom
FROM rcv_shipment_lines
WHERE shipment_line_id = l_ship_line_id;
l_qty_to_be_updated := rcv_transactions_interface_sv.convert_into_correct_qty(l_primary_qty_to_be_updated,
l_primary_uom,
l_item_id,
l_parent_uom
);
asn_debug.put_line('l_qty_to_be_updated ' || l_qty_to_be_updated);
asn_debug.put_line('l_primary_qty_to_be_updated ' || l_primary_qty_to_be_updated);
IF (l_rls_primary_qty >= l_primary_qty_to_be_updated) THEN --{
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line('rls primary qty greater');
asn_debug.put_line('l_qty_to_be_updated ' || l_qty_to_be_updated);
asn_debug.put_line('l_primary_qty_to_be_updated ' || l_primary_qty_to_be_updated);
UPDATE rcv_lots_supply rls
SET quantity = quantity - l_qty_to_be_updated,
primary_quantity = primary_quantity - l_primary_qty_to_be_updated
WHERE rls.lot_num = l_lot_num
AND shipment_line_id = l_ship_line_id
AND rls.supply_type_code = 'SHIPMENT';
l_qty_to_be_updated := 0;
l_primary_qty_to_be_updated := 0;
asn_debug.put_line('l_qty_to_be_updated ' || l_qty_to_be_updated);
asn_debug.put_line('l_primary_qty_to_be_updated ' || l_primary_qty_to_be_updated);
UPDATE rcv_lots_supply rls
SET quantity = 0,
primary_quantity = 0
WHERE rls.lot_num = l_lot_num
AND shipment_line_id = l_ship_line_id
AND rls.supply_type_code = 'SHIPMENT';
l_qty_to_be_updated := l_qty_to_be_updated - l_rls_qty;
l_primary_qty_to_be_updated := l_primary_qty_to_be_updated - l_rls_primary_qty;
asn_debug.put_line('l_qty_to_be_updated ' || l_qty_to_be_updated);
asn_debug.put_line('l_primary_qty_to_be_updated ' || l_primary_qty_to_be_updated);
asn_debug.put_line('Exit insert_rcv_lots_supply');
asn_debug.put_line('no_data_found insert_rcv_lots_supply');
INSERT INTO po_interface_errors
(interface_type,
interface_transaction_id,
error_message,
processing_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT 'RECEIVING',
p_interface_transaction_id,
'RCV_INSERT_LOT_SUPPLY_FAIL',
SYSDATE,
rti.creation_date,
rti.created_by,
rti.last_update_date,
rti.last_updated_by,
rti.last_update_login,
rti.request_id,
rti.program_application_id,
rti.program_id,
rti.program_update_date
FROM rcv_transactions_interface rti
WHERE rti.interface_transaction_id = p_interface_transaction_id;
asn_debug.put_line('others insert_rcv_lots_supply');
INSERT INTO po_interface_errors
(interface_type,
interface_transaction_id,
error_message,
processing_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT 'RECEIVING',
p_interface_transaction_id,
'RCV_INSERT_LOT_SUPPLY_ERROR',
SYSDATE,
rti.creation_date,
rti.created_by,
rti.last_update_date,
rti.last_updated_by,
rti.last_update_login,
rti.request_id,
rti.program_application_id,
rti.program_id,
rti.program_update_date
FROM rcv_transactions_interface rti
WHERE rti.interface_transaction_id = p_interface_transaction_id;
END insert_rcv_lots_supply;
PROCEDURE insert_rcv_serials_supply(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
p_interface_transaction_id IN NUMBER,
p_shipment_line_id IN NUMBER,
p_supply_source_id IN NUMBER,
p_source_type_code IN VARCHAR2,
p_transaction_type IN VARCHAR2
) IS
CURSOR select_serials(
p_interface_transaction_id NUMBER
) IS
SELECT msnt.fm_serial_number,
msnt.to_serial_number,
mtlt.lot_number
FROM mtl_serial_numbers_temp msnt,
mtl_transaction_lots_temp mtlt
WHERE msnt.product_transaction_id = p_interface_transaction_id
AND msnt.product_code = 'RCV'
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id(+);
l_select_serials select_serials%ROWTYPE;
l_delete_shipment_supply VARCHAR2(1) := 'N';
* In this case we do not error nor insert. So return.
*/
SELECT COUNT(*)
INTO l_serial_count
FROM mtl_serial_numbers_temp msnt
WHERE msnt.product_transaction_id = p_interface_transaction_id
AND msnt.product_code = 'RCV';
/* We need to insert into rcv_lots_supply and
* rcv_serials_supply table only when we come through ROI
* or when we come through desktop and have lpn info.
* We insert lpn_id in rcv_supply. So return if there is
* a value and validation_flag is N.
*/
SELECT NVL(validation_flag, 'N')
INTO l_validation_flag
FROM rcv_transactions_interface
WHERE interface_transaction_id = p_interface_transaction_id;
SELECT NVL(lpn_id, -999)
INTO l_lpn_id
FROM rcv_supply
WHERE supply_source_id = p_supply_source_id;
OPEN select_serials(p_interface_transaction_id);
* then we need to delete the values for the shipment
* supply in rcv_shipment_supply since we would have created the
* receiving supply for the serial numbers that are used for
* receiving. Set l_delete_shipment_supply to Y. This will be
* used later to delete the shipment serial supply row.
*/
IF ( p_transaction_type = 'RECEIVE'
AND p_source_type_code IN('INVENTORY', 'REQ')) THEN
l_delete_shipment_supply := 'Y';
FETCH select_serials INTO l_select_serials;
EXIT WHEN select_serials%NOTFOUND;
split_serial_number(l_select_serials.fm_serial_number,
l_serial_prefix,
l_from_serial_number
);
split_serial_number(l_select_serials.to_serial_number,
l_serial_prefix,
l_to_serial_number
);
l_serial_num_length := LENGTH(l_select_serials.fm_serial_number);
SELECT transaction_id
INTO l_transaction_id
FROM rcv_transactions rt
WHERE rt.interface_transaction_id = p_interface_transaction_id;
insert_serial_supply(p_interface_transaction_id,
l_select_serials.lot_number,
l_cur_serial_number,
'RECEIVING',
p_supply_source_id,
x_return_status
);
asn_debug.put_line('After insert_serial_supply ');
asn_debug.put_line('l_delete_shipment_supply ' || l_delete_shipment_supply);
IF (l_delete_shipment_supply = 'Y') THEN
SELECT shipment_header_id,
item_id
INTO l_shipment_header_id,
l_item_id
FROM rcv_shipment_lines
WHERE shipment_line_id = p_shipment_line_id;
* So instead of using shipment_line_id to delete use
* shipment_header_id.
*/
DELETE FROM rcv_serials_supply rss
WHERE supply_type_code = 'SHIPMENT'
AND ( l_select_serials.lot_number IS NULL
OR NVL(lot_num, l_select_serials.lot_number) = l_select_serials.lot_number)
AND (serial_num BETWEEN(l_serial_prefix || LPAD(TO_CHAR(l_from_serial_number),
l_serial_suffix_length,
'0'
)) AND(l_serial_prefix || LPAD(TO_CHAR(l_to_serial_number),
l_serial_suffix_length,
'0'
)))
AND EXISTS(SELECT 1
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_header_id = l_shipment_header_id
AND rsl.shipment_line_id = rss.shipment_line_id
AND rsl.item_id = l_item_id);
CLOSE select_serials;
asn_debug.put_line('Exit insert_rcv_serials_supply ');
asn_debug.put_line('no_data_found insert_rcv_serials_supply ');
INSERT INTO po_interface_errors
(interface_type,
interface_transaction_id,
error_message,
processing_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT 'RECEIVING',
p_interface_transaction_id,
'RCV_INSERT_SERIAL_SUPPLY_FAIL',
SYSDATE,
rti.creation_date,
rti.created_by,
rti.last_update_date,
rti.last_updated_by,
rti.last_update_login,
rti.request_id,
rti.program_application_id,
rti.program_id,
rti.program_update_date
FROM rcv_transactions_interface rti
WHERE rti.interface_transaction_id = p_interface_transaction_id;
asn_debug.put_line('others insert_rcv_serials_supply ');
INSERT INTO po_interface_errors
(interface_type,
interface_transaction_id,
error_message,
processing_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT 'RECEIVING',
p_interface_transaction_id,
'RCV_INSERT_SERIAL_SUPPLY_ERROR',
SYSDATE,
rti.creation_date,
rti.created_by,
rti.last_update_date,
rti.last_updated_by,
rti.last_update_login,
rti.request_id,
rti.program_application_id,
rti.program_id,
rti.program_update_date
FROM rcv_transactions_interface rti
WHERE rti.interface_transaction_id = p_interface_transaction_id;
END insert_rcv_serials_supply;
PROCEDURE update_rcv_lots_supply(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
p_interface_transaction_id IN NUMBER,
p_transaction_type IN VARCHAR2,
p_shipment_line_id IN NUMBER,
p_source_type_code IN VARCHAR2,
p_parent_supply_id IN NUMBER,
p_correction_type IN VARCHAR2
) IS
CURSOR lot_cursor(
p_interface_id NUMBER
) IS
/* INVCONV , Remove sublot_num as part of new lot model. Punit Kumar */
/** OPM change Bug# 3061052 add sublot_num **/
SELECT mtlt.lot_number,
/* INVCONV */
/* mtlt.sublot_num, */
/* end , INVCONV*/
SUM(mtlt.primary_quantity),
SUM(mtlt.transaction_quantity),
SUM(mtlt.secondary_quantity),
rti.shipment_header_id,
rti.item_id,
rti.unit_of_measure,
rti.to_organization_id
FROM mtl_transaction_lots_temp mtlt,
rcv_transactions_interface rti
WHERE product_transaction_id = p_interface_id
AND product_code = 'RCV'
AND rti.interface_transaction_id = mtlt.product_transaction_id
GROUP BY mtlt.lot_number,
/* INVCONV */
/* mtlt.sublot_num, */
/* end , INVCONV*/
rti.shipment_header_id,
rti.shipment_line_id,
rti.item_id,
rti.unit_of_measure,
rti.to_organization_id;
l_update_shipment_supply VARCHAR2(1) := 'N';
SELECT rls.quantity,
rls.primary_quantity
FROM rcv_lots_supply rls
WHERE rls.lot_num = l_lot_num
/* INVCONV*/
/*
AND ( (rls.sublot_num = l_sublot_num)
OR ( rls.sublot_num IS NULL
AND l_sublot_num IS NULL))
*/
/* end , INVCONV */
AND rls.supply_type_code = 'RECEIVING'
AND rls.transaction_id = p_parent_supply_id;
SELECT rls.quantity,
rls.primary_quantity,
rls.shipment_line_id
FROM rcv_lots_supply rls
WHERE rls.lot_num = l_lot_num
AND rls.supply_type_code = 'SHIPMENT'
AND EXISTS(SELECT 1
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_header_id = l_shipment_header_id
AND rsl.item_id = l_item_id
AND rsl.shipment_line_id = rls.shipment_line_id);
l_qty_to_be_updated NUMBER;
l_primary_qty_to_be_updated NUMBER;
l_secondary_qty_to_be_updated NUMBER;
asn_debug.put_line('Enter update_rcv_lots_supply ');
* In this case we do not error nor insert. So return.
*/
SELECT COUNT(*)
INTO l_lot_count
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.product_transaction_id = p_interface_transaction_id
AND mtlt.product_code = 'RCV';
/* We need to insert into rcv_lots_supply and
* rcv_serials_supply table only when we come through ROI
* or when we come through desktop and have lpn info.
* We insert lpn_id in rcv_supply. So return if there is
* a value and validation_flag is N.
*/
SELECT NVL(validation_flag, 'N')
INTO l_validation_flag
FROM rcv_transactions_interface
WHERE interface_transaction_id = p_interface_transaction_id;
SELECT NVL(lpn_id, -999)
INTO l_lpn_id
FROM rcv_supply
WHERE supply_source_id = p_parent_supply_id;
/* When we update rcv_supply, we call this procedure and set
* the p_correction_type depending upon whether we need to add
* or subtract supply from rcv_lots_supply.
*/
IF (p_correction_type = 'POSITIVE') THEN
l_factor := -1;
/* We need to insert or update rcv_lot_supply only when there is
* already a row existing in rcv_lots_supply for a corresponding
* row in rcv_supply. If not dont do anything.
*/
SELECT COUNT(*)
INTO l_count
FROM rcv_lots_supply
WHERE transaction_id = p_parent_supply_id
AND supply_type_code = 'RECEIVING';
SELECT transaction_type,
organization_id,
unit_of_measure,
secondary_unit_of_measure
INTO l_parent_trans_type,
l_organization_id,
l_parent_uom,
l_parent_secondary_uom
FROM rcv_transactions
WHERE transaction_id = p_parent_supply_id;
/* INVCONV , Update for OPM transactions also. Punit Kumar */
-- roi enhacements for OPM.bug# 3061052
-- don't update for OPM transactions.
/* IF (gml_process_flags.check_process_orgn(p_organization_id => l_organization_id) = 0) THEN */
IF ( p_transaction_type = 'CORRECTION'
AND l_parent_trans_type = 'RECEIVE'
AND p_source_type_code IN('INVENTORY', 'REQ')) THEN --{
l_update_shipment_supply := 'Y';
asn_debug.put_line('l_update_shipment_supply ' || l_update_shipment_supply);
asn_debug.put_line(' INVCONV , Update shipment supply for OPM transactions also.');
l_primary_qty_to_be_updated,
l_qty_to_be_updated,
l_secondary_qty_to_be_updated,
l_shipment_header_id,
l_item_id,
l_txn_uom,
l_to_org_id;
asn_debug.put_line('INVCONV, Subllot_num has been removed in update_lot_supply1 ');
asn_debug.put_line('l_primary_qty_to_be_updated ' || l_primary_qty_to_be_updated);
asn_debug.put_line('l_secondary_qty_to_be_updated ' || l_secondary_qty_to_be_updated);
asn_debug.put_line('l_qty_to_be_updated ' || l_qty_to_be_updated);
/* If there is already a row existing for this lot_num, update
* the quantity. Else insert a new one since this might be a
* new lot_num for this transaction. At this point, inventory
* would have validated these numbers.
*/
/* INVCONV , Remove sublot_num. Punit Kumar */
/** OPM change Bug# 3061052 added sublot_num check**/
SELECT COUNT(*)
INTO l_count1
FROM rcv_lots_supply
WHERE transaction_id = p_parent_supply_id
AND lot_num = l_lot_num
/* INVCONV */
/*
AND ( (sublot_num = l_sublot_num)
OR ( sublot_num IS NULL
AND l_sublot_num IS NULL))
*/
/*end , INVCONV*/
AND supply_type_code = 'RECEIVING';
asn_debug.put_line('INVCONV, Subllot_num has been removed in update_lot_supply2 ');
asn_debug.put_line('Before insert_lot_supply ' || l_count1);
insert_lot_supply(p_interface_transaction_id,
'RECEIVING',
p_parent_supply_id,
x_return_status
);
asn_debug.put_line('After insert_lot_supply ' || l_count1);
asn_debug.put_line('Else update rcv_lots_supply');
SELECT MAX(primary_unit_of_measure)
INTO l_primary_uom
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id = l_item_id
AND mtl_system_items.organization_id = l_to_org_id;
/* l_qty_to_be_updated shd be in
* terms of the parent's uom. For
* shipment supply qty, it must be
* in terms of uom in rsl.
*/
IF (l_txn_uom <> l_parent_uom) THEN
l_qty_to_be_updated := rcv_transactions_interface_sv.convert_into_correct_qty(l_primary_qty_to_be_updated,
l_primary_uom,
l_item_id,
l_parent_uom
);
asn_debug.put_line('l_qty_to_be_updated ' || l_qty_to_be_updated);
asn_debug.put_line('l_primary_qty_to_be_updated ' || l_primary_qty_to_be_updated);
asn_debug.put_line('l_secondary_qty_to_be_updated ' || l_secondary_qty_to_be_updated);
asn_debug.put_line('INVCONV, Subllot_num has been removed in update_lot_supply3 ');
UPDATE rcv_lots_supply rls
SET quantity = quantity -(l_qty_to_be_updated * l_factor),
primary_quantity = primary_quantity -(l_primary_qty_to_be_updated * l_factor),
secondary_quantity = secondary_quantity -(l_secondary_qty_to_be_updated * l_factor)
WHERE rls.lot_num = l_lot_num
AND rls.transaction_id = p_parent_supply_id
AND rls.supply_type_code = 'RECEIVING' ;
asn_debug.put_line('l_update_shipment_supply ' || l_update_shipment_supply);
asn_debug.put_line('INVCONV, Subllot_num has been removed in update_lot_supply4 ');
IF (l_update_shipment_supply = 'Y') THEN --{
SELECT COUNT(*)
INTO l_count2
FROM rcv_lots_supply
WHERE shipment_line_id = p_shipment_line_id
AND supply_type_code = 'SHIPMENT'
AND lot_num = l_lot_num;
* may or may not be a row. Hence get the count and insert
* a new shipment supply row if there is no row or update
* if there is already a shipment supply row. For ASNs the
* shipment lot numbers are just suggestions and users can
* override those values. Hence update if the lot number
* already exists. We will delete all the shipment supply
* from rcv_lot and serial tables when we fully receive
* the asn in the processor.
*/
IF ( ( p_correction_type = 'POSITIVE'
AND l_count2 >= 1)
OR ( p_correction_type = 'NEGATIVE'
AND l_count2 >= 1)) THEN --{
/* Bug 3376348.
* It might happen that the original shipment lines are split
* into multiple shipment_lines (when lpn has 2 lots for eg).
* So instead of using shipment_line_id to delete use
* shipment_header_id.
*/
SELECT MAX(primary_unit_of_measure)
INTO l_primary_uom
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id = l_item_id
AND mtl_system_items.organization_id = l_to_org_id;
/* l_qty_to_be_updated shd be in
* terms of the parent's uom. For
* shipment supply qty, it must be
* in terms of uom in rsl.
*/
OPEN shipment_supply_quantity(l_lot_num,
l_shipment_header_id,
l_item_id
);
OR l_primary_qty_to_be_updated = 0;
SELECT unit_of_measure
INTO l_parent_uom
FROM rcv_shipment_lines
WHERE shipment_line_id = l_ship_line_id;
l_qty_to_be_updated := rcv_transactions_interface_sv.convert_into_correct_qty(l_primary_qty_to_be_updated,
l_primary_uom,
l_item_id,
l_parent_uom
);
IF (l_rls_primary_qty >= l_primary_qty_to_be_updated) THEN --{
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line('rls_primary_qty is greater ');
asn_debug.put_line('l_qty_to_be_updated ' || l_qty_to_be_updated);
asn_debug.put_line('l_primary_qty_to_be_updated ' || l_primary_qty_to_be_updated);
UPDATE rcv_lots_supply rls
SET quantity = quantity -(l_qty_to_be_updated * l_factor),
primary_quantity = primary_quantity -(l_primary_qty_to_be_updated * l_factor)
WHERE rls.lot_num = l_lot_num
AND shipment_line_id = l_ship_line_id
AND rls.supply_type_code = 'SHIPMENT';
l_qty_to_be_updated := 0;
l_primary_qty_to_be_updated := 0;
asn_debug.put_line('l_qty_to_be_updated ' || l_qty_to_be_updated);
asn_debug.put_line('l_primary_qty_to_be_updated ' || l_primary_qty_to_be_updated);
UPDATE rcv_lots_supply rls
SET quantity = quantity -(quantity * l_factor),
primary_quantity = primary_quantity -(primary_quantity * l_factor)
WHERE rls.lot_num = l_lot_num
AND shipment_line_id = l_ship_line_id
AND rls.supply_type_code = 'SHIPMENT';
l_qty_to_be_updated := l_qty_to_be_updated - l_rls_qty;
l_primary_qty_to_be_updated := l_primary_qty_to_be_updated - l_rls_primary_qty;
select shipment_header_id,item_id
into l_shipment_header_id,l_item_id
from rcv_shipment_lines
where shipment_line_id = p_shipment_line_id;
update rcv_lots_supply rls
set rls.quantity =
(select rls.quantity +
(sum(mtlt.transaction_quantity) * l_factor)
from mtl_transaction_lots_temp mtlt
where mtlt.product_transaction_ID =
p_interface_transaction_id
and mtlt.product_code = 'RCV'
and rls.lot_num = mtlt.lot_number),
rls.primary_quantity =
(select rls.primary_quantity -
(sum(mtlt.primary_quantity) * l_factor)
from mtl_transaction_lots_temp mtlt
where mtlt.product_transaction_ID =
p_interface_transaction_id
and mtlt.product_code = 'RCV'
and rls.lot_num = mtlt.lot_number)
where supply_type_code = 'SHIPMENT'
AND exists (select 1 from rcv_shipment_lines rsl
where rsl.shipment_header_id = l_shipment_header_id
and rsl.shipment_line_id = rls.shipment_line_id
and rsl.item_id = l_item_id)
and rls.lot_num = l_lot_num;
asn_debug.put_line('Exit update_rcv_lots_supply ');
asn_debug.put_line('no_data_found update_rcv_lots_supply ');
INSERT INTO po_interface_errors
(interface_type,
interface_transaction_id,
error_message,
processing_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT 'RECEIVING',
p_interface_transaction_id,
'RCV_UPDATE_LOT_SUPPLY_FAIL',
SYSDATE,
rti.creation_date,
rti.created_by,
rti.last_update_date,
rti.last_updated_by,
rti.last_update_login,
rti.request_id,
rti.program_application_id,
rti.program_id,
rti.program_update_date
FROM rcv_transactions_interface rti
WHERE rti.interface_transaction_id = p_interface_transaction_id;
asn_debug.put_line('others update_rcv_lots_supply ');
INSERT INTO po_interface_errors
(interface_type,
interface_transaction_id,
error_message,
processing_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT 'RECEIVING',
p_interface_transaction_id,
'RCV_UPDATE_LOT_SUPPLY_ERROR',
SYSDATE,
rti.creation_date,
rti.created_by,
rti.last_update_date,
rti.last_updated_by,
rti.last_update_login,
rti.request_id,
rti.program_application_id,
rti.program_id,
rti.program_update_date
FROM rcv_transactions_interface rti
WHERE rti.interface_transaction_id = p_interface_transaction_id;
END update_rcv_lots_supply;
PROCEDURE update_rcv_serials_supply(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
p_interface_transaction_id IN NUMBER,
p_transaction_type IN VARCHAR2,
p_shipment_line_id IN NUMBER,
p_source_type_code IN VARCHAR2,
p_parent_supply_id IN NUMBER,
p_correction_type IN VARCHAR2
) IS
CURSOR select_serials(
p_interface_transaction_id NUMBER
) IS
SELECT msnt.fm_serial_number,
msnt.to_serial_number,
mtlt.lot_number
FROM mtl_serial_numbers_temp msnt,
mtl_transaction_lots_temp mtlt
WHERE msnt.product_transaction_id = p_interface_transaction_id
AND msnt.product_code = 'RCV'
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id(+);
l_select_serials select_serials%ROWTYPE;
l_insert_serial VARCHAR2(1) := 'N';
l_delete_serial VARCHAR2(1) := 'N';
l_update_shipment_supply VARCHAR2(1) := 'N';
asn_debug.put_line('Enter update_rcv_serials_supply ');
* In this case we do not error nor insert. So return.
*/
SELECT COUNT(*)
INTO l_serial_count
FROM mtl_serial_numbers_temp msnt
WHERE msnt.product_transaction_id = p_interface_transaction_id
AND msnt.product_code = 'RCV';
/* We need to insert into rcv_lots_supply and
* rcv_serials_supply table only when we come through ROI
* or when we come through desktop and have lpn info.
* We insert lpn_id in rcv_supply. So return if there is
* a value and validation_flag is N.
*/
SELECT NVL(validation_flag, 'N')
INTO l_validation_flag
FROM rcv_transactions_interface
WHERE interface_transaction_id = p_interface_transaction_id;
SELECT NVL(lpn_id, -999)
INTO l_lpn_id
FROM rcv_supply
WHERE supply_source_id = p_parent_supply_id;
OPEN select_serials(p_interface_transaction_id);
/* Correction_type is positive when we need to insert new rows and
* and will be negative when we need to delete the existing rows.
* We need to insert new rows only when we already have rows
* in rcv_serials_supply for the corresponding row in rcv_supply.
*/
IF (p_correction_type = 'POSITIVE') THEN --{
SELECT COUNT(*)
INTO l_count
FROM rcv_serials_supply
WHERE transaction_id = p_parent_supply_id
AND supply_type_code = 'RECEIVING';
l_insert_serial := 'Y';
l_delete_serial := 'Y';
asn_debug.put_line('l_insert_serial ' || l_insert_serial);
asn_debug.put_line('l_delete_serial ' || l_delete_serial);
SELECT transaction_type
INTO l_parent_trans_type
FROM rcv_transactions
WHERE transaction_id = p_parent_supply_id;
l_update_shipment_supply := 'Y';
asn_debug.put_line('l_update_shipment_supply ' || l_update_shipment_supply);
FETCH select_serials INTO l_select_serials;
EXIT WHEN select_serials%NOTFOUND;
split_serial_number(l_select_serials.fm_serial_number,
l_serial_prefix,
l_from_serial_number
);
split_serial_number(l_select_serials.to_serial_number,
l_serial_prefix,
l_to_serial_number
);
l_serial_num_length := LENGTH(l_select_serials.fm_serial_number);
IF (l_delete_serial = 'Y') THEN --{
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line('l_serial_prefix ' || l_serial_prefix);
DELETE FROM rcv_serials_supply
WHERE transaction_id = p_parent_supply_id
AND supply_type_code = 'RECEIVING'
AND ( l_select_serials.lot_number IS NULL
OR NVL(lot_num, l_select_serials.lot_number) = l_select_serials.lot_number)
AND (serial_num BETWEEN(l_serial_prefix || LPAD(TO_CHAR(l_from_serial_number),
l_serial_suffix_length,
'0'
)) AND(l_serial_prefix || LPAD(TO_CHAR(l_to_serial_number),
l_serial_suffix_length,
'0'
)));
IF (l_insert_serial = 'Y') THEN --{
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line('Before insert_serial_supply ');
insert_serial_supply(p_interface_transaction_id,
l_select_serials.lot_number,
l_cur_serial_number,
'RECEIVING',
p_parent_supply_id,
x_return_status
);
asn_debug.put_line('After insert_serial_supply ');
IF ( (l_update_shipment_supply = 'Y')
AND (l_delete_serial = 'Y')) THEN --{
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line('Before insert_serial_supply when update and delete serial is Y');
insert_serial_supply(p_interface_transaction_id,
l_select_serials.lot_number,
l_cur_serial_number,
'SHIPMENT',
p_parent_supply_id,
x_return_status
);
asn_debug.put_line('After insert_serial_supply when update and delete serial is Y');
IF ( (l_update_shipment_supply = 'Y')
AND (l_insert_serial = 'Y')) THEN --{
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line('Before delete when update and insert serial is Y');
SELECT shipment_header_id,
item_id
INTO l_shipment_header_id,
l_item_id
FROM rcv_shipment_lines
WHERE shipment_line_id = p_shipment_line_id;
* So instead of using shipment_line_id to delete use
* shipment_header_id.
*/
DELETE FROM rcv_serials_supply rss
WHERE supply_type_code = 'SHIPMENT'
AND ( l_select_serials.lot_number IS NULL
OR NVL(lot_num, l_select_serials.lot_number) = l_select_serials.lot_number)
AND (serial_num BETWEEN(l_serial_prefix || LPAD(TO_CHAR(l_from_serial_number),
l_serial_suffix_length,
'0'
)) AND(l_serial_prefix || LPAD(TO_CHAR(l_to_serial_number),
l_serial_suffix_length,
'0'
)))
AND EXISTS(SELECT 1
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_header_id = l_shipment_header_id
AND rsl.shipment_line_id = rss.shipment_line_id
AND rsl.item_id = l_item_id);
asn_debug.put_line('After delete when update and insert serial is Y');
CLOSE select_serials;
asn_debug.put_line('no_data_found update_rcv_serials_supply');
INSERT INTO po_interface_errors
(interface_type,
interface_transaction_id,
error_message,
processing_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT 'RECEIVING',
p_interface_transaction_id,
'RCV_UPDATE_SERIAL_SUPPLY_FAIL',
SYSDATE,
rti.creation_date,
rti.created_by,
rti.last_update_date,
rti.last_updated_by,
rti.last_update_login,
rti.request_id,
rti.program_application_id,
rti.program_id,
rti.program_update_date
FROM rcv_transactions_interface rti
WHERE rti.interface_transaction_id = p_interface_transaction_id;
asn_debug.put_line('others update_rcv_serials_supply');
INSERT INTO po_interface_errors
(interface_type,
interface_transaction_id,
error_message,
processing_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT 'RECEIVING',
p_interface_transaction_id,
'RCV_UPDATE_SERIAL_SUPPLY_ERROR',
SYSDATE,
rti.creation_date,
rti.created_by,
rti.last_update_date,
rti.last_updated_by,
rti.last_update_login,
rti.request_id,
rti.program_application_id,
rti.program_id,
rti.program_update_date
FROM rcv_transactions_interface rti
WHERE rti.interface_transaction_id = p_interface_transaction_id;
END update_rcv_serials_supply;
PROCEDURE insert_lot_supply(
p_interface_transaction_id IN NUMBER,
p_supply_type_code IN VARCHAR2,
p_supply_source_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) IS
CURSOR c IS
SELECT rls.ROWID
FROM rcv_lots_supply rls
WHERE rls.transaction_id = p_supply_source_id;
asn_debug.put_line('Enter insert_lots_supply');
* In this case we do not error nor insert. So return.
*/
SELECT COUNT(*)
INTO l_lot_count
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.product_transaction_id = p_interface_transaction_id
AND mtlt.product_code = 'RCV';
/* We need to insert into rcv_lots_supply and
* rcv_serials_supply table only when we come through ROI
* or when we come through desktop and have lpn info.
* We insert lpn_id in rcv_supply. So return if there is
* a value and validation_flag is N.
*/
SELECT NVL(validation_flag, 'N')
INTO l_validation_flag
FROM rcv_transactions_interface
WHERE interface_transaction_id = p_interface_transaction_id;
SELECT NVL(lpn_id, -999)
INTO l_lpn_id
FROM rcv_supply
WHERE supply_source_id = p_supply_source_id;
INSERT INTO rcv_lots_supply
(supply_type_code,
shipment_line_id,
transaction_id,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
request_id,
program_application_id,
program_id,
program_update_date,
lot_num,
quantity,
primary_quantity,
expiration_date,
/** OPM change Bug# 3061052**/
secondary_quantity,
/*INVCONV*/
/* sublot_num, */
/*end , INVCONV*/
reason_code
)
SELECT p_supply_type_code,
rs.shipment_line_id,
DECODE(p_supply_type_code,
'RECEIVING', rs.supply_source_id,
NULL
),
rs.last_updated_by,
rs.last_update_date,
rs.last_update_login,
rs.created_by,
rs.creation_date,
rs.request_id,
rs.program_application_id,
rs.program_id,
SYSDATE,
mtltview.lot_number,
mtltview.qty,
mtltview.primary_qty,
mtltview.lot_expiration_date,
mtltview.secondary_qty,
/*INVCONV*/
/* mtltview.sublot_num, */
/*end , INVCONV*/
mtltview.reason_code
FROM rcv_supply rs,
(SELECT SUM(mtlt.transaction_quantity) qty,
SUM(mtlt.primary_quantity) primary_qty,
SUM(mtlt.secondary_quantity) secondary_qty,
mtlt.lot_number,
mtlt.lot_expiration_date,
mtlt.product_transaction_id,
mtlt.product_code,
/*INVCONV*/
/* mtlt.sublot_num, */
/*end , INVCONV*/
mtlt.reason_code
FROM mtl_transaction_lots_temp mtlt
GROUP BY mtlt.product_transaction_id,
mtlt.lot_number,
/*INVCONV*/
/* mtlt.sublot_num, */
/*end , INVCONV*/
mtlt.lot_expiration_date,
mtlt.product_code,
mtlt.reason_code) mtltview
WHERE ( mtltview.product_transaction_id = p_interface_transaction_id
AND mtltview.product_code = 'RCV'
AND rs.supply_source_id = p_supply_source_id);
asn_debug.put_line('After insert into rcv_lots_supply ');
asn_debug.put_line('INVCONV, Subllot_num has been removed in insert_lot_supply1 ');
asn_debug.put_line('Exit insert_lots_supply ');
asn_debug.put_line('no_data_found insert_lots_supply ');
asn_debug.put_line('others insert_lots_supply ');
END insert_lot_supply;
PROCEDURE insert_serial_supply(
p_interface_transaction_id IN NUMBER,
p_lot_number IN VARCHAR2,
p_serial_number IN VARCHAR2,
p_supply_type_code IN VARCHAR2,
p_supply_source_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) IS
CURSOR c IS
SELECT rss.ROWID
FROM rcv_serials_supply rss
WHERE rss.transaction_id = p_supply_source_id;
asn_debug.put_line('Enter insert_serial_supply ');
* In this case we do not error nor insert. So return.
*/
SELECT COUNT(*)
INTO l_serial_count
FROM mtl_serial_numbers_temp msnt
WHERE msnt.product_transaction_id = p_interface_transaction_id
AND msnt.product_code = 'RCV';
/* We need to insert into rcv_lots_supply and
* rcv_serials_supply table only when we come through ROI
* or when we come through desktop and have lpn info.
* We insert lpn_id in rcv_supply. So return if there is
* a value and validation_flag is N.
*/
SELECT NVL(validation_flag, 'N')
INTO l_validation_flag
FROM rcv_transactions_interface
WHERE interface_transaction_id = p_interface_transaction_id;
SELECT NVL(lpn_id, -999)
INTO l_lpn_id
FROM rcv_supply
WHERE supply_source_id = p_supply_source_id;
INSERT INTO rcv_serials_supply
(supply_type_code,
shipment_line_id,
transaction_id,
lot_num,
serial_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT p_supply_type_code,
rs.shipment_line_id,
DECODE(p_supply_type_code,
'RECEIVING', rs.supply_source_id,
NULL
),
p_lot_number,
p_serial_number,
rs.last_update_date,
rs.last_updated_by,
rs.creation_date,
rs.created_by,
rs.last_update_login,
rs.request_id,
rs.program_application_id,
rs.program_id,
rs.program_update_date
FROM rcv_supply rs
WHERE rs.supply_source_id = p_supply_source_id;
asn_debug.put_line(' After insert into rcv_serials_supply');
asn_debug.put_line(' Exit insert_serial_supply');
asn_debug.put_line(' no_data_found insert_serial_supply');
asn_debug.put_line(' others insert_serial_supply');
END insert_serial_supply;
PROCEDURE insert_lot_transactions(
p_interface_transaction_id IN NUMBER,
p_lot_context IN VARCHAR2,
p_lot_context_id IN NUMBER,
p_source_transaction_id IN NUMBER,
p_correction_transaction_id IN NUMBER,
p_negate_qty IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_lot_count NUMBER;
asn_debug.put_line(' enter insert_lot_transactions');
* In this case we do not error nor insert. So return.
*/
SELECT COUNT(*)
INTO l_lot_count
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.product_transaction_id = p_interface_transaction_id
AND mtlt.product_code = 'RCV';
INSERT INTO rcv_lot_transactions
(lot_transaction_type,
shipment_line_id,
transaction_id,
source_transaction_id,
correction_transaction_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
transaction_date,
item_id,
lot_num,
quantity,
primary_quantity,
expiration_date,
/* INVCONV */
/* sublot_num, */
/* end , INVCONV*/
secondary_quantity,
reason_code
)
SELECT DECODE(p_lot_context,
'CORRECTION', 'TRANSACTION',
p_lot_context
),
rti.shipment_line_id,
DECODE(p_lot_context,
'SHIPMENT', -1,
p_lot_context_id
),
DECODE(p_lot_context,
'SHIPMENT', -1,
p_source_transaction_id
),
p_correction_transaction_id,
rti.created_by,
rti.creation_date,
rti.last_updated_by,
rti.last_update_date,
rti.last_update_login,
rti.request_id,
rti.program_application_id,
rti.program_id,
SYSDATE,
rti.transaction_date,
rti.item_id,
mtltview.lot_number,
DECODE(p_negate_qty,
'Y',(mtltview.qty * -1),
mtltview.qty
),
DECODE(p_negate_qty,
'Y',(mtltview.primary_qty * -1),
mtltview.primary_qty
),
mtltview.lot_expiration_date,
/*INVCONV*/
/* mtltview.sublot_num, */
/*end ,INVCONV*/
mtltview.secondary_qty,
mtltview.reason_code
FROM rcv_transactions_interface rti,
(SELECT SUM(mtlt.transaction_quantity) qty,
SUM(mtlt.primary_quantity) primary_qty,
SUM(mtlt.secondary_quantity) secondary_qty,
mtlt.lot_number,
mtlt.lot_expiration_date,
mtlt.product_transaction_id,
/*INVCONV*/
/* mtlt.sublot_num, */
/*end , INVCONV*/
mtlt.reason_code,
mtlt.product_code
FROM mtl_transaction_lots_temp mtlt
GROUP BY mtlt.product_transaction_id,
mtlt.lot_number,
mtlt.lot_expiration_date,
/* INVCONV*/
/* mtlt.sublot_num, */
/*end , INVCONV*/
mtlt.reason_code,
mtlt.product_code) mtltview
WHERE mtltview.product_transaction_id = p_interface_transaction_id
AND mtltview.product_code = 'RCV'
AND rti.interface_transaction_id = mtltview.product_transaction_id;
asn_debug.put_line('Exit insert_lot_transactions ');
asn_debug.put_line('INVCONV , sublot_num has not been inserted in rcv_lot_transactions');
asn_debug.put_line('no_data_found insert_lot_transactions ');
asn_debug.put_line('others insert_lot_transactions ');
END insert_lot_transactions;
SELECT NVL(header_interface_id, -999),
auto_transact_code,
shipment_header_id
INTO l_header_id,
l_asn_type,
l_ship_id
FROM rcv_transactions_interface
WHERE lpn_group_id = l_lpn_group_id
AND GROUP_ID = DECODE(p_group_id,
0, GROUP_ID,
p_group_id
)
AND ROWNUM < 2;
we must delete the shipment_header if it exists
update the rhi and rti to error for the shipment_headerid */
IF l_header_id <> -999
AND l_asn_type = 'SHIP' THEN --{
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line(' This is an ASN');
UPDATE rcv_headers_interface
SET processing_status_code = 'ERROR'
WHERE header_interface_id = l_header_id;
rcv_roi_preprocessor.update_rti_error(p_group_id => p_group_id,
p_interface_id => NULL,
p_header_interface_id => l_header_id,
p_lpn_group_id => NULL
);
/* for non-ASN transactions we should update the corresponding lpn group
to error */
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line(' Before updating rti error');
rcv_roi_preprocessor.update_rti_error(p_group_id => p_group_id,
p_interface_id => NULL,
p_header_interface_id => NULL,
p_lpn_group_id => l_lpn_group_id
);