[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE errupdate(rowid IN VARCHAR2);
* LotTrxInsert(p_transaction_interface_id IN NUMBER)
* Added this function to process lot split, merge and translate.
* As part of J-dev, we will bypass this API.
* This API has been onbsoleted.
* we will use tmpinsert() to move records.
*******************************************************************/
/** end of lot transactions changes **/
/******************************************************************
* Check_Partial_Split - private procedure to check if the lot split
* transaction is a partial split, i.e., there are remaining qty
* in the parent lots. In this case, we need to insert additional
* record in mmtt for the remaining qty
* This procedure assumes that the primary qty is already calculated
* and the qty comparison is done with the primary qty.
* This procedure is called after calling LotTrxInsert
* As part of J-dev, we will use tmpInsert to
* move lot transaction records from MTI to MMTT. (also for I)
* Some changes have been made in this API for I + J, to enable bulk
* insert. do not re-insert the parent transaction.
* do not insert into MMTT here, but into MTI only, if we are
* creating a new record for this transaction
*
* CHANGES FOR OSFM SUPPORT FOR SERIALIZED LOT ITEMS:
* Store all the resulting serials into l_rs_serial_tbl.
* Loop through the source Serials. If the serial is not present in
* l_rs_serial_tbl then add the serial to p_rem_serial_tbl.
* End Loop
* Loop through the p_rem_serial_tbl
* insert MSNI for that serial
* End Loop
* Update the MTLI with serial_txn_temp_id for the serials inserted
* into MSNI.
*******************************************************************/
FUNCTION check_partial_split (p_parent_id IN NUMBER, p_current_index IN NUMBER)
RETURN BOOLEAN
IS
CURSOR mti_csr (p_interface_id NUMBER)
IS
SELECT mti.transaction_header_id
, mti.acct_period_id
, mti.distribution_account_id
, mti.transaction_interface_id
, mti.transaction_type_id
, mti.source_code
, mti.source_line_id
, mti.source_header_id
, mti.inventory_item_id
, mti.revision
, mti.organization_id
, mti.subinventory_code
, mti.locator_id
, mti.transaction_quantity
, mti.primary_quantity
, mti.transaction_uom
, mti.lpn_id
, mti.transfer_lpn_id
, mti.cost_group_id
, mti.transaction_source_type_id
, mti.transaction_action_id
, mti.parent_id
, mti.created_by
, mtli.lot_number
, mtli.lot_expiration_date
, mtli.description
, mtli.vendor_id
, mtli.supplier_lot_number
, mtli.territory_code
, mtli.grade_code
, mtli.origination_date
, mtli.date_code
, mtli.status_id
, mtli.change_date
, mtli.age
, mtli.retest_date
, mtli.maturity_date
, mtli.lot_attribute_category
, mtli.item_size
, mtli.color
, mtli.volume
, mtli.volume_uom
, mtli.place_of_origin
, mtli.best_by_date
, mtli.LENGTH
, mtli.length_uom
, mtli.recycled_content
, mtli.thickness
, mtli.thickness_uom
, mtli.width
, mtli.width_uom
, mtli.curl_wrinkle_fold
, mtli.c_attribute1
, mtli.c_attribute2
, mtli.c_attribute3
, mtli.c_attribute4
, mtli.c_attribute5
, mtli.c_attribute6
, mtli.c_attribute7
, mtli.c_attribute8
, mtli.c_attribute9
, mtli.c_attribute10
, mtli.c_attribute11
, mtli.c_attribute12
, mtli.c_attribute13
, mtli.c_attribute14
, mtli.c_attribute15
, mtli.c_attribute16
, mtli.c_attribute17
, mtli.c_attribute18
, mtli.c_attribute19
, mtli.c_attribute20
, mtli.d_attribute1
, mtli.d_attribute2
, mtli.d_attribute3
, mtli.d_attribute4
, mtli.d_attribute5
, mtli.d_attribute6
, mtli.d_attribute7
, mtli.d_attribute8
, mtli.d_attribute9
, mtli.d_attribute10
, mtli.n_attribute1
, mtli.n_attribute2
, mtli.n_attribute3
, mtli.n_attribute4
, mtli.n_attribute5
, mtli.n_attribute6
, mtli.n_attribute7
, mtli.n_attribute8
, mtli.n_attribute9
, mtli.n_attribute10
, mtli.attribute1
, mtli.attribute2
, mtli.attribute3
, mtli.attribute4
, mtli.attribute5
, mtli.attribute6
, mtli.attribute7
, mtli.attribute8
, mtli.attribute9
, mtli.attribute10
, mtli.attribute11
, mtli.attribute12
, mtli.attribute13
, mtli.attribute14
, mtli.attribute15
, mtli.attribute_category
, mtli.parent_object_type --R12 Genealogy enhancements
, mtli.parent_object_id --R12 Genealogy enhancements
, mtli.parent_object_number --R12 Genealogy enhancements
, mtli.parent_item_id --R12 Genealogy enhancements
, mtli.parent_object_type2 --R12 Genealogy enhancements
, mtli.parent_object_id2 --R12 Genealogy enhancements
, mtli.parent_object_number2 --R12 Genealogy enhancements
, msi.description item_description
, msi.location_control_code
, msi.restrict_subinventories_code
, msi.restrict_locators_code
, msi.revision_qty_control_code
, msi.primary_uom_code
, msi.shelf_life_code
, msi.shelf_life_days
, msi.allowed_units_lookup_code
, mti.transaction_batch_id
, mti.transaction_batch_seq
, mti.kanban_card_id
, mti.transaction_mode --J-dev
FROM mtl_transactions_interface mti
, mtl_transaction_lots_interface mtli
, mtl_system_items_b msi
WHERE mti.transaction_interface_id = p_interface_id
AND mti.transaction_interface_id = mtli.transaction_interface_id
AND mti.organization_id = msi.organization_id
AND mti.inventory_item_id = msi.inventory_item_id
AND mti.process_flag = 1;
SELECT transaction_interface_id
, last_update_login
, created_by
, last_updated_by
, request_id
, program_application_id
, program_id
, program_update_date
, vendor_serial_number
, vendor_lot_number
, fm_serial_number
, to_serial_number
, parent_serial_number
, serial_attribute_category
, 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_attribute9
, n_attribute10
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, status_id
, territory_code
, time_since_new
, cycles_since_new
, time_since_overhaul
, cycles_since_overhaul
, time_since_repair
, cycles_since_repair
, time_since_visit
, cycles_since_visit
, time_since_mark
, cycles_since_mark
, number_of_repairs
, parent_object_type --R12 Genealogy enhancements
, parent_object_id --R12 Genealogy enhancements
, parent_object_number --R12 Genealogy enhancements
, parent_item_id --R12 Genealogy enhancements
, parent_object_type2 --R12 Genealogy enhancements
, parent_object_id2 --R12 Genealogy enhancements
, parent_object_number2 --R12 Genealogy enhancements
FROM mtl_serial_numbers_interface msni
WHERE msni.transaction_interface_id =
(SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_interface mtli
WHERE mtli.transaction_interface_id = p_parent_id)
AND inv_serial_number_pub.get_serial_diff (msni.fm_serial_number
, p_serial_number
) <> -1
AND inv_serial_number_pub.get_serial_diff (msni.fm_serial_number
, NVL (msni.to_serial_number
, msni.fm_serial_number
)
) >=
inv_serial_number_pub.get_serial_diff (msni.fm_serial_number
, p_serial_number
);
SELECT fm_serial_number
, NVL (to_serial_number, fm_serial_number) to_serial_number
FROM mtl_serial_numbers_interface msni
WHERE msni.transaction_interface_id IN (
SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_interface mtli
WHERE mtli.transaction_interface_id IN (
SELECT transaction_interface_id
FROM mtl_transactions_interface mti
WHERE mti.parent_id = p_parent_id
AND mti.transaction_interface_id <> mti.parent_id));
SELECT fm_serial_number
, NVL (to_serial_number, fm_serial_number) to_serial_number
FROM mtl_serial_numbers_interface msni
WHERE msni.transaction_interface_id =
(SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_interface mtli
WHERE mtli.transaction_interface_id =
(SELECT transaction_interface_id
FROM mtl_transactions_interface mti
WHERE mti.parent_id = p_parent_id
AND mti.transaction_interface_id = mti.parent_id));
SELECT COUNT (parent_id)
INTO l_count
FROM mtl_transactions_interface
WHERE parent_id = p_parent_id;
SELECT ABS (primary_quantity)
INTO l_split_qty
FROM mtl_transactions_interface
WHERE transaction_interface_id = p_parent_id;
SELECT SUM (ABS (primary_quantity))
INTO l_partial_total_qty
FROM mtl_transactions_interface
WHERE parent_id = p_parent_id AND transaction_interface_id <> p_parent_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_transaction_interface_id --J-dev
FROM DUAL;
INSERT INTO mtl_transactions_interface
(transaction_header_id
, transaction_interface_id
, transaction_mode
, lock_flag
, source_code
, source_line_id
, source_header_id
, process_flag
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, inventory_item_id
, revision
, organization_id
, subinventory_code
, locator_id
, transaction_quantity
, primary_quantity
, transaction_uom
, transaction_type_id
, transaction_action_id
, transaction_source_type_id
, transaction_date
, acct_period_id
, distribution_account_id
,
/*item_description ,
item_location_control_code ,
item_restrict_subinv_code
,item_restrict_locators_code ,
item_revision_qty_control_code ,
item_primary_uom_code
,item_shelf_life_code ,
item_shelf_life_days ,
item_lot_control_code
,item_serial_control_code ,
allowed_units_lookup_code,*/--J-dev not in MTI
parent_id
, --J-dev
lpn_id
, transfer_lpn_id
, cost_group_id
, transaction_batch_id
, transaction_batch_seq
, kanban_card_id
)
VALUES (l_mti_csr.transaction_header_id
, l_transaction_interface_id
, --J-dev
l_mti_csr.transaction_mode /*2722754 */
, 2
, l_mti_csr.source_code
, l_mti_csr.source_line_id
, l_mti_csr.source_header_id
, --J-dev
1
, --J-dev
l_sysdate
, l_mti_csr.created_by
, l_sysdate
, l_mti_csr.created_by
, l_mti_csr.created_by
, NULL
, NULL
, NULL
, NULL
, l_mti_csr.inventory_item_id
, l_mti_csr.revision
, l_mti_csr.organization_id
, l_mti_csr.subinventory_code
, l_mti_csr.locator_id
, l_remaining_qty
, l_remaining_qty
, l_mti_csr.primary_uom_code
, l_mti_csr.transaction_type_id
, l_mti_csr.transaction_action_id
, l_mti_csr.transaction_source_type_id
, l_sysdate
, l_mti_csr.acct_period_id
, l_mti_csr.distribution_account_id
,
/*l_mti_csr.item_description,
l_mti_csr.location_control_code,
l_mti_csr.restrict_subinventories_code,
l_mti_csr.restrict_locators_code,
l_mti_csr.revision_qty_control_code,
l_mti_csr.primary_uom_code,
l_mti_csr.shelf_life_code,
l_mti_csr.shelf_life_days,
2,
1,
l_mti_csr.allowed_units_lookup_code,*/--J-dev Not in MTI
l_mti_csr.parent_id
, null--l_mti_csr.lpn_id
, l_mti_csr.lpn_id
, l_mti_csr.cost_group_id
, l_mti_csr.transaction_batch_id
, l_mti_csr.transaction_batch_seq
, l_mti_csr.kanban_card_id
);
INSERT INTO mtl_transaction_lots_interface
(transaction_interface_id --J-dev
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, transaction_quantity
, primary_quantity
, lot_number
, lot_expiration_date
, description
, vendor_id
, supplier_lot_number
, territory_code
, 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_attribute9
, n_attribute10
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, attribute_category
, parent_object_type --R12 Genealogy enhancements
, parent_object_id --R12 Genealogy enhancements
, parent_object_number --R12 Genealogy enhancements
, parent_item_id --R12 Genealogy enhancements
, parent_object_type2 --R12 Genealogy enhancements
, parent_object_id2 --R12 Genealogy enhancements
, parent_object_number2 --R12 Genealogy enhancements
)
VALUES (l_transaction_interface_id
, l_sysdate
, l_mti_csr.created_by
, l_sysdate
, l_mti_csr.created_by
, l_mti_csr.created_by
, NULL
, NULL
, NULL
, NULL
, l_remaining_qty
, l_remaining_qty
, l_mti_csr.lot_number
, l_mti_csr.lot_expiration_date
, l_mti_csr.description
, l_mti_csr.vendor_id
, l_mti_csr.supplier_lot_number
, l_mti_csr.territory_code
, l_mti_csr.grade_code
, l_mti_csr.origination_date
, l_mti_csr.date_code
, l_mti_csr.status_id
, l_mti_csr.change_date
, l_mti_csr.age
, l_mti_csr.retest_date
, l_mti_csr.maturity_date
, l_mti_csr.lot_attribute_category
, l_mti_csr.item_size
, l_mti_csr.color
, l_mti_csr.volume
, l_mti_csr.volume_uom
, l_mti_csr.place_of_origin
, l_mti_csr.best_by_date
, l_mti_csr.LENGTH
, l_mti_csr.length_uom
, l_mti_csr.recycled_content
, l_mti_csr.thickness
, l_mti_csr.thickness_uom
, l_mti_csr.width
, l_mti_csr.width_uom
, l_mti_csr.curl_wrinkle_fold
, l_mti_csr.c_attribute1
, l_mti_csr.c_attribute2
, l_mti_csr.c_attribute3
, l_mti_csr.c_attribute4
, l_mti_csr.c_attribute5
, l_mti_csr.c_attribute6
, l_mti_csr.c_attribute7
, l_mti_csr.c_attribute8
, l_mti_csr.c_attribute9
, l_mti_csr.c_attribute10
, l_mti_csr.c_attribute11
, l_mti_csr.c_attribute12
, l_mti_csr.c_attribute13
, l_mti_csr.c_attribute14
, l_mti_csr.c_attribute15
, l_mti_csr.c_attribute16
, l_mti_csr.c_attribute17
, l_mti_csr.c_attribute18
, l_mti_csr.c_attribute19
, l_mti_csr.c_attribute20
, l_mti_csr.d_attribute1
, l_mti_csr.d_attribute2
, l_mti_csr.d_attribute3
, l_mti_csr.d_attribute4
, l_mti_csr.d_attribute5
, l_mti_csr.d_attribute6
, l_mti_csr.d_attribute7
, l_mti_csr.d_attribute8
, l_mti_csr.d_attribute9
, l_mti_csr.d_attribute10
, l_mti_csr.n_attribute1
, l_mti_csr.n_attribute2
, l_mti_csr.n_attribute3
, l_mti_csr.n_attribute4
, l_mti_csr.n_attribute5
, l_mti_csr.n_attribute6
, l_mti_csr.n_attribute7
, l_mti_csr.n_attribute8
, l_mti_csr.n_attribute9
, l_mti_csr.n_attribute10
, l_mti_csr.attribute1
, l_mti_csr.attribute2
, l_mti_csr.attribute3
, l_mti_csr.attribute4
, l_mti_csr.attribute5
, l_mti_csr.attribute6
, l_mti_csr.attribute7
, l_mti_csr.attribute8
, l_mti_csr.attribute9
, l_mti_csr.attribute10
, l_mti_csr.attribute11
, l_mti_csr.attribute12
, l_mti_csr.attribute13
, l_mti_csr.attribute14
, l_mti_csr.attribute15
, l_mti_csr.attribute_category
, l_mti_csr.parent_object_type --R12 Genealogy enhancements
, l_mti_csr.parent_object_id --R12 Genealogy enhancements
, l_mti_csr.parent_object_number --R12 Genealogy enhancements
, l_mti_csr.parent_item_id --R12 Genealogy enhancements
, l_mti_csr.parent_object_type2 --R12 Genealogy enhancements
, l_mti_csr.parent_object_id2 --R12 Genealogy enhancements
, l_mti_csr.parent_object_number2 --R12 Genealogy enhancements
);
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 mtl_material_transactions_s.NEXTVAL
INTO l_sequence
FROM DUAL;
INSERT INTO mtl_serial_numbers_interface
(transaction_interface_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, vendor_serial_number
, vendor_lot_number
, fm_serial_number
, to_serial_number
, parent_serial_number
, serial_attribute_category
, 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_attribute9
, n_attribute10
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, status_id
, territory_code
, time_since_new
, cycles_since_new
, time_since_overhaul
, cycles_since_overhaul
, time_since_repair
, cycles_since_repair
, time_since_visit
, cycles_since_visit
, time_since_mark
, cycles_since_mark
, number_of_repairs
, parent_object_type --R12 Genealogy enhancements
, parent_object_id --R12 Genealogy enhancements
, parent_object_number --R12 Genealogy enhancements
, parent_item_id --R12 Genealogy enhancements
, parent_object_type2 --R12 Genealogy enhancements
, parent_object_id2 --R12 Genealogy enhancements
, parent_object_number2 --R12 Genealogy enhancements
)
VALUES (l_sequence
, l_sysdate
, l_msni_csr.last_updated_by
, l_sysdate
, l_msni_csr.created_by
, l_msni_csr.last_update_login
, l_msni_csr.request_id
, l_msni_csr.program_application_id
, l_msni_csr.program_id
, l_msni_csr.program_update_date
, l_msni_csr.vendor_serial_number
, l_msni_csr.vendor_lot_number
--Serial remaining
, l_rem_serial_tbl (l_rem_var_index)
, l_rem_serial_tbl (l_rem_var_index)
, l_msni_csr.parent_serial_number
, l_msni_csr.serial_attribute_category
, l_msni_csr.c_attribute1
, l_msni_csr.c_attribute2
, l_msni_csr.c_attribute3
, l_msni_csr.c_attribute4
, l_msni_csr.c_attribute5
, l_msni_csr.c_attribute6
, l_msni_csr.c_attribute7
, l_msni_csr.c_attribute8
, l_msni_csr.c_attribute9
, l_msni_csr.c_attribute10
, l_msni_csr.c_attribute11
, l_msni_csr.c_attribute12
, l_msni_csr.c_attribute13
, l_msni_csr.c_attribute14
, l_msni_csr.c_attribute15
, l_msni_csr.c_attribute16
, l_msni_csr.c_attribute17
, l_msni_csr.c_attribute18
, l_msni_csr.c_attribute19
, l_msni_csr.c_attribute20
, l_msni_csr.d_attribute1
, l_msni_csr.d_attribute2
, l_msni_csr.d_attribute3
, l_msni_csr.d_attribute4
, l_msni_csr.d_attribute5
, l_msni_csr.d_attribute6
, l_msni_csr.d_attribute7
, l_msni_csr.d_attribute8
, l_msni_csr.d_attribute9
, l_msni_csr.d_attribute10
, l_msni_csr.n_attribute1
, l_msni_csr.n_attribute2
, l_msni_csr.n_attribute3
, l_msni_csr.n_attribute4
, l_msni_csr.n_attribute5
, l_msni_csr.n_attribute6
, l_msni_csr.n_attribute7
, l_msni_csr.n_attribute8
, l_msni_csr.n_attribute9
, l_msni_csr.n_attribute10
, l_msni_csr.attribute_category
, l_msni_csr.attribute1
, l_msni_csr.attribute2
, l_msni_csr.attribute3
, l_msni_csr.attribute4
, l_msni_csr.attribute5
, l_msni_csr.attribute6
, l_msni_csr.attribute7
, l_msni_csr.attribute8
, l_msni_csr.attribute9
, l_msni_csr.attribute10
, l_msni_csr.attribute11
, l_msni_csr.attribute12
, l_msni_csr.attribute13
, l_msni_csr.attribute14
, l_msni_csr.attribute15
, l_msni_csr.status_id
, l_msni_csr.territory_code
, l_msni_csr.time_since_new
, l_msni_csr.cycles_since_new
, l_msni_csr.time_since_overhaul
, l_msni_csr.cycles_since_overhaul
, l_msni_csr.time_since_repair
, l_msni_csr.cycles_since_repair
, l_msni_csr.time_since_visit
, l_msni_csr.cycles_since_visit
, l_msni_csr.time_since_mark
, l_msni_csr.cycles_since_mark
, l_msni_csr.number_of_repairs
, l_msni_csr.parent_object_type --R12 Genealogy enhancements
, l_msni_csr.parent_object_id --R12 Genealogy enhancements
, l_msni_csr.parent_object_number --R12 Genealogy enhancements
, l_msni_csr.parent_item_id --R12 Genealogy enhancements
, l_msni_csr.parent_object_type2 --R12 Genealogy enhancements
, l_msni_csr.parent_object_id2 --R12 Genealogy enhancements
, l_msni_csr.parent_object_number2 --R12 Genealogy enhancements
);
/*Need to update the MTLI with serial_txn_temP_id to connect with the MSNIs*/
UPDATE mtl_transaction_lots_interface
SET serial_transaction_temp_id = l_sequence
WHERE transaction_interface_id = l_transaction_interface_id;
('Error while inserting in MSNI check_partial_split '
, 'INV_TXN_MANAGER_PUB'
, '9'
);
* errupdate()
*
******************************************************************/
PROCEDURE errupdate (p_rowid IN VARCHAR2)
IS
l_userid NUMBER := -1; -- = prg_info.userid;
UPDATE mtl_transactions_interface
SET ERROR_CODE = SUBSTRB (l_error_code, 1, 240)
, error_explanation = SUBSTRB (l_error_exp, 1, 240)
, last_update_date = SYSDATE
, last_updated_by = l_userid
, last_update_login = l_loginid
, program_update_date = SYSDATE
, process_flag = 3
, lock_flag = 2
WHERE ROWID = p_rowid;
END errupdate;
SELECT transaction_interface_id
, transaction_header_id
, request_id
, inventory_item_id
, organization_id
, subinventory_code
, transfer_organization
, transfer_subinventory
, transaction_uom
, transaction_date
, transaction_quantity
, locator_id
, transfer_locator
, transaction_source_id
, transaction_source_type_id
, transaction_action_id
, transaction_type_id
, distribution_account_id
, NVL (shippable_flag, 'Y')
, ROWID
, new_average_cost
, value_change
, percentage_change
, material_account
, material_overhead_account
, resource_account
, outside_processing_account
, overhead_account
, requisition_line_id
, overcompletion_transaction_qty
, /* Overcompletion Transactions */
end_item_unit_number
, scheduled_payback_date
, /* Borrow Payback */
revision
, /* Borrow Payback */
org_cost_group_id
, /* PCST */
cost_type_id
, /* PCST */
primary_quantity
, source_line_id
, process_flag
, transaction_source_name
, trx_source_delivery_id
, trx_source_line_id
, parent_id
, transaction_batch_id
, transaction_batch_seq
,
-- INVCONV start fabdi
secondary_transaction_quantity
, secondary_uom_code
-- INVCONV end fabdi
, SHIP_TO_LOCATION_ID --eIB Build; Bug# 4348541
SELECT acct_period_id
INTO acct_period_id
FROM org_acct_periods
WHERE period_close_date IS NULL
AND organization_id = p_org_id
AND NVL (p_trans_date, SYSDATE) BETWEEN period_start_date
AND schedule_close_date
ORDER BY period_start_date DESC, schedule_close_date ASC;
SELECT acct_period_id
INTO acct_period_id
FROM org_acct_periods
WHERE period_close_date IS NULL
AND organization_id = p_org_id
AND TRUNC (schedule_close_date) >=
TRUNC (NVL (p_trans_date, SYSDATE))
AND TRUNC (period_start_date) <= TRUNC (NVL (p_trans_date, SYSDATE));
* tmpinsert() moved to INV_TXN_MANAGER_GRP
*
******************************************************************/
FUNCTION tmpinsert (p_header_id IN NUMBER)
RETURN BOOLEAN
IS
l_lt_flow_schedule NUMBER;
l_return := inv_txn_manager_grp.tmpinsert (p_header_id => p_header_id);
inv_log_util.TRACE ( 'Error in tmpinsert: sqlerrm : '
|| SUBSTR (SQLERRM, 1, 200)
, 'INV_TXN_MANAGER_PUB'
, '9'
);
END tmpinsert;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_hdr_id
FROM DUAL;
UPDATE mtl_material_transactions_temp
SET transaction_header_id = l_new_hdr_id
, lock_flag = 'Y'
WHERE process_flag = 'Y'
AND NVL (lock_flag, 'N') = 'N'
AND transaction_header_id IN (
SELECT mmtt.transaction_header_id
FROM mtl_material_transactions mmt
, mtl_material_transactions_temp mmtt
WHERE mmt.transaction_set_id = l_old_hdr_id
AND mmt.completion_transaction_id =
mmtt.completion_transaction_id);
SELECT fnd_profile.VALUE (p_prof)
INTO x_ret
FROM DUAL;
SELECT transaction_interface_id
, transaction_header_id
, request_id
, inventory_item_id
, organization_id
, subinventory_code
, transfer_organization
, transfer_subinventory
, transaction_uom
, transaction_date
, transaction_quantity
, locator_id
, transfer_locator
, transaction_source_id
, transaction_source_type_id
, transaction_action_id
, transaction_type_id
, distribution_account_id
, NVL (shippable_flag, 'Y')
, ROWID
, new_average_cost
, value_change
, percentage_change
, material_account
, material_overhead_account
, resource_account
, outside_processing_account
, overhead_account
, requisition_line_id
, overcompletion_transaction_qty
, /* Overcompletion Transactions */
end_item_unit_number
, scheduled_payback_date
, /* Borrow Payback */
revision
, /* Borrow Payback */
org_cost_group_id
, /* PCST */
cost_type_id
, /* PCST */
primary_quantity
, source_line_id
, process_flag
, transaction_source_name
, trx_source_delivery_id
, trx_source_line_id
, parent_id
, transaction_batch_id
, transaction_batch_seq
,
-- INVCONV start fabdi
secondary_transaction_quantity
, secondary_uom_code
-- INVCONV end fabdi
, ship_to_location_id --eIB Build; Bug# 4348541
SELECT
p_line_rec_type.ROWID,
p_line_rec_type.INVENTORY_ITEM_ID,
p_line_rec_type.REVISION,
p_line_rec_type.ORGANIZATION_ID,
p_line_rec_type.SUBINVENTORY_CODE,
p_line_rec_type.LOCATOR_ID,
ABS(p_line_rec_type.PRIMARY_QUANTITY) PRIMARY_QUANTITY,
NULL LOT_NUMBER,
p_line_rec_type.TRANSACTION_SOURCE_TYPE_ID,
p_line_rec_type.TRANSACTION_ACTION_ID,
p_line_rec_type.TRANSACTION_SOURCE_ID,
p_line_rec_type.TRANSACTION_SOURCE_NAME,
--Jalaj Srivastava 5010595
--for GME (wip_enity_type=10) select trx_source_line_id as source_line_id
decode(p_line_rec_type.transaction_source_type_id,5,decode(p_line_rec_type.wip_entity_type,10,p_line_rec_type.TRX_SOURCE_LINE_ID,p_line_rec_type.SOURCE_LINE_ID),p_line_rec_type.SOURCE_LINE_ID) SOURCE_LINE_ID,
MSI.REVISION_QTY_CONTROL_CODE,
decode(p_line_rec_type.transaction_source_type_id,5,1,MSI.lot_control_code) lot_control_code,--j-dev
decode(p_line_rec_type.TRANSACTION_ACTION_ID,2,p_line_rec_type.TRANSFER_SUBINVENTORY,28,p_line_rec_type.TRANSFER_SUBINVENTORY,null) TRANSFER_SUBINVENTORY,
p_line_rec_type.TRANSFER_LOCATOR,
p_line_rec_type.transaction_date,
MP.NEGATIVE_INV_RECEIPT_CODE
FROM MTL_PARAMETERS MP,
MTL_SYSTEM_ITEMS MSI
WHERE MP.ORGANIZATION_ID = p_line_rec_type.ORGANIZATION_ID
-- AND MP.NEGATIVE_INV_RECEIPT_CODE = 2 'bug 3679189'
AND p_line_rec_type.PROCESS_FLAG = 1
AND ((MSI.LOT_CONTROL_CODE = 1) OR (p_line_rec_type.transaction_source_type_id=5 and p_line_rec_type.wip_entity_type <> 10))--J-dev--verify this
AND ( ( (p_line_rec_type.wip_entity_type <> 10)
AND ( (p_flow_sch <> 1
AND p_line_rec_type.TRANSACTION_ACTION_ID IN (1,2,3,21,32,34,5) )
OR (p_flow_sch = 1
AND p_line_rec_type.TRANSACTION_ACTION_ID IN (1, 32) )
)
)
--Jalaj Srivastava 5232394
--select all transactions for GME
OR (p_line_rec_type.wip_entity_type = 10)
)
AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSI.ORGANIZATION_ID = p_line_rec_type.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = p_line_rec_type.INVENTORY_ITEM_ID
UNION
SELECT
p_line_rec_type.ROWID,
p_line_rec_type.INVENTORY_ITEM_ID,
p_line_rec_type.REVISION,
p_line_rec_type.ORGANIZATION_ID,
p_line_rec_type.SUBINVENTORY_CODE,
p_line_rec_type.LOCATOR_ID,
ABS(MTLI.PRIMARY_QUANTITY) PRIMARY_QUANTITY,
MTLI.lot_number LOT_NUMBER,
p_line_rec_type.TRANSACTION_SOURCE_TYPE_ID,
p_line_rec_type.TRANSACTION_ACTION_ID,
p_line_rec_type.TRANSACTION_SOURCE_ID,
p_line_rec_type.TRANSACTION_SOURCE_NAME,
--Jalaj Srivastava 5010595
--for GME (wip_enity_type=10) select trx_source_line_id as source_line_id
decode(p_line_rec_type.wip_entity_type,10,p_line_rec_type.TRX_SOURCE_LINE_ID,p_line_rec_type.SOURCE_LINE_ID) SOURCE_LINE_ID,
MSI.REVISION_QTY_CONTROL_CODE,
MSI.lot_control_code lot_control_code,
decode(p_line_rec_type.TRANSACTION_ACTION_ID,2,p_line_rec_type.TRANSFER_SUBINVENTORY,28,p_line_rec_type.TRANSFER_SUBINVENTORY,5,p_line_rec_type.transfer_subinventory,null) TRANSFER_SUBINVENTORY,
p_line_rec_type.TRANSFER_LOCATOR,
p_line_rec_type.transaction_date,
MP.NEGATIVE_INV_RECEIPT_CODE
FROM MTL_TRANSACTION_LOTS_INTERFACE MTLI,
MTL_PARAMETERS MP,
MTL_SYSTEM_ITEMS MSI
WHERE MP.ORGANIZATION_ID = p_line_rec_type.ORGANIZATION_ID
--AND MP.NEGATIVE_INV_RECEIPT_CODE = 2 'bug 3679189'
AND MTLI.TRANSACTION_INTERFACE_ID = p_line_rec_type.TRANSACTION_INTERFACE_ID
AND p_line_rec_type.PROCESS_FLAG = 1
AND MSI.LOT_CONTROL_CODE = 2
AND ( ( (p_line_rec_type.wip_entity_type <> 10)
AND ( (p_flow_sch <> 1
AND p_line_rec_type.TRANSACTION_ACTION_ID IN (1,2,3,21,32,34,5) )
OR (p_flow_sch = 1
AND p_line_rec_type.TRANSACTION_ACTION_ID = 32 )
)
)
--Jalaj Srivastava 5232394
--select all transactions for GME
OR (p_line_rec_type.wip_entity_type = 10)
)
AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSI.ORGANIZATION_ID = p_line_rec_type.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = p_line_rec_type.INVENTORY_ITEM_ID
-- Pawan 11th july added this for validation of lot for GME only
AND ((p_line_rec_type.transaction_source_type_id <> 5) OR
(p_line_rec_type.transaction_source_type_id = 5 AND
p_line_rec_type.wip_entity_type = 10 ));--J-dev verify
SELECT *
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_header_id
AND NVL(transaction_status, 1) <> 2 -- don't consider suggestions
AND process_flag = 'Y'
ORDER BY transaction_batch_id;
UPDATE mtl_material_transactions_temp
SET last_update_date = SYSDATE
, transaction_temp_id =
NVL (transaction_temp_id, mtl_material_transactions_s.NEXTVAL)
, last_updated_by = l_userid
, last_update_login = l_loginid
, program_application_id = l_applid
, program_id = l_progid
, request_id = l_reqstid
, program_update_date = SYSDATE
, ERROR_CODE = NULL
, error_explanation = NULL
WHERE process_flag = 'Y'
AND NVL (transaction_status, ts_default) <> ts_save_only /* 2STEP */
AND transaction_header_id = l_header_id;
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
SET PRIMARY_QUANTITY = ROUND(PRIMARY_QUANTITY,5),
TRANSACTION_QUANTITY = ROUND(TRANSACTION_QUANTITY,5)
WHERE PROCESS_FLAG = 'Y'
AND NVL(TRANSACTION_STATUS,TS_DEFAULT) <> TS_SAVE_ONLY /* 2STEP */
AND TRANSACTION_HEADER_ID = l_header_id
AND transaction_source_type_id <> 5;
UPDATE mtl_transaction_lots_temp
SET PRIMARY_QUANTITY = ROUND(PRIMARY_QUANTITY,5),
TRANSACTION_QUANTITY = ROUND(TRANSACTION_QUANTITY,5)
WHERE transaction_temp_id
IN ( SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE PROCESS_FLAG = 'Y'
AND NVL(TRANSACTION_STATUS,TS_DEFAULT) <> TS_SAVE_ONLY /* 2STEP */
AND TRANSACTION_HEADER_ID = l_header_id
AND transaction_source_type_id <> 5);
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
SET PRIMARY_QUANTITY = ROUND(PRIMARY_QUANTITY,6),
TRANSACTION_QUANTITY = ROUND(TRANSACTION_QUANTITY,6)
WHERE PROCESS_FLAG = 'Y'
AND NVL(TRANSACTION_STATUS,TS_DEFAULT) <> TS_SAVE_ONLY /* 2STEP */
AND TRANSACTION_HEADER_ID = l_header_id
AND transaction_source_type_id = 5;
UPDATE mtl_transaction_lots_temp
SET PRIMARY_QUANTITY = ROUND(PRIMARY_QUANTITY,6),
TRANSACTION_QUANTITY = ROUND(TRANSACTION_QUANTITY,6)
WHERE transaction_temp_id
IN( SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE PROCESS_FLAG = 'Y'
AND NVL(TRANSACTION_STATUS,TS_DEFAULT) <> TS_SAVE_ONLY /* 2STEP */
AND TRANSACTION_HEADER_ID = l_header_id
AND transaction_source_type_id = 5);
SELECT COUNT (1)
INTO l_process
FROM mtl_material_transactions_temp
WHERE transaction_header_id = l_header_id
AND process_flag = 'Y'
AND transaction_status = 3 /* not able to use the TS_PROCESS macro */
AND ROWNUM < 2;
SELECT COUNT (*)
INTO l_totrows
FROM mtl_material_transactions_temp
WHERE transaction_header_id = l_header_id
AND process_flag = 'Y'
AND NVL (transaction_status, ts_default) <> ts_save_only; /* 2STEP */
SELECT transaction_source_type_id, wip_entity_type
INTO l_srctypeid, l_wip_entity_type
FROM mtl_material_transactions_temp
WHERE transaction_header_id = l_header_id AND ROWNUM < 2;
/* No need to update MMTT after returning from process_lpn_trx as this has already
been done within the Java code. - Bug 2284667 */
IF fnd_api.to_boolean (p_commit)
THEN
COMMIT WORK;
SELECT COUNT (*)
INTO l_totrows
FROM mtl_material_transactions_temp
WHERE transaction_header_id = l_header_id AND process_flag = 'Y';
UPDATE mtl_material_transactions_temp
SET transaction_header_id = (-1) * l_header_id
WHERE transaction_header_id = l_header_id
AND process_flag = 'Y';
UPDATE mtl_material_transactions_temp
SET transaction_header_id = ABS (l_header_id)
WHERE transaction_header_id = (-1) * (l_header_id)
AND process_flag = 'Y'
AND ROWNUM < 201;
UPDATE mtl_material_transactions_temp
SET transaction_header_id = ABS (l_header_id)
WHERE transaction_header_id = (-1) * (l_header_id)
AND process_flag = 'Y'
AND ROWNUM < 201;
UPDATE mtl_transactions_interface
SET last_update_date = SYSDATE
, transaction_interface_id =
NVL (transaction_interface_id
, mtl_material_transactions_s.NEXTVAL
)
, transaction_batch_id =
NVL (transaction_batch_id
, DECODE (transaction_source_type_id
, 2, DECODE (l_batch_size
, 0, transaction_batch_id
, CEIL (ROWNUM / l_batch_size)
)
, 8, DECODE (l_batch_size
, 0, transaction_batch_id
, CEIL (ROWNUM / l_batch_size)
)
, 16, DECODE (l_batch_size
, 0, transaction_batch_id
, CEIL (ROWNUM / l_batch_size)
)
, transaction_batch_id
)
)
, last_updated_by = l_userid
, last_update_login = l_loginid
, program_application_id = l_applid
, program_id = l_progid
, request_id = l_reqstid
, program_update_date = SYSDATE
, lock_flag = 1
WHERE process_flag = 1 AND transaction_header_id = l_header_id;
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
TRANSACTION_INTERFACE_ID = NVL(TRANSACTION_INTERFACE_ID,
mtl_material_transactions_s.nextval),
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_APPLICATION_ID = l_applid,
PROGRAM_ID = l_progid,
REQUEST_ID = l_reqstid,
PROGRAM_UPDATE_DATE = SYSDATE,
LOCK_FLAG = 1
WHERE PROCESS_FLAG = 1
AND TRANSACTION_HEADER_ID = l_header_id;
SELECT NVL (validation_required, 1)
, mtt.transaction_source_type_id, mti.wip_entity_type
INTO l_valreq
, l_srctypeid, l_wip_entity_type
FROM mtl_transactions_interface mti, mtl_transaction_types mtt
WHERE transaction_header_id = l_header_id
AND mtt.transaction_type_id = mti.transaction_type_id
AND ROWNUM < 2;
SELECT DECODE (UPPER (flow_schedule), 'Y', 1, 0)
INTO inv_txn_manager_grp.gi_flow_schedule
FROM mtl_transactions_interface
WHERE transaction_header_id = l_header_id
AND transaction_source_type_id = 5
AND transaction_action_id IN
(30, 31, 32) --CFM Scrap Transactions
AND process_flag = 1
AND ROWNUM < 2;
SELECT transaction_source_type_id INTO l_source_type_id
FROM MTL_TRANSACTIONS_INTERFACE
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND ROWNUM < 2;
SELECT transaction_source_type_id
INTO l_source_type_id
FROM mtl_transactions_interface
WHERE transaction_header_id = l_header_id
AND process_flag = 1
AND ROWNUM < 2;
/** UPDATE MTI row with Group Failure Message **/
NULL;
SELECT COUNT (1)
INTO l_batch_count
FROM mtl_transactions_interface
WHERE parent_id = l_line_rec_type.parent_id
AND transaction_batch_id IS NULL;
UPDATE mtl_transactions_interface
SET last_update_date = SYSDATE
, last_updated_by = l_userid
, last_update_login = l_loginid
, program_update_date = SYSDATE
, process_flag = 3
, lock_flag = 2
, ERROR_CODE = SUBSTR (l_error_code, 1, 240)
, error_explanation = SUBSTRB (l_error_exp, 1, 240)
WHERE parent_id = l_line_rec_type.parent_id
AND process_flag = 1;
UPDATE mtl_transactions_interface
SET last_update_date = SYSDATE
, last_updated_by = l_userid
, last_update_login = l_loginid
, program_update_date = SYSDATE
, process_flag = 3
, lock_flag = 2
, ERROR_CODE = SUBSTR (l_error_code, 1, 240)
, error_explanation = SUBSTRB (x_msg_data, 1, 240)
WHERE ROWID = l_line_rec_type.ROWID
AND process_flag = 1;
UPDATE mtl_transactions_interface
SET last_update_date = SYSDATE
, last_updated_by = l_userid
, last_update_login = l_loginid
, program_update_date = SYSDATE
, process_flag = 3
, lock_flag = 2
, ERROR_CODE = SUBSTRB (l_error_code, 1, 240)
, error_explanation = SUBSTRB (x_msg_data, 1, 240)
WHERE ROWID = l_line_rec_type.ROWID
AND process_flag = 1;
UPDATE mtl_transactions_interface
SET last_update_date = SYSDATE
, last_updated_by = l_userid
, last_update_login = l_loginid
, program_update_date = SYSDATE
, process_flag = 3
, lock_flag = 2
, ERROR_CODE = SUBSTR (l_error_code, 1, 240)
, error_explanation = SUBSTRB (x_msg_data, 1, 240)
WHERE ROWID = l_line_rec_type.ROWID
AND process_flag = 1;
UPDATE mtl_transactions_interface
SET last_update_date = SYSDATE
, last_updated_by = l_userid
, last_update_login = l_loginid
, program_update_date = SYSDATE
, process_flag = 3
, lock_flag = 2
, ERROR_CODE = SUBSTR (l_error_code, 1, 240)
, error_explanation = SUBSTRB (x_msg_data, 1, 240)
WHERE ROWID = l_line_rec_type.ROWID AND process_flag = 1;
SELECT distribution_account_id
INTO l_dist_acct_id
FROM mtl_transactions_interface
WHERE ROWID = l_line_rec_type.ROWID;
before inserting record into MMTT.
*/
IF ( l_srctypeid = 5 ) THEN
IF ( l_current_err_batch_id IS NULL
OR l_Line_rec_Type.transaction_batch_id IS NULL
OR l_current_err_batch_id <> l_Line_rec_Type.transaction_batch_id )THEN --050
l_current_batch_failed := FALSE;
SELECT demand_source_header_id , demand_source_line
INTO l_dem_hdr_id,l_dem_line_id
FROM mtl_transactions_interface
WHERE
ROWID = l_temp_rowid ;
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = l_temp_rowid
AND PROCESS_FLAG = 1;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_BATCH_ID =l_Line_rec_Type.transaction_batch_id;
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = l_temp_rowid
AND PROCESS_FLAG = 1;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id; --Bug#5075521
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_interface_id = l_temp_rowid
AND PROCESS_FLAG = 1;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id; --Bug#5075521
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = l_temp_rowid
AND PROCESS_FLAG = 1;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;--Bug#5075521
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = l_temp_rowid
AND PROCESS_FLAG = 1;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE TRANSACTION_interface_id = l_temp_rowid
AND PROCESS_FLAG = 1;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = l_temp_rowid
AND PROCESS_FLAG = 1;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = l_temp_rowid
AND PROCESS_FLAG = 1;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = l_temp_rowid
AND PROCESS_FLAG = 1;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
-- update the qty
-- Pawan 11th july Added - GME does not have transfer subinventory
IF ( NOT l_current_batch_failed ) THEN --300
IF (l_actid in (2,28)) then
inv_quantity_tree_pub.update_quantities
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_tree_mode => 2,
p_is_revision_control => l_revision_control,
p_is_lot_control => l_lot_control,
p_is_serial_control => FALSE,
p_demand_source_type_id => nvl(l_srctypeid,-9999),
p_demand_source_header_id => nvl(l_srcid,-9999),
p_demand_source_line_id => nvl(l_srclineid,-9999),
p_revision => l_rev,
p_lot_number => l_lotnum,
p_subinventory_code => l_xfrsub,
p_locator_id => l_xlocid,
p_primary_quantity => l_trx_qty,
p_quantity_type => inv_quantity_tree_pvt.g_qoh,
p_onhand_source => inv_quantity_tree_pvt.g_all_subs,
x_qoh => l_qoh,
x_rqoh => l_rqoh,
x_qr => l_qr,
x_qs => l_qs,
x_att => l_att,
x_atr => l_atr);
FND_MESSAGE.set_token('ROUTINE','INV_QUANTITY_TREE_PUB.UPDATE_QUANTITIES');
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = l_temp_rowid
AND PROCESS_FLAG = 1;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
update tree with correct sign.
sign is derived from transaction_quantity */
inv_quantity_tree_pub.update_quantities
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_organization_id => l_org_id,
p_inventory_item_id => l_item_id,
p_tree_mode => 2,
p_is_revision_control => l_revision_control,
p_is_lot_control => l_lot_control,
p_is_serial_control => FALSE,
p_demand_source_type_id => nvl(l_srctypeid,-9999),
p_demand_source_header_id => nvl(l_srcid,-9999),
p_demand_source_line_id => nvl(l_srclineid,-9999),
p_revision => l_rev,
p_lot_number => l_lotnum,
p_subinventory_code => l_sub_code,
p_locator_id => l_locid,
p_primary_quantity => (sign(l_line_rec_type.transaction_quantity)*(l_trx_qty)),
p_quantity_type => inv_quantity_tree_pvt.g_qoh,
p_onhand_source => inv_quantity_tree_pvt.g_all_subs,
x_qoh => l_qoh,
x_rqoh => l_rqoh,
x_qr => l_qr,
x_qs => l_qs,
x_att => l_att,
x_atr => l_atr);
FND_MESSAGE.set_token('ROUTINE','INV_QUANTITY_TREE_PUB.UPDATE_QUANTITIES');
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240),
ERROR_EXPLANATION = substrb(l_error_exp,1,240)
WHERE ROWID = l_temp_rowid
AND PROCESS_FLAG = 1;
UPDATE MTL_TRANSACTIONS_INTERFACE MTI
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = substrb(l_error_code,1,240)
WHERE TRANSACTION_HEADER_ID = l_header_id
AND PROCESS_FLAG = 1
AND TRANSACTION_BATCH_ID = l_Line_rec_Type.transaction_batch_id;
--qty tree update was successful
IF (l_debug = 1) THEN
inv_log_util.trace('after update of quantity tree qoh='||l_qoh||' l_att='||l_att||' l_atr='||l_atr,'INV_TXN_MANAGER_PUB', 9);
UPDATE MTL_TRANSACTIONS_INTERFACE
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_userid,
LAST_UPDATE_LOGIN = l_loginid,
PROGRAM_UPDATE_DATE = SYSDATE,
PROCESS_FLAG = 3,
LOCK_FLAG = 2,
ERROR_CODE = SUBSTR (l_error_code, 1, 240),
ERROR_EXPLANATION = SUBSTR (l_error_code, 1, 240)
WHERE ROWID = l_line_rec_type.rowid
AND PROCESS_FLAG = 1;
/* Insert into MMTT */
/** Change for lOt Transactions **/
IF l_line_rec_type.transaction_source_type_id = 13
AND l_line_rec_type.transaction_action_id IN (40, 41, 42)
THEN
IF (l_line_rec_type.transaction_action_id = 40)
THEN
IF (l_debug = 1)
THEN
inv_log_util.TRACE ('Checking for lot partial split'
, 'INV_TXN_MANAGER_PUB'
, 9
);
UPDATE mtl_transactions_interface
SET last_update_date = SYSDATE
, last_updated_by = l_userid
, last_update_login = l_loginid
, program_update_date = SYSDATE
, process_flag = 3
, lock_flag = 2
, ERROR_CODE = SUBSTR (l_error_code, 1, 240)
, error_explanation = SUBSTR (l_error_exp, 1, 240)
WHERE ROWID = l_line_rec_type.ROWID AND process_flag = 1;
UPDATE mtl_transactions_interface mti
SET last_update_date = SYSDATE
, last_updated_by = l_userid
, last_update_login = l_loginid
, program_update_date = SYSDATE
, process_flag = 3
, lock_flag = 2
, ERROR_CODE = SUBSTRB (l_error_code, 1, 240)
WHERE transaction_header_id = l_header_id
AND process_flag = 1
AND transaction_batch_id IN (
SELECT DISTINCT mti2.transaction_batch_id
FROM mtl_transactions_interface mti2
WHERE mti2.transaction_header_id = l_header_id
AND mti2.process_flag = 3
AND mti2.ERROR_CODE IS NOT NULL);
SELECT transaction_source_type_id
INTO l_source_type_id
FROM mtl_transactions_interface
WHERE transaction_header_id = l_header_id
AND process_flag = 1
AND ROWNUM < 2;
UPDATE mtl_transactions_interface mti
SET last_update_date = SYSDATE
, last_updated_by = l_userid
, last_update_login = l_loginid
, program_update_date = SYSDATE
, process_flag = 3
, lock_flag = 2
, ERROR_CODE = SUBSTRB (l_error_code, 1, 240)
WHERE transaction_header_id = l_header_id
AND process_flag = 1
AND transaction_batch_id IN (
SELECT DISTINCT mti2.transaction_batch_id
FROM mtl_transactions_interface mti2
WHERE mti2.transaction_header_id = l_header_id
AND mti2.process_flag = 3
AND mti2.ERROR_CODE IS NOT NULL);
SELECT transaction_source_type_id
INTO l_source_type_id
FROM mtl_transactions_interface
WHERE transaction_header_id = l_header_id
AND process_flag = 1
AND ROWNUM < 2;
*tmpinsert will insert the data into the temp tables for transactions
*other than split/merge/translate. Only if it is a success we move on to
*tmpinsert2 whihc handles the three transactions
*/
IF (l_debug = 1)
THEN
inv_log_util.TRACE ('Calling tmpinsert'
, 'INV_TXN_MANAGER_PUB'
, 9
);
IF (NOT tmpinsert (l_header_id))
THEN
l_error_exp := fnd_message.get;
inv_log_util.TRACE ('Error in tmpinsert=' || l_error_exp
, 'INV_TXN_MANAGER_PUB'
, 9
);
inv_log_util.TRACE ( 'Calling tmpinsert2'
, 'INV_TXN_MANAGER_PUB'
, 9
);
inv_txn_manager_grp.tmpinsert2(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, x_validation_status => l_validation_status
, p_header_id => l_header_id);
inv_log_util.TRACE ( 'After tmpinsert2'
, 'INV_TXN_MANAGER_PUB'
, 9
);
inv_log_util.TRACE ( 'tmpinsert2 raised exception '
, 'INV_TXN_MANAGER_PUB'
, 9
);
inv_log_util.TRACE ( 'tmpinsert2 failed..returned with error '
, 'INV_TXN_MANAGER_PUB'
, 9
);
SELECT serial_number_control_code
INTO l_serial_control
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id= p_mmtt.inventory_item_id
AND organization_id = p_mmtt.organization_id;
SELECT project_id INTO l_from_project_id
FROM mtl_item_locations
WHERE inventory_location_id = p_mmtt.locator_id
AND organization_id = p_mmtt.organization_id;
inv_cost_group_update.cost_group_update
(p_transaction_rec => p_mmtt,
p_fob_point => null,
p_transfer_wms_org => FALSE,
p_tfr_primary_cost_method => null,
p_tfr_org_cost_group_id => null,
p_from_project_id => l_from_project_id,
p_to_project_id => null,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
/* Delete the errored out flow schedules */
IF (inv_txn_manager_grp.gi_flow_schedule <> 0)
THEN
wip_flow_utilities.delete_flow_schedules (l_header_id);
SELECT COUNT (*)
INTO l_midtotrows
FROM mtl_material_transactions_temp
WHERE transaction_header_id = l_header_id AND process_flag = 'Y';
DELETE FROM mtl_material_transactions_temp
WHERE transaction_header_id = l_header_id
AND shippable_flag = 'N'
AND process_flag = 'Y';
SELECT COUNT (*)
INTO l_totrows
FROM mtl_material_transactions_temp
WHERE transaction_header_id = l_header_id AND process_flag = 'Y';
delete from mtl_serial_numbers_temp
where transaction_temp_id in (
select mmtt.transaction_temp_id
from mtl_material_transactions_temp mmtt
where mmtt.transaction_header_id = l_header_id );
delete from mtl_serial_numbers_temp
where transaction_temp_id in (
select mtlt.serial_transaction_temp_id
from mtl_transaction_lots_temp mtlt
where mtlt.transaction_temp_id in (
select mmtt.transaction_temp_id
from mtl_material_transactions_temp mmtt
where mmtt.transaction_header_id = l_header_id));
DELETE from mtl_transaction_lots_temp
where transaction_temp_id in
(select mmtt.transaction_temp_id
from MTL_MATERIAL_TRANSACTIONS_TEMP mmtt
WHERE mmtt.TRANSACTION_HEADER_ID = l_header_id );
DELETE FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE TRANSACTION_HEADER_ID = l_header_id;
inv_log_util.trace('Deleted MSNT/MTLT/MMTT for header_id ' || l_header_id, 'INV_TXN_MANAGER_PUB',1);
SELECT COUNT (*)
INTO l_totrows
FROM mtl_material_transactions_temp
WHERE transaction_header_id = l_header_id AND process_flag = 'Y';
UPDATE mtl_material_transactions_temp
SET transaction_header_id = (-1) * l_header_id
WHERE transaction_header_id = l_header_id
AND process_flag = 'Y';
UPDATE mtl_material_transactions_temp
SET transaction_header_id = ABS (l_header_id)
WHERE transaction_header_id = (-1) * (l_header_id)
AND process_flag = 'Y'
AND ROWNUM < 201;
UPDATE mtl_material_transactions_temp
SET transaction_header_id = ABS (l_header_id)
WHERE transaction_header_id = (-1) * (l_header_id)
AND process_flag = 'Y'
AND ROWNUM < 201;
UPDATE mtl_transactions_interface
SET last_update_date = SYSDATE
, last_updated_by = l_userid
, last_update_login = l_loginid
, program_update_date = SYSDATE
, process_flag = 3
, lock_flag = 2
, ERROR_CODE = SUBSTRB (l_error_code, 1, 240)
, error_explanation = SUBSTRB (l_error_exp, 1, 240)
WHERE transaction_header_id = l_header_id AND process_flag = 1;
* Name: insert_relief
* Description:
* Creates a row in MRP_RELIEF_INTERFACE with the values it's passed.
* This process was taken from mrlpr1.ppc to facilitate PLtion of PL/SQL TM API
*
******************************************************************/
FUNCTION insert_relief (
p_new_order_qty NUMBER
, p_new_order_date DATE
, p_old_order_qty NUMBER
, p_old_order_date DATE
, p_item_id NUMBER
, p_org_id NUMBER
, p_disposition_id NUMBER
, p_user_id NUMBER
, p_line_num VARCHAR2
, p_relief_type NUMBER
, p_disposition VARCHAR2
, p_demand_class VARCHAR2
)
RETURN BOOLEAN
IS
BEGIN
IF (p_relief_type = mds_relief)
THEN
IF (p_disposition <> r_sales_order)
THEN
fnd_message.set_name ('MRP', 'GEN-invalid entity');
INSERT INTO mrp_relief_interface
(transaction_id
, inventory_item_id
, organization_id
, relief_type
, disposition_type
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, new_order_quantity
, new_order_date
, old_order_quantity
, old_order_date
, disposition_id
, demand_class
, process_status
, line_num
)
VALUES (mrp_relief_interface_s.NEXTVAL
, p_item_id
, p_org_id
, p_relief_type
, p_disposition
, SYSDATE
, p_user_id
, SYSDATE
, p_user_id
, -1
, p_new_order_qty
, p_new_order_date
, p_old_order_qty
, p_old_order_date
, p_disposition_id
, p_demand_class
, to_be_processed
, p_line_num
);
inv_log_util.TRACE ('Error in insert_relief'
, 'INV_TXN_MANAGER_PUB'
, 9
);
END insert_relief;
, p_last_updated_by NUMBER
, p_org_id NUMBER
, p_line_num VARCHAR2
, p_shipment_date DATE
, p_demand_class VARCHAR2
)
RETURN BOOLEAN
AS
BEGIN
IF (NOT insert_relief (p_quantity
, p_shipment_date
, 0
, NULL
, p_inv_item_id
, p_org_id
, p_disposition_id
, p_last_updated_by
, p_line_num
, mds_relief
, r_sales_order
, p_demand_class
)
)
THEN
RETURN (FALSE);
SELECT p_line_rec_type.ROWID
, p_line_rec_type.inventory_item_id
, p_line_rec_type.revision
, p_line_rec_type.organization_id
, p_line_rec_type.subinventory_code
, p_line_rec_type.locator_id
, ABS (p_line_rec_type.primary_quantity)
, NULL
, p_line_rec_type.transaction_source_type_id
, p_line_rec_type.transaction_action_id
,p_line_rec_type.TRANSACTION_TYPE_ID /*Bug:4866991*/
, p_line_rec_type.transaction_source_id
, p_line_rec_type.transaction_source_name
, TO_CHAR (p_line_rec_type.source_line_id)
, msi.revision_qty_control_code
, msi.lot_control_code
, DECODE (p_line_rec_type.transaction_action_id
, 2, p_line_rec_type.transfer_subinventory
, 28, p_line_rec_type.transfer_subinventory
, NULL
)
, p_line_rec_type.transfer_locator
, p_line_rec_type.transaction_date
, mp.negative_inv_receipt_code
FROM mtl_parameters mp, mtl_system_items msi
WHERE mp.organization_id = p_line_rec_type.organization_id
-- AND MP.NEGATIVE_INV_RECEIPT_CODE = 2
AND p_line_rec_type.process_flag = 1
-- AND p_line_rec_type.SHIPPABLE_FLAG='Y'
AND msi.lot_control_code = 1
AND ( ( p_flow_sch <> 1
AND p_line_rec_type.transaction_action_id IN
(1, 2, 3, 21, 32, 34, 5)
)
OR (p_flow_sch = 1
AND p_line_rec_type.transaction_action_id = 32
)
)
AND msi.organization_id = mp.organization_id
AND msi.organization_id = p_line_rec_type.organization_id
AND msi.inventory_item_id = p_line_rec_type.inventory_item_id
UNION
SELECT p_line_rec_type.ROWID
, p_line_rec_type.inventory_item_id
, p_line_rec_type.revision
, p_line_rec_type.organization_id
, p_line_rec_type.subinventory_code
, p_line_rec_type.locator_id
, ABS (mtli.primary_quantity)
, mtli.lot_number
, p_line_rec_type.transaction_source_type_id
, p_line_rec_type.transaction_action_id
,p_line_rec_type.TRANSACTION_TYPE_ID /*Bug:4866991*/
, p_line_rec_type.transaction_source_id
, p_line_rec_type.transaction_source_name
, TO_CHAR (p_line_rec_type.source_line_id)
, msi.revision_qty_control_code
, msi.lot_control_code
, DECODE (p_line_rec_type.transaction_action_id
, 2, p_line_rec_type.transfer_subinventory
, 28, p_line_rec_type.transfer_subinventory
, 5, p_line_rec_type.transfer_subinventory
, NULL
)
, p_line_rec_type.transfer_locator
, p_line_rec_type.transaction_date
, mp.negative_inv_receipt_code
FROM mtl_transaction_lots_interface mtli
, mtl_parameters mp
, mtl_system_items msi
WHERE mp.organization_id = p_line_rec_type.organization_id
-- AND MP.NEGATIVE_INV_RECEIPT_CODE = 2
-- AND p_line_rec_type.SHIPPABLE_FLAG='Y'
AND mtli.transaction_interface_id =
p_line_rec_type.transaction_interface_id
AND p_line_rec_type.process_flag = 1
AND ts_default <> ts_save_only
AND msi.lot_control_code = 2
AND ( ( p_flow_sch <> 1
AND p_line_rec_type.transaction_action_id IN
(1, 2, 3, 21, 32, 34, 5)
)
OR (p_flow_sch = 1
AND p_line_rec_type.transaction_action_id = 32
)
)
AND msi.organization_id = mp.organization_id
AND msi.organization_id = p_line_rec_type.organization_id
AND msi.inventory_item_id = p_line_rec_type.inventory_item_id
AND MTLI.LOT_NUMBER = NVL(p_lot_number, MTLI.LOT_NUMBER); -- Added for 4377625
SELECT a.organization_id
, a.inventory_item_id
, NVL (a.transaction_source_id, 0)
, a.transaction_source_type_id
, a.trx_source_delivery_id
, a.trx_source_line_id
, a.revision
, DECODE (c.lot_control_code, 2, b.lot_number, a.lot_number)
, a.subinventory_code
, a.locator_id
, DECODE (c.lot_control_code
, 2, ABS (NVL (b.primary_quantity, 0))
, a.primary_quantity * (-1)
)
, a.transaction_source_name
, a.transaction_date
, a.content_lpn_id
FROM mtl_system_items c
, mtl_transaction_lots_temp b
, mtl_material_transactions_temp a
WHERE a.transaction_header_id = p_line_rec_type.transaction_header_id
AND a.transaction_temp_id =
p_line_rec_type.transaction_interface_id
AND a.organization_id = c.organization_id
AND a.inventory_item_id = c.inventory_item_id
AND b.transaction_temp_id(+) = a.transaction_temp_id
AND a.primary_quantity < 0
ORDER BY a.transaction_source_type_id
, a.transaction_source_id
, a.transaction_source_name
, a.trx_source_line_id
, a.trx_source_delivery_id
, a.inventory_item_id
, a.organization_id;
PROCEDURE rel_reservations_mrp_update
(p_header_id IN NUMBER
, p_transaction_temp_id IN NUMBER
, p_transaction_id IN NUMBER DEFAULT NULL
, p_res_sts OUT NOCOPY VARCHAR2
, p_res_msg OUT NOCOPY VARCHAR2
, p_res_count OUT NOCOPY NUMBER
, p_mrp_status OUT NOCOPY VARCHAR2
)
IS
CURSOR c1
IS
SELECT a.organization_id
, a.inventory_item_id
, NVL (a.transaction_source_id, 0)
, a.transaction_source_type_id
, a.trx_source_delivery_id
, a.trx_source_line_id
, a.revision
, DECODE (c.lot_control_code, 2, b.lot_number, a.lot_number)
, a.subinventory_code
, a.locator_id
, DECODE (c.lot_control_code
, 2, ABS (NVL (b.primary_quantity, 0))
, a.primary_quantity * (-1)
)
, a.transaction_source_name
, a.transaction_date
, a.content_lpn_id
, a.primary_quantity
, --
a.transaction_action_id
, A.transaction_type_id /*Bug:4866991*/
, a.transfer_subinventory
, a.transfer_to_location
, DECODE (a.process_flag, 'Y', 1, 'N', 2, 'E', 3, 3)
, a.shippable_flag
, b.transaction_temp_id --lot record identifier in MTLT
, a.relieve_high_level_rsv_flag /*** {{ R12 Enhanced reservations code changes ***/
FROM mtl_system_items c
, mtl_transaction_lots_temp b
, mtl_material_transactions_temp a
WHERE a.transaction_header_id = p_header_id
AND a.transaction_temp_id = p_transaction_temp_id
AND a.organization_id = c.organization_id
AND a.inventory_item_id = c.inventory_item_id
AND b.transaction_temp_id(+) = a.transaction_temp_id
-- AND A.PRIMARY_QUANTITY < 0 /* Bug: 3462946: This clause is commented as BaseTransaction.java already does this validation */
ORDER BY a.transaction_source_type_id
, a.transaction_source_id
, a.transaction_source_name
, a.trx_source_line_id
, a.trx_source_delivery_id
, a.inventory_item_id
, a.organization_id;
l_procedure_name VARCHAR2(60) := g_pkg_name || '.' || 'REL_RESERVATIONS_MRP_UPDATE';
SELECT lot_control_code,
revision_qty_control_code
INTO l_lctrl,
l_rctrl
FROM mtl_system_items_b
WHERE organization_id = l_org_id
AND inventory_item_id = l_item_id;
UPDATE mtl_transactions_interface
SET last_update_date = SYSDATE
, last_updated_by = l_userid
, last_update_login = l_loginid
, program_update_date = SYSDATE
, process_flag = 3
, lock_flag = 2
, ERROR_CODE = SUBSTRB (l_error_code, 1, 240)
, error_explanation = SUBSTRB (l_error_exp, 1, 240)
--WHERE ROWID = l_Line_rec_type.rowid
WHERE transaction_interface_id = p_transaction_temp_id
AND process_flag = 1
AND organization_id = l_org_id
AND inventory_item_id = l_item_id
AND NVL (subinventory_code, '@@@@') = NVL (l_sub_code, '@@@@');
UPDATE mtl_transactions_interface
SET last_update_date = SYSDATE
, last_updated_by = l_userid
, last_update_login = l_loginid
, program_update_date = SYSDATE
, process_flag = 3
, lock_flag = 2
, ERROR_CODE = SUBSTRB (l_error_code, 1, 240)
--WHERE TRANSACTION_HEADER_ID = l_header_id
WHERE transaction_interface_id = p_transaction_temp_id
AND process_flag = 1;
inv_log_util.TRACE ( 'When others Ex..rel_reservations_mrp_update '
|| l_error_code
, l_procedure_name
, '1'
);
END rel_reservations_mrp_update;