[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT transaction_type_id,
inv_material_transaction_id,
transaction_date,
transacted_by
INTO l_txn_type_id,
l_mtl_txn_id,
l_txn_date,
l_transacted_by
FROM csi_transactions
WHERE transaction_id = p_txn_id;
SELECT source_txn_type_name
INTO l_txn_type
FROM csi_txn_types
WHERE transaction_type_id = l_txn_type_id;
PROCEDURE update_txn_status (
p_src_move_trans_tbl IN move_trans_tbl,
p_dest_move_trans_tbl IN move_trans_tbl,
p_conc_request_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2)
IS
l_txn_rec csi_datastructures_pub.transaction_rec ;
SELECT object_version_number
FROM csi_transactions
WHERE transaction_id = c_transaction_id ;
debug('Inside API update_txn_status');
debug('Inside API csi_transactions_pvt.update_transactions');
csi_transactions_pvt.update_transactions(
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_transaction_rec => l_txn_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
debug('Inside API csi_transactions_pvt.update_transactions');
csi_transactions_pvt.update_transactions(
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_transaction_rec => l_txn_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
END update_txn_status ;
SELECT fcgd.retirement_prorate_convention
FROM fa_category_book_defaults fcgd,
fa_books fb,
fa_additions_b fa
WHERE fa.asset_id = c_asset_id
AND fb.asset_id = fa.asset_id
AND fb.book_type_code = c_book_type_code
AND fb.date_ineffective IS NULL
AND fcgd.category_id = fa.asset_category_id
AND fcgd.book_type_code = fb.book_type_code
AND fb.date_placed_in_service
BETWEEN fcgd.start_dpis AND NVL(fcgd.end_dpis, fb.date_placed_in_service);
SELECT sysdate INTO l_sysdate FROM sys.dual ;
SELECT fa_mass_ext_retirements_s.nextval
INTO l_mass_external_retire_id
FROM dual ;
l_ext_ret_rec.last_updated_by := fnd_global.user_id ;
l_ext_ret_rec.last_update_date := l_sysdate ;
l_ext_ret_rec.last_update_login := fnd_global.login_id ;
cse_asset_adjust_pkg.insert_retirement(
p_ext_ret_rec => l_ext_ret_rec,
x_return_status => l_return_status,
x_error_msg => l_error_msg) ;
debug('Insert into Retirements table failed ');
SELECT cii.instance_id,
cii.quantity instance_qty,
cii.serial_number instance_serial_number,
NVL(cii.active_end_date,sysdate) active_end_date,
fa.asset_id fa_asset_id,
fa.asset_category_id fa_category_id,
fdh.book_type_code fa_book_type_code,
fb.date_placed_in_service fa_dpi,
fb.cost fa_cost,
fa.current_units fa_units,
fa.serial_number fa_serial_number,
fa.asset_key_ccid fa_key_ccid,
fa.tag_number fa_tag_number,
fa.asset_type fa_asset_type,
fa.model_number,
fa.manufacturer_name,
fb.depreciate_flag,
fdh.distribution_id,
fdh.location_id ,
NVL(fdh.units_assigned,0) fa_loc_units,
fdh.code_combination_id fa_depr_expense_ccid,
fdh.assigned_to fa_employee_id,
cia.asset_quantity instance_asset_qty,
cia.instance_asset_id
FROM fa_distribution_history fdh,
csi_i_assets cia,
fa_additions fa,
fa_books fb,
csi_item_instances cii
WHERE cii.instance_id = c_instance_id
AND cia.instance_id = cii.instance_id
AND cia.fa_asset_id = fdh.asset_id
AND cia.fa_book_type_code = fdh.book_type_code
AND cia.fa_location_id = fdh.location_id
AND sysdate BETWEEN nvl(cia.active_start_date, sysdate-1) AND nvl(cia.active_end_date, sysdate+1)
AND fdh.date_ineffective is null
AND cia.fa_asset_id = fa.asset_id
AND fa.asset_id = fb.asset_id
AND cia.fa_book_type_code = fb.book_type_code
AND fb.date_ineffective IS NULL
AND cia.asset_quantity > 0
AND cia.fa_sync_flag = 'Y'
AND NOT EXISTS (
SELECT 'X' FROM fa_retirements fr
WHERE fdh.retirement_id = fr.retirement_id
AND fr.status IN ('PENDING','ERROR'))
AND NOT EXISTS (
SELECT 'X' FROM fa_mass_ext_retirements fmer
WHERE fdh.retirement_id = fmer.retirement_id
AND fmer.review_status IN ('POST','ERROR'))
ORDER BY fb.date_placed_in_service ;
SELECT SUM(NVL(fma.fixed_assets_cost,0)) cost
FROM fa_mass_additions fma
WHERE fma.posting_status = 'POST'
AND fma.book_type_code = c_book_type_code
AND fma.add_to_asset_id = c_asset_id;
PROCEDURE update_inst_asset (
p_inst_asset_rec IN csi_datastructures_pub.instance_asset_rec ,
p_transaction_units IN NUMBER,
p_csi_txn_rec IN csi_datastructures_pub.transaction_rec,
x_return_status OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2)
IS
---Variables require for calling Pub API's
l_msg_count NUMBER;
SELECT cia.object_version_number
FROM csi_i_assets cia
WHERE cia.instance_asset_id = c_instance_asset_id ;
debug('Inside API update_inst_asset');
SELECT sysdate
INTO l_sysdate
FROM sys.DUAL ;
debug('Calling csi_asset_pvt.update_instance_asset');
csi_asset_pvt.update_instance_asset (
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_asset_rec => l_inst_asset_rec,
p_txn_rec => l_csi_txn_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_lookup_tbl => l_lookup_tbl,
p_asset_count_rec => l_asset_count_rec,
p_asset_id_tbl => l_asset_id_tbl,
p_asset_loc_tbl => l_asset_loc_tbl );
l_dest_inst_asset_query_rec.update_status := cse_datastructures_pub.g_in_service;
debug('Inside API csi_asset_pvt.update_instance_asset');
csi_asset_pvt.update_instance_asset (
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_asset_rec => l_dest_inst_asset_tbl(1),
p_txn_rec => l_csi_txn_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_lookup_tbl => l_lookup_tbl,
p_asset_count_rec => l_asset_count_rec,
p_asset_id_tbl => l_asset_id_tbl,
p_asset_loc_tbl => l_asset_loc_tbl );
l_dest_inst_asset_tbl(1).update_status := cse_datastructures_pub.G_IN_SERVICE ;
END update_inst_asset ;
l_trans_rec.who_info.last_updated_by := fnd_global.user_id ;
l_trans_rec.who_info.last_update_login := fnd_global.login_id ;
debug('Both Source and Destination Location and also Instances are same, no updates are required');
update_inst_asset (
p_inst_asset_rec => l_src_inst_asset_rec,
p_transaction_units => l_transaction_units,
p_csi_txn_rec => p_csi_txn_rec,
x_return_status => l_return_status,
x_error_msg => l_error_msg);
debug('After Source update Inst-Asset link '|| l_return_status ); --???
debug('Before Dest update Inst-Asset link '); --???
update_inst_asset (
p_inst_asset_rec => l_dest_inst_asset_rec,
p_transaction_units => l_transaction_units,
p_csi_txn_rec => p_csi_txn_rec,
x_return_status => l_return_status,
x_error_msg => l_error_msg);
debug('After Dest update Inst-Asset link '|| l_return_status ); --???
SELECT ct.transaction_id,
cii.instance_id ,
DECODE(cii.serial_number, NULL, mmt.primary_quantity, 1) primary_units,
cii.serial_number,
Nvl(mmt.inventory_item_id, cii.inventory_item_id) inventory_item_id ,
cii.instance_usage_code,
ctt.source_transaction_type ,
NVL(mmt.organization_id,cii.last_vld_organization_id ) inv_organization_id,
mmt.subinventory_code inv_subinventory_name ,
cii.location_id ,
cii.location_type_code ,
ct.transaction_date ,
mmt.transaction_id inv_material_transaction_id ,
ct.object_version_number,
cii.operational_status_code
FROM csi_item_instances cii,
csi_item_instances_h ciih,
csi_transactions ct,
mtl_material_transactions mmt,
csi_txn_types ctt
WHERE ct.transaction_id = p_transaction_id
AND ct.inv_material_transaction_id = mmt.transaction_id(+)
AND ct.transaction_type_id = ctt.transaction_type_id
AND cii.instance_id = ciih.instance_id
AND ciih.transaction_id = ct.transaction_id
AND (Nvl(mmt.primary_quantity,-1) < 0
OR
--Misc Receipt from HZ Loc
(ct.transaction_type_id = 134 AND cii.operational_status_code = 'OUT_OF_SERVICE')
AND
cii.serial_number IS NULL) ;
SELECT transaction_error_id
FROM csi_txn_errors
WHERE transaction_id = c_transaction_id
AND source_type = 'ASSET_MOVE' ;
update_txn_status (
p_src_move_trans_tbl => l_src_move_trans_tbl,
p_dest_move_trans_tbl => l_dest_move_trans_tbl,
p_conc_request_id => p_conc_request_id,
x_return_status => l_return_status,
x_error_msg => l_error_msg);
debug ('Update Status Failed ..');
UPDATE csi_txn_errors
SET error_text = l_trx_error_rec.error_text ,
source_group_ref_id = p_conc_request_id,
last_update_date = sysdate
WHERE transaction_error_id = l_txn_error_id ;
SELECT sysdate INTO l_sysdate FROM DUAL ;
UPDATE csi_txn_errors
SET error_text = l_trx_error_rec.error_text ,
source_group_ref_id = p_conc_request_id,
last_update_date = l_sysdate
WHERE transaction_error_id = l_txn_error_id ;
SELECT creation_date
INTO l_time_stamp
FROM csi_item_instances_h
WHERE transaction_id = l_transaction_id
AND instance_id = p_instance_id;
/* SELECT max(transaction_id)
INTO l_transaction_id
FROM csi_item_instances_h
WHERE instance_id = p_instance_id
AND transaction_id < l_transaction_id;*/
SELECT transaction_id
INTO l_transaction_id
FROM (SELECT ciih.transaction_id
FROM csi_item_instances_h ciih,
csi_transactions ct
WHERE ciih.transaction_id = ct.transaction_id
AND ciih.instance_id = p_instance_id
AND ct.transaction_date < (SELECT transaction_date
FROM csi_transactions
WHERE transaction_id = l_transaction_id)
ORDER BY ct.transaction_date DESC)
WHERE ROWNUM = 1;
SELECT creation_date
INTO l_time_stamp
FROM csi_item_instances_h
WHERE transaction_id = l_transaction_id
AND instance_id = p_instance_id;
SELECT ct.transaction_type_id,
ct.transaction_id,
ct.transaction_date,
ct.source_transaction_date,
ct.inv_material_transaction_id,
ct.object_version_number,
ctt.source_transaction_type
FROM csi_transactions ct,
csi_txn_types ctt
WHERE ct.transaction_id = p_transaction_id
AND ctt.transaction_type_id = ct.transaction_type_id;
SELECT mmt.inventory_item_id,
mmt.organization_id,
mmt.primary_quantity,
msi.serial_number_control_code,
msi.primary_unit_of_measure
FROM mtl_material_transactions mmt,
mtl_system_items msi
WHERE mmt.transaction_id = p_mtl_txn_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND msi.organization_id = mmt.organization_id;
SELECT ciih.instance_id,
cii.inventory_item_id,
cii.last_vld_organization_id,
msi.serial_number_control_code,
msi.primary_unit_of_measure
FROM csi_item_instances_h ciih,
csi_item_instances cii,
mtl_system_items msi
WHERE ciih.transaction_id = p_transaction_id
AND cii.instance_id = ciih.instance_id
AND msi.inventory_item_id = cii.inventory_item_id
AND msi.organization_id = cii.last_vld_organization_id;
SELECT cii.instance_id,
cii.serial_number,
cii.instance_usage_code,
nvl(ciih.old_quantity,0) old_quantity,
nvl(ciih.new_quantity, 0) new_quantity
FROM csi_item_instances_h ciih,
csi_item_instances cii
WHERE ciih.transaction_id = p_transaction_id
AND cii.instance_id = ciih.instance_id
AND cii.inventory_item_id = p_item_id;
SELECT cii.instance_id,
cii.serial_number,
cii.instance_usage_code,
cit.transaction_id,
cit.transaction_type_id
FROM csi_item_instances_h ciih,
csi_item_instances cii,
csi_transactions cit,
csi_i_assets cia
WHERE cit.transaction_id <= p_transaction_id
AND cii.inventory_item_id = p_item_id
AND cii.instance_id = ciih.instance_id
AND ciih.transaction_id = cit.transaction_id
AND cia.instance_id = cii.instance_id
AND cia.asset_quantity >= p_txn_quantity
AND cia.active_end_date IS NULL
ORDER BY cit.transaction_id desc;
SELECT cia.instance_id,
cia.fa_asset_id,
cia.asset_quantity
FROM csi_i_assets cia
WHERE cia.instance_id = p_instance_id
AND cia.asset_quantity > 0
AND cia.active_end_date IS NULL ;
SELECT a.instance_id , a.transaction_id
INTO l_instance_id, l_transaction_id
FROM csi_item_instances_h a,
( SELECT b.transaction_id, b.instance_id
FROM csi_inst_txn_details_v b
WHERE b.transaction_id > l_transaction_id
AND b.instance_id = l_instance_id
AND b.transaction_type_id = 109
AND ROWNUM = 1
ORDER BY b.transaction_id ) c
WHERE a.transaction_id = c.transaction_id
AND a.instance_id <> c.instance_id
AND ROWNUM =1 ;
PROCEDURE update_fa (
p_transaction_id IN number,
p_src_move_trans_tbl IN move_trans_tbl,
p_dest_move_trans_tbl IN move_trans_tbl,
x_return_status OUT nocopy varchar2,
x_error_msg OUT nocopy varchar2)
IS
l_fa_rec fa_rec ;
debug('Inside update_fa');
SELECT asset_category_id
INTO l_dest_fa_category_id
FROM mtl_system_items
WHERE inventory_item_id = p_dest_move_trans_tbl(d_ind).inv_item_id
AND organization_id = p_dest_move_trans_tbl(d_ind).inv_organization_id;
l_trans_rec.who_info.last_update_date := l_sysdate ;
l_trans_rec.who_info.creation_date := l_trans_rec.who_info.last_update_date ;
l_trans_rec.who_info.created_by := l_trans_rec.who_info.last_updated_by ;
END update_fa ;
SELECT object_version_number
INTO l_txn_rec.object_version_number
FROM csi_transactions
WHERE transaction_id = l_txn_rec.transaction_id;
csi_transactions_pvt.update_transactions(
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_transaction_rec => l_txn_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT object_version_number
FROM csi_transactions
WHERE transaction_id = c_transaction_id ;
update_fa(
p_transaction_id => p_transaction_id,
p_src_move_trans_tbl => l_src_move_trans_tbl,
p_dest_move_trans_tbl => l_dest_move_trans_tbl,
x_return_status => l_return_status,
x_error_msg => l_error_msg) ;
SELECT citdv.transaction_id transaction_id
,citdv.transaction_type_id transaction_type_id
,citdv.instance_id instance_id
,DECODE(citdv.serial_number, NULL, (NVL(ciih.old_quantity,0) -
NVL(ciih.new_quantity,0)), 1) primary_units
,citdv.serial_number serial_number
,citdv.inv_material_transaction_id
,citdv.source_transaction_type
,citdv.object_version_number
FROM csi_inst_txn_details_v citdv,
csi_item_instances_h ciih
WHERE citdv.transaction_id = ciih.transaction_id
AND citdv.instance_id = ciih.instance_id
AND citdv.transaction_status_code = cse_datastructures_pub.G_PENDING
AND NVL(ciih.old_quantity,0) > NVL(ciih.new_quantity,0)
AND citdv.transaction_id = p_transaction_id
AND citdv.serial_number is NULL
--ORDER BY 1 ;
SELECT citdv.transaction_id transaction_id
,citdv.transaction_type_id transaction_type_id
,citdv.instance_id instance_id
,1 primary_units
,citdv.serial_number serial_number
,citdv.inv_material_transaction_id
,citdv.source_transaction_type
,citdv.object_version_number
FROM csi_inst_txn_details_v citdv
WHERE citdv.transaction_status_code = cse_datastructures_pub.G_PENDING
AND citdv.transaction_id = p_transaction_id
AND citdv.serial_number is NOT NULL
--ORDER BY 1 ;
SELECT citdv.transaction_id transaction_id
,citdv.transaction_type_id transaction_type_id
,citdv.instance_id instance_id
,DECODE(citdv.serial_number, NULL, (NVL(ciih.new_quantity,0) -
NVL(ciih.old_quantity,0)), 1) primary_units
,citdv.serial_number serial_number
,citdv.object_version_number
FROM csi_inst_txn_details_v citdv ,
csi_item_instances_h ciih
WHERE citdv.transaction_id = c_src_transaction_id
AND ciih.transaction_id = citdv.transaction_id
AND ciih.instance_id = citdv.instance_id
AND NVL(ciih.old_quantity,0) < NVL(ciih.new_quantity,0)
AND citdv.serial_number IS NULL ;
SELECT instance_asset_id
,fa_location_id
,fa_asset_id
,fa_book_type_code
,asset_quantity
,object_version_number
,fa_sync_flag
FROM csi_i_assets
WHERE update_status IN ('OUT_OF_SERVICE', 'IN_SERVICE')
AND instance_id = c_instance_id
AND asset_quantity > 0
ORDER BY fa_asset_id ;
SELECT SUM(asset_quantity)
FROM csi_i_assets
WHERE update_status = 'IN_SERVICE'
AND instance_id = c_instance_id
AND asset_quantity > 0 ;
SELECT transaction_error_id
FROM csi_txn_errors
WHERE transaction_id = c_transaction_id
AND source_type = 'ASSET_MOVE' ;
SELECT sysdate into l_sysdate from dual ;
l_src_inst_asset_tbl.DELETE ;
l_dest_inst_asset_header_tbl.DELETE ;
l_dest_inst_asset_tbl.DELETE ;
debug ('Update Source Inst Asset');
csi_asset_pvt.update_instance_asset (
p_api_version => 1.0
,p_commit => fnd_api.g_false
,p_init_msg_list => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,p_instance_asset_rec => l_src_inst_asset_rec
,p_txn_rec => l_txn_rec
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_lookup_tbl => l_lookup_tbl
,p_asset_count_rec => l_asset_count_rec
,p_asset_id_tbl => l_asset_id_tbl
,p_asset_loc_tbl => l_asset_loc_tbl );
debug ('After Update Source Inst Asset');
l_dest_inst_asset_rec.update_status := cse_datastructures_pub.G_IN_SERVICE ;
l_dest_inst_asset_query_rec.update_status := cse_datastructures_pub.G_IN_SERVICE ;
csi_asset_pvt.update_instance_asset (
p_api_version => 1.0
,p_commit => fnd_api.g_false
,p_init_msg_list => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,p_instance_asset_rec => l_dest_inst_asset_rec
,p_txn_rec => l_txn_rec
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_lookup_tbl => l_lookup_tbl
,p_asset_count_rec => l_asset_count_rec
,p_asset_id_tbl => l_asset_id_tbl
,p_asset_loc_tbl => l_asset_loc_tbl );
l_dest_inst_asset_rec.update_status := cse_datastructures_pub.G_IN_SERVICE ;
csi_transactions_pvt.update_transactions(
p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,p_commit => l_commit
,p_validation_level => l_validation_level
,p_transaction_rec => l_txn_rec
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
csi_transactions_pvt.update_transactions(
p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,p_commit => l_commit
,p_validation_level => l_validation_level
,p_transaction_rec => l_txn_rec
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
UPDATE csi_txn_errors
SET error_text = l_trx_error_rec.error_text ,
source_group_ref_id = p_conc_request_id,
last_update_date = l_sysdate
WHERE transaction_error_id = l_txn_error_id ;
UPDATE csi_txn_errors
SET error_text = l_trx_error_rec.error_text ,
source_group_ref_id = p_conc_request_id,
last_update_date = l_sysdate
WHERE transaction_error_id = l_txn_error_id ;
csi_transactions_pvt.update_transactions(
p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,p_commit => l_commit
,p_validation_level => l_validation_level
,p_transaction_rec => l_txn_rec
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
SELECT DECODE (msib.serial_number_control_code,1,'NON-SERIAL',
2, 'SERIAL', 5 ,'SERIAL',6, 'NON-SERIAL','NON-SERIAL') serial_control_code
FROM mtl_material_transactions mmt
,mtl_system_items_b msib
WHERE mmt.transaction_id = p_mtl_transaction_id
AND mmt.inventory_item_id = msib.inventory_item_id
AND mmt.organization_id = msib.organization_id ;
SELECT DECODE (msib.serial_number_control_code,1,'NON-SERIAL',
2, 'SERIAL', 5 ,'SERIAL',6, 'NON-SERIAL','NON-SERIAL') serial_control_code
FROM mtl_material_transactions mmt
,mtl_system_items_b msib
WHERE mmt.transaction_id = p_mtl_transaction_id
AND mmt.inventory_item_id = msib.inventory_item_id
AND mmt.transfer_organization_id = msib.organization_id ;
SELECT ct.transaction_id transaction_id,
ct.transaction_type_id transaction_type_id,
ciih.instance_id instance_id,
DECODE(cii.serial_number, NULL, (NVL(ciih.old_quantity,0)-NVL(ciih.new_quantity,0)), 1) primary_units,
ct.transaction_quantity,
cii.serial_number serial_number,
ct.inv_material_transaction_id,
cii.object_version_number,
cii.inv_subinventory_name,
cii.location_id,
'INVENTORY' location_type_code,
ct.transaction_date,
cii.inventory_revision,
cii.instance_usage_code
FROM csi_transactions ct,
csi_item_instances_h ciih,
csi_item_instances cii
WHERE ct.transaction_id = p_transaction_id
AND ciih.transaction_id = ct.transaction_id
AND cii.instance_id = ciih.instance_id
AND NVL(ciih.old_quantity,0) > NVL(ciih.new_quantity,0)
AND cii.serial_number is NULL
AND EXISTS (
SELECT 'x'
FROM csi_transactions ct1,
mtl_material_transactions mmt
WHERE ct1.transaction_type_id in (131, 142, 143, 144)
AND ct1.transaction_status_code = 'PENDING'
AND mmt.transaction_id = ct1.inv_material_transaction_id
AND mmt.inventory_item_id = mmt.inventory_item_id
AND mmt.shipment_number = mmt.shipment_number
AND mmt.transaction_id <> p_material_transaction_id);
SELECT citdv.transaction_id transaction_id
,citdv.transaction_type_id transaction_type_id
,citdv.instance_id instance_id
,DECODE(citdv.serial_number, NULL, (NVL(ciih.old_quantity,0) -
NVL(ciih.new_quantity,0)), 1) primary_units
,citdv.serial_number serial_number
,citdv.object_version_number
,ciih.new_inv_organization_id inv_organization_id
,ciih.new_inv_subinventory_name inv_subinventory_name
,citdv.location_id
,'INVENTORY' location_type_code
,citdv.transaction_date
,citdv.instance_usage_code
,citdv.inventory_item_id
,citdv.transaction_quantity
,citdv.source_transaction_type
FROM csi_inst_txn_details_v citdv,
mtl_material_transactions mmt,
csi_item_instances_h ciih
WHERE mmt.inventory_item_id = c_inv_item_id
AND mmt.organization_id = c_inv_org_id
AND mmt.shipment_number = c_shipment_number
AND citdv.transaction_id = ciih.transaction_id
AND citdv.instance_id = ciih.instance_id
AND citdv.inv_material_transaction_id = mmt.transaction_id
AND citdv.transaction_status_code = 'PENDING'
AND citdv.inventory_item_id = citdv.inventory_item_id
AND citdv.serial_number is NOT NULL
AND citdv.source_transaction_type IN (
'INTERORG_TRANS_RECEIPT',
'ISO_REQUISITION_RECEIPT',
'INTERORG_DIRECT_SHIP',
'ISO_DIRECT_SHIP') ;
SELECT ct.transaction_id transaction_id,
ct.transaction_type_id transaction_type_id,
cii.instance_id instance_id,
DECODE(cii.serial_number, NULL, (NVL(ciih.old_quantity,0) - NVL(ciih.new_quantity,0)), 1) primary_units,
cii.serial_number serial_number,
ct.inv_material_transaction_id,
cii.object_version_number,
ciih.old_inv_organization_id inv_organization_id,
ciih.old_inv_subinventory_name inv_subinventory_name,
cii.location_id,
'INVENTORY' location_type_code,
ct.transaction_date,
cii.instance_usage_code,
ct.transaction_quantity
FROM csi_transactions ct,
csi_item_instances_h ciih ,
csi_item_instances cii
WHERE ct.transaction_id = p_transaction_id
AND ciih.transaction_id = ct.transaction_id
AND cii.instance_id = ciih.instance_id
AND cii.serial_number is NOT NULL
AND EXISTS (
SELECT 'x'
FROM csi_transactions ct1,
mtl_material_transactions mmt
WHERE ct1.transaction_type_id in (131, 142, 143, 144)
AND ct1.transaction_status_code = 'PENDING'
AND mmt.transaction_id = ct1.inv_material_transaction_id
AND mmt.inventory_item_id = mmt.inventory_item_id
AND mmt.shipment_number = mmt.shipment_number
AND mmt.transaction_id <> p_material_transaction_id);
SELECT citdv.transaction_id transaction_id
,citdv.transaction_type_id transaction_type_id
,citdv.instance_id instance_id
,DECODE(citdv.serial_number, NULL, (NVL(ciih.new_quantity,0) -
NVL(ciih.old_quantity,0)), 1) primary_units
,citdv.serial_number serial_number
,citdv.object_version_number
,citdv.inv_organization_id inv_organization_id
,citdv.inv_subinventory_name inv_subinventory_name
,citdv.location_id
,'INVENTORY' location_type_code
,citdv.transaction_date
,citdv.instance_usage_code
,citdv.transaction_quantity
,citdv.source_transaction_type
,citdv.inventory_item_id
FROM csi_inst_txn_details_v citdv,
csi_item_instances_h ciih,
mtl_material_transactions mmt
WHERE mmt.inventory_item_id = c_inv_item_id
AND citdv.inv_material_transaction_id = mmt.transaction_id
AND mmt.organization_id = c_inv_org_id
AND mmt.shipment_number = c_shipment_number
AND citdv.transaction_status_code = 'PENDING'
AND citdv.transaction_id = ciih.transaction_id
AND citdv.instance_id = ciih.instance_id
AND citdv.inventory_item_id = citdv.inventory_item_id
AND citdv.serial_number is NULL
AND citdv.location_type_code = 'INVENTORY'
AND citdv.source_transaction_type IN (
'INTERORG_TRANS_RECEIPT',
'ISO_REQUISITION_RECEIPT',
'INTERORG_DIRECT_SHIP',
'ISO_DIRECT_SHIP') ;
SELECT transaction_error_id
FROM csi_txn_errors
WHERE transaction_id = c_transaction_id
AND source_type = 'ASSET_MOVE' ;
SELECT sysdate INTO l_sysdate FROM DUAL ;
SELECT inventory_item_id,
shipment_number,
transfer_organization_id
INTO l_inventory_item_id,
l_shipment_number,
l_xfer_organization_id
FROM mtl_material_transactions
WHERE transaction_id = p_material_transaction_id;
SELECT source_transaction_type
INTO l_src_transaction_type
FROM csi_txn_types
WHERE transaction_type_id = p_transaction_type_id;
update_fa(
p_transaction_id => p_transaction_id,
p_src_move_trans_tbl => l_src_move_trans_tbl,
p_dest_move_trans_tbl => l_dest_move_trans_tbl,
x_return_status => l_return_status,
x_error_msg => l_error_msg) ;
debug ('Update Status Failed ..');
update_txn_status (
p_src_move_trans_tbl => l_src_move_trans_tbl,
p_dest_move_trans_tbl => l_dest_move_trans_tbl,
p_conc_request_id => p_conc_request_id,
x_return_status => l_return_status,
x_error_msg => l_error_msg);
debug ('Update Status Failed ..');
update_fa(
p_transaction_id => p_transaction_id,
p_src_move_trans_tbl => l_src_move_trans_tbl,
p_dest_move_trans_tbl => l_dest_move_trans_tbl,
x_return_status => l_return_status,
x_error_msg => l_error_msg) ;
debug ('Update Status Failed ..');
update_txn_status (
p_src_move_trans_tbl => l_src_move_trans_tbl,
p_dest_move_trans_tbl => l_dest_move_trans_tbl,
p_conc_request_id => p_conc_request_id,
x_return_status => l_return_status,
x_error_msg => l_error_msg);
debug ('Update Status Failed ..');
UPDATE csi_txn_errors
SET error_text = l_trx_error_rec.error_text ,
source_group_ref_id = p_conc_request_id,
last_update_date = l_sysdate
WHERE transaction_error_id = l_txn_error_id ;
UPDATE csi_txn_errors
SET error_text = l_trx_error_rec.error_text ,
source_group_ref_id = p_conc_request_id,
last_update_date = l_sysdate
WHERE transaction_error_id = l_txn_error_id ;
SELECT transaction_type_id,
transaction_source_type_id,
transaction_action_id ,
trx_source_line_id,
transaction_source_id,
primary_quantity,
transaction_date,
inventory_item_id,
organization_id,
transfer_transaction_id
INTO l_mtl_type_id,
l_mtl_src_type_id,
l_mtl_action_id,
l_mtl_src_line_id,
l_mtl_txn_src_id,
l_mtl_primary_qty,
l_mtl_txn_date,
l_inventory_item_id,
l_organization_id,
l_mtl_xfer_txn_id
FROM mtl_material_transactions
WHERE transaction_id = p_csi_txn_rec.inv_material_transaction_id;
SELECT cii.inventory_item_id,
cii.last_vld_organization_id
INTO l_inventory_item_id,
l_organization_id
FROM csi_item_instances cii,
csi_item_instances_h ciih
WHERE ciih.transaction_id = p_csi_txn_rec.transaction_id
AND cii.instance_id = ciih.instance_id
AND rownum = 1;
SELECT serial_number_control_code,
primary_uom_code,
asset_creation_code,
description,
concatenated_segments
INTO l_serial_code,
l_primary_uom_code,
l_asset_creation_code,
l_item_description,
l_item
FROM mtl_system_items_kfv
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id;
SELECT 'Y'
INTO l_asset_exists
FROM csi_item_instances_h CIIH,
csi_item_instances CII,
csi_i_assets cia
WHERE CIIH.transaction_id = p_csi_txn_rec.transaction_id
AND CIIH.instance_id = CII.instance_id
AND CII.instance_id = CIA.instance_id
AND CII.inventory_item_id = l_inventory_item_id
AND CIA.active_end_date IS NULL OR CIA.active_end_date > SYSDATE;
SELECT 'Y'
INTO l_asset_exists
FROM csi_item_instances_h CIIH,
csi_item_instances CII,
csi_i_assets CIA
WHERE CIIH.transaction_id = p_csi_txn_rec.transaction_id
AND CII.instance_id = CIIH.instance_id
AND CII.inventory_item_id = l_inventory_item_id
AND nvl(CIIH.new_quantity, 0) - nvl(CIIH.old_quantity,0) < 0
AND CII.instance_id = CIA.instance_id
AND CIA.active_end_date IS NULL OR CIA.active_end_date > SYSDATE;
SELECT source_line_ref_id
INTO l_line_id
FROM csi_transactions
WHERE transaction_id = p_csi_txn_rec.transaction_id;
SELECT 'Y'
INTO l_ship_only
FROM oe_order_lines_all
WHERE Nvl(shipped_quantity,0) >0
AND Nvl(invoiced_quantity,0) = 0
AND (invoice_interface_status_code IS NULL OR invoice_interface_status_code = 'NOT_ELIGIBLE' )
AND line_id = l_line_id;
SELECT ctld.sub_type_id
INTO l_sub_type_id
FROM csi_t_txn_line_details ctld,csi_t_transaction_lines ctl
WHERE ctld.transaction_line_id = ctl.transaction_line_id
AND ctl. source_transaction_id = l_line_id
AND ROWNUM=1;
SELECT nvl(citt.sub_type_id, -1)
INTO l_sub_type_id
FROM csi_ib_txn_types citt,
csi_source_ib_types csit
WHERE csit.transaction_type_id = 51
AND csit.default_flag = 'Y'
and citt.sub_type_id = csit.sub_type_id;
SELECT nvl(src_change_owner, 'N')
INTO l_change_owner
FROM csi_ib_txn_types citt,
csi_t_txn_line_details ctld,
csi_t_transaction_lines ctl
WHERE ctl.source_transaction_type_id = 51
AND ctld.transaction_line_id = ctl.transaction_line_id
AND ctld.source_transaction_flag = 'Y'
AND ctld.csi_transaction_id = p_csi_txn_rec.transaction_id
AND citt.sub_type_id = ctld.sub_type_id
AND rownum = 1;
SELECT nvl(src_change_owner, 'N')
INTO l_change_owner
FROM csi_ib_txn_types citt,
csi_source_ib_types csit
WHERE csit.transaction_type_id = 51
AND csit.default_flag = 'Y'
and citt.sub_type_id = csit.sub_type_id;
SELECT nvl(src_change_owner, 'N')
INTO l_change_owner
FROM csi_ib_txn_types
WHERE sub_type_id = p_csi_txn_rec.txn_sub_type_id;
SELECT nvl(src_change_owner, 'N')
INTO l_change_owner
FROM csi_ib_txn_types citt,
csi_source_ib_types csit
WHERE csit.transaction_type_id = 53
AND csit.default_flag = 'Y'
AND citt.sub_type_id = csit.sub_type_id;
ELSIF p_csi_txn_rec.transaction_type_id = 3 AND p_csi_txn_rec.source_group_ref = 'MOVE' --MAss update move batch added for bug 9738305
THEN
l_action := 'MOVE';
SELECT nvl(src_change_owner, 'N')
INTO l_change_owner
FROM csi_ib_txn_types citt,
csi_t_txn_line_details ctld,
csi_t_transaction_lines ctl
WHERE ctl.source_transaction_type_id = 51
AND ctld.transaction_line_id = ctl.transaction_line_id
AND ctld.source_transaction_flag = 'Y'
AND ctld.csi_transaction_id = p_csi_txn_rec.transaction_id
AND citt.sub_type_id = ctld.sub_type_id
AND rownum = 1;
SELECT nvl(src_change_owner, 'N')
INTO l_change_owner
FROM csi_ib_txn_types citt,
csi_source_ib_types csit
WHERE csit.transaction_type_id = 51
AND csit.default_flag = 'Y'
and citt.sub_type_id = csit.sub_type_id;
SELECT cii.instance_id,
cii.lot_number,
cii.serial_number,
nvl(ciih.old_quantity, 0) old_quantity,
nvl(ciih.new_quantity, 0) new_quantity,
ciih.old_location_type_code,
ciih.old_location_id,
ciih.new_location_type_code,
ciih.new_location_id
FROM csi_item_instances_h ciih,
csi_item_instances cii
WHERE ciih.transaction_id = p_csi_txn_id
AND cii.instance_id = ciih.instance_id
AND cii.inventory_item_id = p_inventory_item_id;
SELECT instance_asset_id
FROM csi_i_assets
WHERE instance_id = p_inst_id
AND asset_quantity > 0
AND fa_sync_flag = 'Y';
SELECT cia.instance_asset_id
FROM csi_i_assets cia,
fa_mass_additions fma
WHERE cia.instance_id = p_inst_id
AND cia.asset_quantity > 0
AND cia.fa_asset_id is null
AND fma.mass_addition_id = cia.fa_mass_addition_id
AND fma.queue_name = 'POST'
AND fma.posting_status = 'POST';
SELECT ct.transaction_id
FROM csi_transactions ct,
csi_item_instances cii,
csi_item_instances_h ciih
WHERE ciih.instance_id = p_instance_id
AND ciih.transaction_id < p_csi_txn_id
AND cii.instance_id = ciih.instance_id
AND cii.inventory_item_id = p_inv_item_id
AND ct.transaction_id = ciih.transaction_id
AND ct.transaction_status_code = 'PENDING';
SELECT transaction_error_id
INTO l_error_id
FROM csi_txn_errors
WHERE source_type = 'CSEFAMOV'
AND source_id = l_error_rec.source_id
AND rownum < 2;
UPDATE csi_txn_errors
SET error_text = l_error_rec.error_text,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
last_update_date = sysdate
WHERE transaction_error_id = l_error_id;
debug(' error updated. transaction_error_id : '||l_error_id);
SELECT ct.*
FROM csi_transactions ct
WHERE ct.transaction_type_id IN (
1, -- IB_UI
3, -- MASS_EDIT
5, -- EXPIRE_STATUS
6, -- OPEN_INTERFACE
51, -- OM_SHIPMENT
53, -- RMA_RECEIPT
55, -- FIELD_SERVICE_REPORT
71, -- WIP_ISSUE
72, -- WIP_RECEIPT
73, -- WIP_ASSY_COMPLETION
74, -- WIP_ASSY_RETURN
75, -- WIP_BYPRODUCT_COMPLETION
76, -- WIP_BYPRODUCT_RETURN
91, -- EAM_ASSET_CREATION
105, -- PO_RECEIPT_INTO_PROJECT
106, -- PROJECT_ITEM_INSTALLED
107, -- PROJECT_ITEM_UNINSTALLED
108, -- PROJECT_ITEM_IN_SERVICE
109, -- IN_SERVICE
110, -- OUT_OF_SERVICE
111, -- ITEM_MOVE
112, -- PO_RECEIPT_INTO_INVENTORY
113, -- MOVE_ORDER_ISSUE_TO_PROJECT
114, -- SUBINVENTORY_TRANSFER
115, -- INTERORG_TRANSFER
116, -- MISC_ISSUE
117, -- MISC_RECEIPT
118, -- PHYSICAL_INVENTORY
119, -- CYCLE_COUNT
120, -- MISC_RECEIPT_FROM_PROJECT
121, -- MISC_ISSUE_TO_PROJECT
122, -- INTERNAL_SALES_ORDER
124, -- ACCT_ISSUE
125, -- ACCT_ALIAS_ISSUE
126, -- ISO_ISSUE
127, -- RETURN_TO_VENDOR
128, -- ACCT_RECEIPT
129, -- ACCT_ALIAS_RECEIPT
130, -- ISO_SHIPMENT
131, -- ISO_REQUISITION_RECEIPT
132, -- ISSUE_TO_HZ_LOC
133, -- MISC_ISSUE_HZ_LOC
134, -- MISC_RECEIPT_HZ_LOC
135, -- ISO_ISSUE
136, -- MOVE_ORDER_ISSUE
137, -- MOVE_ORDER_TRANSFER
138, -- ISO_TRANSFER
139, -- CYCLE_COUNT_TRANSFER
140, -- PHYSICAL_INV_TRANSFER
141, -- BACKFLUSH_TRANSFER
142, -- ISO_DIRECT_SHIP
143, -- INTERORG_DIRECT_SHIP
144, -- INTERORG_TRANS_RECEIPT
145, -- INTERORG_TRANS_SHIPMENT
146, -- SALES_ORDER_PICK
147, -- ISO_PICK
148, -- PO_RCPT_ADJUSTMENT
149, -- INT_REQ_RCPT_ADJUSTMENT
150, -- SHIPMENT_RCPT_ADJUSTMENT
151, -- PROJECT_BORROW
152, -- PROJECT_TRANSFER
153, -- PROJECT_PAYBACK
326) -- PROJECT_CONTRACT_SHIPMENT
AND ct.transaction_status_code = 'PENDING'
AND EXISTS (
SELECT 1
FROM csi_item_instances_h ciih,
csi_item_instances cii
WHERE ciih.transaction_id = ct.transaction_id
AND cii.instance_id = ciih.instance_id
AND cii.inventory_item_id = nvl(p_inventory_item_id, cii.inventory_item_id))
ORDER BY ct.creation_date;