[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT creation_date,
INSTANCE_ASSET_ID,
NEW_INSTANCE_ID,
NEW_FA_ASSET_ID,
NEW_ASSET_QUANTITY,
NEW_FA_BOOK_TYPE_CODE,
NEW_FA_LOCATION_ID,
NEW_UPDATE_STATUS,
NEW_ACTIVE_START_DATE,
NEW_ACTIVE_END_DATE,
FULL_DUMP_FLAG,
OBJECT_VERSION_NUMBER
FROM CSI_I_ASSETS_H
WHERE instance_asset_id = p_inst_ass_id
ORDER by creation_date;
x_instance_asset_rec.UPDATE_STATUS := C1.NEW_UPDATE_STATUS;
SELECT
CREATION_DATE ,
INSTANCE_ASSET_ID ,
NEW_INSTANCE_ID ,
NEW_FA_ASSET_ID ,
NEW_ASSET_QUANTITY ,
NEW_FA_BOOK_TYPE_CODE ,
NEW_FA_LOCATION_ID ,
NEW_UPDATE_STATUS ,
NEW_ACTIVE_START_DATE ,
NEW_ACTIVE_END_DATE ,
FULL_DUMP_FLAG ,
OBJECT_VERSION_NUMBER
FROM CSI_I_ASSETS_H
WHERE instance_asset_history_id = p_inst_ass_hist_id
and full_dump_flag = 'Y' ;
x_instance_asset_rec.UPDATE_STATUS := C1.NEW_UPDATE_STATUS;
SELECT decode(cii.location_type_code,'HZ_PARTY_SITES',
(select hzp.location_id
from hz_party_sites hzp
where hzp.party_site_id = cii.location_id),cii.location_id) cii_location_id
FROM csi_item_instances cii
WHERE cii.instance_id = px_instance_asset_rec.instance_id;
SELECT fa_location_id
FROM csi_a_locations
WHERE location_id = l_cii_location_id;
SELECT SUM(fdh.units_assigned)
FROM fa_distribution_history fdh
WHERE fdh.asset_id = px_instance_asset_rec.fa_asset_id
AND fdh.date_ineffective is null
AND fdh.location_id = px_instance_asset_rec.fa_location_id ;
SELECT SUM(cia.asset_quantity)
FROM csi_i_assets cia
WHERE cia.fa_asset_id = px_instance_asset_rec.fa_asset_id
AND cia.fa_location_id = px_instance_asset_rec.fa_location_id
AND cia.asset_quantity > 0
AND sysdate between nvl(cia.active_start_date, sysdate-1) and nvl(cia.active_end_date, sysdate+1)
AND cia.update_status = 'IN_SERVICE'
AND cia.fa_sync_flag = 'Y' ;
SELECT cia.fa_sync_flag
FROM csi_i_assets cia
WHERE cia.fa_asset_id = px_instance_asset_rec.fa_asset_id
AND cia.fa_location_id = px_instance_asset_rec.fa_location_id
AND cia.instance_id = px_instance_asset_rec.instance_id
AND cia.asset_quantity > 0
AND sysdate between nvl(cia.active_start_date, sysdate-1) and nvl(cia.active_end_date, sysdate+1)
AND cia.update_status = 'IN_SERVICE';
select concatenated_segments
into l_fa_location
from FA_LOCATIONS_KFV
where location_id = px_instance_asset_rec.fa_location_id;
select location_code
into l_cii_location
from HR_LOCATIONS_ALL
where location_id = l_cii_location_id;
select address1||','||address2||','||address3||','||address4||','||city||','||state||','||postal_code||','||country
into l_cii_location
from HZ_LOCATIONS
where location_id = l_cii_location_id;
SELECT
MAX(instance_asset_history_id)
FROM CSI_I_ASSETS_H
WHERE creation_date <= p_time
and instance_asset_id = p_asset_id
and full_dump_flag = 'Y';
SELECT
INSTANCE_ASSET_ID ,
TRANSACTION_ID ,
OLD_INSTANCE_ID ,
NEW_INSTANCE_ID ,
OLD_FA_ASSET_ID ,
NEW_FA_ASSET_ID ,
OLD_ASSET_QUANTITY ,
NEW_ASSET_QUANTITY ,
OLD_FA_BOOK_TYPE_CODE ,
NEW_FA_BOOK_TYPE_CODE ,
OLD_FA_LOCATION_ID ,
NEW_FA_LOCATION_ID ,
OLD_UPDATE_STATUS ,
NEW_UPDATE_STATUS ,
FULL_DUMP_FLAG ,
OLD_ACTIVE_START_DATE ,
NEW_ACTIVE_START_DATE ,
OLD_ACTIVE_END_DATE ,
NEW_ACTIVE_END_DATE ,
OBJECT_VERSION_NUMBER
FROM CSI_I_ASSETS_H
WHERE creation_date <= p_time
and creation_date >= p_nearest_full_dump
and instance_asset_id = p_asset_id
ORDER BY creation_date;
l_instance_asset_tbl.DELETE(i);
IF (C2.OLD_UPDATE_STATUS IS NULL AND C2.NEW_UPDATE_STATUS IS NOT NULL)
OR (C2.OLD_UPDATE_STATUS IS NOT NULL AND C2.NEW_UPDATE_STATUS IS NULL)
OR (C2.OLD_UPDATE_STATUS <> C2.NEW_UPDATE_STATUS) THEN
l_instance_asset_tbl(i).UPDATE_STATUS := C2.NEW_UPDATE_STATUS;
x_instance_asset_tbl.DELETE;
SELECT b.asset_number
,b.serial_number
,b.tag_number
,d.concatenated_segments category
,e.date_placed_in_service
,b.description
,f.name
,g.concatenated_segments
INTO p_asset_header_tbl(tab_row).asset_number
,p_asset_header_tbl(tab_row).serial_number
,p_asset_header_tbl(tab_row).tag_number
,p_asset_header_tbl(tab_row).category
,p_asset_header_tbl(tab_row).date_placed_in_service
,p_asset_header_tbl(tab_row).description
,p_asset_header_tbl(tab_row).employee_name
,p_asset_header_tbl(tab_row).expense_account_number
FROM fa_additions_vl b
,fa_distribution_history c
,fa_categories_b_kfv d
,fa_books e
,fa_employees f
,gl_code_combinations_kfv g
WHERE b.asset_id = c.asset_id
AND b.asset_category_id = d.category_id
AND b.asset_id = e.asset_id
AND c.book_type_code = e.book_type_code
AND c.assigned_to = f.employee_id(+)
AND c.code_combination_id = g.code_combination_id
--AND c.date_ineffective IS NULL -- Commented for bug 4206038
-- Added for Bug: 3903805
--AND e.date_ineffective IS NULL -- Commented for bug 4206038
AND e.book_type_code = p_asset_header_tbl(tab_row).fa_book_type_code
AND c.location_id = p_asset_header_tbl(tab_row).fa_location_id
-- End of addition for Bug: 3903805
AND b.asset_id = p_asset_header_tbl(tab_row).fa_asset_id
AND rownum < 2; -- Added for Bug: 3903805
SELECT b.asset_number
,b.serial_number
,b.tag_number
,d.concatenated_segments category
,e.date_placed_in_service
,b.description
,c.code_combination_id
,c.assigned_to
INTO p_asset_header_tbl(tab_row).asset_number
,p_asset_header_tbl(tab_row).serial_number
,p_asset_header_tbl(tab_row).tag_number
,p_asset_header_tbl(tab_row).category
,p_asset_header_tbl(tab_row).date_placed_in_service
,p_asset_header_tbl(tab_row).description
,l_code_combination_id
,l_assigned_to
FROM fa_additions_vl b
,fa_distribution_history c
,fa_categories_b_kfv d
,fa_books e
WHERE b.asset_id = c.asset_id
AND b.asset_category_id = d.category_id
AND b.asset_id = e.asset_id
AND c.book_type_code = e.book_type_code
AND e.book_type_code = p_asset_header_tbl(tab_row).fa_book_type_code
AND c.location_id = p_asset_header_tbl(tab_row).fa_location_id
AND b.asset_id = p_asset_header_tbl(tab_row).fa_asset_id
AND rownum < 2;
SELECT concatenated_segments
INTO p_asset_header_tbl(tab_row).expense_account_number
FROM gl_code_combinations_kfv
WHERE code_combination_id = l_code_combination_id;
SELECT name
INTO p_asset_header_tbl(tab_row).employee_name
FROM fa_employees
WHERE employee_id=l_assigned_to
AND rownum<2;
SELECT segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7
INTO p_asset_header_tbl(tab_row).fa_location_segment1,
p_asset_header_tbl(tab_row).fa_location_segment2,
p_asset_header_tbl(tab_row).fa_location_segment3,
p_asset_header_tbl(tab_row).fa_location_segment4,
p_asset_header_tbl(tab_row).fa_location_segment5,
p_asset_header_tbl(tab_row).fa_location_segment6,
p_asset_header_tbl(tab_row).fa_location_segment7
FROM fa_locations
WHERE location_id = p_asset_header_tbl(tab_row).fa_location_id;
dbms_sql.column_value(p_get_asset_cursor_id, 7, x_inst_asset_rec.update_status);
dbms_sql.define_column(p_get_asset_cursor_id, 7, l_inst_asset_rec.update_status,30);
IF( (p_inst_asset_query_rec.update_status IS NOT NULL)
AND (p_inst_asset_query_rec.update_status <> FND_API.G_MISS_CHAR)) THEN
DBMS_SQL.BIND_VARIABLE(p_get_asset_cursor_id, ':update_status', p_inst_asset_query_rec.update_status);
IF ((p_inst_asset_query_rec.update_status IS NOT NULL) AND
(p_inst_asset_query_rec.update_status <> FND_API.G_MISS_CHAR)) THEN
IF x_where_clause IS NULL THEN
x_where_clause := ' update_status = :update_status ';
' update_status = :update_status ';
ELSIF (p_inst_asset_query_rec.update_status IS NULL) THEN
IF x_where_clause IS NULL THEN
x_where_clause := ' update_status IS NULL ';
' update_status IS NULL ';
l_select_stmt VARCHAR2(20000):= ' SELECT INSTANCE_ASSET_ID,INSTANCE_ID,FA_ASSET_ID,FA_BOOK_TYPE_CODE '||
' ,FA_LOCATION_ID,ASSET_QUANTITY,UPDATE_STATUS,ACTIVE_START_DATE,ACTIVE_END_DATE , '||
' OBJECT_VERSION_NUMBER FROM CSI_I_ASSETS ';
AND (p_instance_asset_query_rec.update_status = FND_API.G_MISS_CHAR) THEN
FND_MESSAGE.Set_Name('CSI', 'CSI_API_INVALID_PARAMETERS');
l_select_stmt := l_select_stmt || ' where '||l_where_clause;
dbms_sql.parse(l_get_inst_asset_cursor_id, l_select_stmt , dbms_sql.native);
/* Description : procedure used to update an Item */
/* Instance */
/*-------------------------------------------------------*/
PROCEDURE create_instance_asset (
p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
p_instance_asset_rec IN OUT NOCOPY csi_datastructures_pub.instance_asset_rec,
p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_lookup_tbl IN OUT NOCOPY csi_asset_pvt.lookup_tbl,
p_asset_count_rec IN OUT NOCOPY csi_asset_pvt.asset_count_rec,
p_asset_id_tbl IN OUT NOCOPY csi_asset_pvt.asset_id_tbl,
p_asset_loc_tbl IN OUT NOCOPY csi_asset_pvt.asset_loc_tbl,
p_called_from_grp IN VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'CREATE_INSTANCE_ASSET';
SELECT instance_asset_id,
object_version_number
INTO p_instance_asset_rec.instance_asset_id,
p_instance_asset_rec.object_version_number
FROM csi_i_assets
WHERE instance_id = p_instance_asset_rec.instance_id
and fa_asset_id = p_instance_asset_rec.fa_asset_id
and fa_book_type_code = p_instance_asset_rec.fa_book_type_code
AND fa_location_id = p_instance_asset_rec.fa_location_id
AND active_end_date < SYSDATE
AND ROWNUM = 1 ;
update_instance_asset(
p_api_version => p_api_version
,p_commit => fnd_api.g_false
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_instance_asset_rec => p_instance_asset_rec
,p_txn_rec => p_txn_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_lookup_tbl => l_asset_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( ' Failed Pvt:update_instance_asset..');
( p_instance_asset_rec.UPDATE_STATUS,
' p_instance_asset_rec.UPDATE_STATUS',
l_api_name );
IF ((p_instance_asset_rec.update_status IS NOT NULL) AND
(p_instance_asset_rec.update_status <> FND_API.G_MISS_CHAR)) THEN
IF p_lookup_tbl.count > 0 THEN
For lookup_count in p_lookup_tbl.FIRST .. p_lookup_tbl.LAST
LOOP
IF p_lookup_tbl(lookup_count).lookup_code = p_instance_asset_rec.update_status THEN
l_valid_flag := p_lookup_tbl(lookup_count).valid_flag;
FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_UPDATE_STATUS');
FND_MESSAGE.SET_TOKEN('UPDATE_STATUS',p_instance_asset_rec.update_status);
p_instance_asset_rec.update_status;
IF NOT( CSI_Asset_vld_pvt.Is_Update_Status_Exists
(p_instance_asset_rec.UPDATE_STATUS)) THEN
p_lookup_tbl(p_asset_count_rec.lookup_count).valid_flag := 'N';
CSI_I_ASSETS_PKG.Insert_Row (
px_INSTANCE_ASSET_ID => p_instance_asset_rec.INSTANCE_ASSET_ID,
p_INSTANCE_ID => p_instance_asset_rec.INSTANCE_ID,
p_FA_ASSET_ID => p_instance_asset_rec.FA_ASSET_ID,
p_FA_BOOK_TYPE_CODE => p_instance_asset_rec.FA_BOOK_TYPE_CODE,
p_FA_LOCATION_ID => p_instance_asset_rec.FA_LOCATION_ID,
p_ASSET_QUANTITY => p_instance_asset_rec.ASSET_QUANTITY,
p_UPDATE_STATUS => p_instance_asset_rec.UPDATE_STATUS,
p_FA_SYNC_FLAG => p_instance_asset_rec.FA_SYNC_FLAG,
p_FA_MASS_ADDITION_ID => p_instance_asset_rec.FA_MASS_ADDITION_ID,
p_CREATION_COMPLETE_FLAG=> p_instance_asset_rec.CREATION_COMPLETE_FLAG,
p_CREATED_BY => FND_GLOBAL.USER_ID,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
p_OBJECT_VERSION_NUMBER => 1,
p_ACTIVE_START_DATE => p_instance_asset_rec.ACTIVE_START_DATE,
p_ACTIVE_END_DATE => p_instance_asset_rec.ACTIVE_END_DATE);
CSI_I_ASSETS_H_PKG.Insert_Row (
px_INSTANCE_ASSET_HISTORY_ID => l_instance_asset_hist_id,
p_INSTANCE_ASSET_ID => p_instance_asset_rec.INSTANCE_ASSET_ID,
p_TRANSACTION_ID => p_txn_rec.transaction_id,
p_OLD_INSTANCE_ID => NULL,
p_NEW_INSTANCE_ID => p_instance_asset_rec.INSTANCE_ID,
p_OLD_FA_ASSET_ID => NULL,
p_NEW_FA_ASSET_ID => p_instance_asset_rec.FA_ASSET_ID,
p_OLD_ASSET_QUANTITY => NULL,
p_NEW_ASSET_QUANTITY => p_instance_asset_rec.ASSET_QUANTITY,
p_OLD_FA_BOOK_TYPE_CODE => NULL,
p_NEW_FA_BOOK_TYPE_CODE => p_instance_asset_rec.FA_BOOK_TYPE_CODE,
p_OLD_FA_LOCATION_ID => NULL,
p_NEW_FA_LOCATION_ID => p_instance_asset_rec.FA_LOCATION_ID,
p_OLD_UPDATE_STATUS => NULL,
p_NEW_UPDATE_STATUS => p_instance_asset_rec.UPDATE_STATUS,
p_OLD_FA_SYNC_FLAG => NULL,
p_NEW_FA_SYNC_FLAG => p_instance_asset_rec.FA_SYNC_FLAG,
p_OLD_FA_MASS_ADDITION_ID => NULL,
p_NEW_FA_MASS_ADDITION_ID => p_instance_asset_rec.FA_MASS_ADDITION_ID,
p_OLD_CREATION_COMPLETE_FLAG => NULL,
p_NEW_CREATION_COMPLETE_FLAG => p_instance_asset_rec.CREATION_COMPLETE_FLAG,
p_FULL_DUMP_FLAG => 'N',
p_CREATED_BY => FND_GLOBAL.USER_ID,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
p_OBJECT_VERSION_NUMBER => 1,
p_OLD_ACTIVE_START_DATE => NULL,
p_NEW_ACTIVE_START_DATE => p_instance_asset_rec.ACTIVE_START_DATE,
p_OLD_ACTIVE_END_DATE => NULL,
p_NEW_ACTIVE_END_DATE => p_instance_asset_rec.ACTIVE_END_DATE);
csi_item_instance_pvt.get_and_update_acct_class(
p_api_version => p_api_version,
p_commit => p_commit,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_instance_id => p_instance_asset_rec.instance_id,
p_instance_expiry_flag => p_instance_asset_rec.check_for_instance_expiry,
p_txn_rec => p_txn_rec,
x_acct_class_code => l_acct_class_code,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
PROCEDURE update_instance_asset(
p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
p_instance_asset_rec IN OUT NOCOPY csi_datastructures_pub.instance_asset_rec,
p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_lookup_tbl IN OUT NOCOPY csi_asset_pvt.lookup_tbl,
p_asset_count_rec IN OUT NOCOPY csi_asset_pvt.asset_count_rec,
p_asset_id_tbl IN OUT NOCOPY csi_asset_pvt.asset_id_tbl,
p_asset_loc_tbl IN OUT NOCOPY csi_asset_pvt.asset_loc_tbl )
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_instance_asset';
SELECT instance_asset_id,
instance_id,
fa_asset_id,
fa_book_type_code,
fa_location_id,
asset_quantity,
update_status,
fa_sync_flag,
fa_mass_addition_id,
creation_complete_flag,
active_start_date,
active_end_date,
object_version_number
FROM csi_i_assets
WHERE INSTANCE_ASSET_ID = p_inst_asset_id
FOR UPDATE OF object_version_number ;
SELECT *
FROM csi_i_assets_h
WHERE csi_i_assets_h.instance_asset_history_id = p_asset_hist_id
FOR UPDATE NOWAIT;
SAVEPOINT update_instance_asset_pvt;
debug( 'update_instance_asset');
debug( 'update_instance_asset:'||p_api_version||'-'||p_commit||'-'||p_init_msg_list||'-'||p_validation_level);
IF ((p_instance_asset_rec.update_status IS NOT NULL) AND
(p_instance_asset_rec.update_status <> FND_API.G_MISS_CHAR)) THEN
IF p_lookup_tbl.count > 0 THEN
For lookup_count in p_lookup_tbl.FIRST .. p_lookup_tbl.LAST
LOOP
IF p_lookup_tbl(lookup_count).lookup_code = p_instance_asset_rec.update_status THEN
l_valid_flag := p_lookup_tbl(lookup_count).valid_flag;
FND_MESSAGE.SET_NAME('CSI','CSI_API_INV_UPDATE_STATUS');
FND_MESSAGE.SET_TOKEN('UPDATE_STATUS',p_instance_asset_rec.update_status);
p_lookup_tbl(p_asset_count_rec.lookup_count).lookup_code := p_instance_asset_rec.update_status;
IF NOT( CSI_Asset_vld_pvt.Is_Update_Status_Exists
(p_instance_asset_rec.UPDATE_STATUS)) THEN
p_lookup_tbl(p_asset_count_rec.lookup_count).valid_flag := 'N';
CSI_I_ASSETS_PKG.Update_Row (
p_INSTANCE_ASSET_ID => p_instance_asset_rec.instance_asset_id,
p_INSTANCE_ID => p_instance_asset_rec.INSTANCE_ID,
p_FA_ASSET_ID => p_instance_asset_rec.FA_ASSET_ID,
p_FA_BOOK_TYPE_CODE => p_instance_asset_rec.FA_BOOK_TYPE_CODE,
p_FA_LOCATION_ID => p_instance_asset_rec.FA_LOCATION_ID,
p_ASSET_QUANTITY => p_instance_asset_rec.ASSET_QUANTITY,
p_UPDATE_STATUS => p_instance_asset_rec.UPDATE_STATUS,
p_FA_SYNC_FLAG => p_instance_asset_rec.FA_SYNC_FLAG,
p_FA_MASS_ADDITION_ID => p_instance_asset_rec.FA_MASS_ADDITION_ID,
p_CREATION_COMPLETE_FLAG=> l_creation_complete_flag,
p_CREATED_BY => FND_API.G_MISS_NUM,
p_CREATION_DATE => fnd_api.g_miss_date,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
p_OBJECT_VERSION_NUMBER => l_OBJECT_VERSION_NUMBER,
p_ACTIVE_START_DATE => p_instance_asset_rec.ACTIVE_START_DATE,
p_ACTIVE_END_DATE => p_instance_asset_rec.ACTIVE_END_DATE);
ROLLBACK TO update_instance_asset_pvt;
SELECT mod(l_object_version_number,l_full_dump_frequency)
INTO l_mod_value
FROM dual;
l_temp_inst_asset_rec.update_status := p_instance_asset_rec.update_status ;
SELECT instance_asset_history_id
INTO l_asset_hist_id
FROM csi_i_assets_h h
WHERE h.transaction_id = p_txn_rec.transaction_id
AND h.instance_asset_id = p_instance_asset_rec.instance_asset_id;
CSI_I_ASSETS_H_PKG.Update_Row (
p_INSTANCE_ASSET_HISTORY_ID => l_asset_hist_id,
p_INSTANCE_ASSET_ID => fnd_api.g_miss_num,
p_TRANSACTION_ID => fnd_api.g_miss_num,
p_OLD_INSTANCE_ID => fnd_api.g_miss_num,
p_NEW_INSTANCE_ID => l_temp_inst_asset_rec.INSTANCE_ID,
p_OLD_FA_ASSET_ID => fnd_api.g_miss_num,
p_NEW_FA_ASSET_ID => l_temp_inst_asset_rec.FA_ASSET_ID,
p_OLD_ASSET_QUANTITY => fnd_api.g_miss_num,
p_NEW_ASSET_QUANTITY => l_temp_inst_asset_rec.ASSET_QUANTITY,
p_OLD_FA_BOOK_TYPE_CODE => fnd_api.g_miss_char,
p_NEW_FA_BOOK_TYPE_CODE => l_temp_inst_asset_rec.FA_BOOK_TYPE_CODE,
p_OLD_FA_LOCATION_ID => fnd_api.g_miss_num,
p_NEW_FA_LOCATION_ID => l_temp_inst_asset_rec.FA_LOCATION_ID,
p_OLD_UPDATE_STATUS => fnd_api.g_miss_char,
p_NEW_UPDATE_STATUS => l_temp_inst_asset_rec.UPDATE_STATUS,
p_OLD_FA_SYNC_FLAG => fnd_api.g_miss_char,
p_NEW_FA_SYNC_FLAG => l_temp_inst_asset_rec.FA_SYNC_FLAG,
p_OLD_FA_MASS_ADDITION_ID => fnd_api.g_miss_num,
p_NEW_FA_MASS_ADDITION_ID => l_temp_inst_asset_rec.FA_MASS_ADDITION_ID,
p_OLD_CREATION_COMPLETE_FLAG => fnd_api.g_miss_char,
p_NEW_CREATION_COMPLETE_FLAG => l_temp_inst_asset_rec.CREATION_COMPLETE_FLAG,
p_FULL_DUMP_FLAG => fnd_api.g_miss_char,
p_CREATED_BY => fnd_api.g_miss_num,
p_CREATION_DATE => fnd_api.g_miss_date,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
p_OBJECT_VERSION_NUMBER => fnd_api.g_miss_num,
p_OLD_ACTIVE_START_DATE => fnd_api.g_miss_date,
p_NEW_ACTIVE_START_DATE => l_temp_inst_asset_rec.ACTIVE_START_DATE,
p_OLD_ACTIVE_END_DATE => fnd_api.g_miss_date,
p_NEW_ACTIVE_END_DATE => l_temp_inst_asset_rec.ACTIVE_END_DATE);
IF ( l_asset_hist_csr.old_update_status IS NULL
AND l_asset_hist_csr.new_update_status IS NULL ) THEN
IF ( l_temp_inst_asset_rec.update_status = l_curr_asset_rec.update_status )
OR ( l_temp_inst_asset_rec.update_status = fnd_api.g_miss_char ) THEN
l_asset_hist_csr.old_update_status := NULL;
l_asset_hist_csr.new_update_status := NULL;
l_asset_hist_csr.old_update_status := fnd_api.g_miss_char;
l_asset_hist_csr.new_update_status := l_temp_inst_asset_rec.update_status;
l_asset_hist_csr.old_update_status := fnd_api.g_miss_char;
l_asset_hist_csr.new_update_status := l_temp_inst_asset_rec.update_status;
csi_i_assets_h_pkg.update_row (
p_instance_asset_history_id => l_asset_hist_id ,
p_instance_asset_id => fnd_api.g_miss_num ,
p_transaction_id => fnd_api.g_miss_num ,
p_old_instance_id => l_asset_hist_csr.old_instance_id ,
p_new_instance_id => l_asset_hist_csr.new_instance_id ,
p_old_fa_asset_id => l_asset_hist_csr.old_fa_asset_id ,
p_new_fa_asset_id => l_asset_hist_csr.new_fa_asset_id ,
p_old_asset_quantity => l_asset_hist_csr.old_asset_quantity ,
p_new_asset_quantity => l_asset_hist_csr.new_asset_quantity ,
p_old_fa_book_type_code => l_asset_hist_csr.old_fa_book_type_code ,
p_new_fa_book_type_code => l_asset_hist_csr.new_fa_book_type_code ,
p_old_fa_location_id => l_asset_hist_csr.old_fa_location_id ,
p_new_fa_location_id => l_asset_hist_csr.new_fa_location_id ,
p_old_update_status => l_asset_hist_csr.old_update_status ,
p_new_update_status => l_asset_hist_csr.new_update_status ,
p_OLD_FA_SYNC_FLAG => l_asset_hist_csr.old_fa_sync_flag,
p_NEW_FA_SYNC_FLAG => l_asset_hist_csr.new_fa_sync_flag,
p_OLD_FA_MASS_ADDITION_ID => l_asset_hist_csr.old_fa_mass_addition_id,
p_NEW_FA_MASS_ADDITION_ID => l_asset_hist_csr.new_fa_mass_addition_id,
p_OLD_CREATION_COMPLETE_FLAG => l_asset_hist_csr.old_creation_complete_flag,
p_NEW_CREATION_COMPLETE_FLAG => l_asset_hist_csr.new_creation_complete_flag,
p_full_dump_flag => fnd_api.g_miss_char ,
p_created_by => fnd_api.g_miss_num ,
p_creation_date => fnd_api.g_miss_date ,
p_last_updated_by => fnd_global.user_id ,
p_last_update_date => SYSDATE ,
p_last_update_login => fnd_global.login_id ,
p_object_version_number => fnd_api.g_miss_num ,
p_old_active_start_date => l_asset_hist_csr.old_active_start_date ,
p_new_active_start_date => l_asset_hist_csr.new_active_start_date ,
p_old_active_end_date => l_asset_hist_csr.old_active_end_date ,
p_new_active_end_date => l_asset_hist_csr.new_active_end_date );
l_temp_inst_asset_rec.update_status := p_instance_asset_rec.update_status ;
IF (p_instance_asset_rec.UPDATE_STATUS = FND_API.G_MISS_CHAR) THEN
l_temp_inst_asset_rec.UPDATE_STATUS := l_curr_asset_rec.UPDATE_STATUS ;
CSI_I_ASSETS_H_PKG.Insert_Row
(
px_INSTANCE_ASSET_HISTORY_ID => l_instance_asset_hist_id ,
p_INSTANCE_ASSET_ID => p_instance_asset_rec.INSTANCE_ASSET_ID ,
p_TRANSACTION_ID => p_txn_rec.transaction_id ,
p_OLD_INSTANCE_ID => l_curr_asset_rec.INSTANCE_ID ,
p_NEW_INSTANCE_ID => l_temp_inst_asset_rec.INSTANCE_ID ,
p_OLD_FA_ASSET_ID => l_curr_asset_rec.FA_ASSET_ID ,
p_NEW_FA_ASSET_ID => l_temp_inst_asset_rec.FA_ASSET_ID ,
p_OLD_ASSET_QUANTITY => l_curr_asset_rec.ASSET_QUANTITY ,
p_NEW_ASSET_QUANTITY => l_temp_inst_asset_rec.ASSET_QUANTITY ,
p_OLD_FA_BOOK_TYPE_CODE => l_curr_asset_rec.FA_BOOK_TYPE_CODE ,
p_NEW_FA_BOOK_TYPE_CODE => l_temp_inst_asset_rec.FA_BOOK_TYPE_CODE ,
p_OLD_FA_LOCATION_ID => l_curr_asset_rec.FA_LOCATION_ID ,
p_NEW_FA_LOCATION_ID => l_temp_inst_asset_rec.FA_LOCATION_ID ,
p_OLD_UPDATE_STATUS => l_curr_asset_rec.UPDATE_STATUS ,
p_NEW_UPDATE_STATUS => l_temp_inst_asset_rec.UPDATE_STATUS ,
p_OLD_FA_SYNC_FLAG => l_curr_asset_rec.FA_SYNC_FLAG,
p_NEW_FA_SYNC_FLAG => l_temp_inst_asset_rec.FA_SYNC_FLAG,
p_OLD_FA_MASS_ADDITION_ID => l_curr_asset_rec.FA_MASS_ADDITION_ID,
p_NEW_FA_MASS_ADDITION_ID => l_temp_inst_asset_rec.FA_MASS_ADDITION_ID,
p_OLD_CREATION_COMPLETE_FLAG => l_curr_asset_rec.CREATION_COMPLETE_FLAG,
p_NEW_CREATION_COMPLETE_FLAG => l_temp_inst_asset_rec.creation_complete_flag,
p_FULL_DUMP_FLAG => 'Y' ,
p_CREATED_BY => FND_GLOBAL.USER_ID ,
p_CREATION_DATE => SYSDATE ,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID ,
p_LAST_UPDATE_DATE => SYSDATE ,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID ,
p_OBJECT_VERSION_NUMBER => 1 ,
p_OLD_ACTIVE_START_DATE => l_curr_asset_rec.ACTIVE_START_DATE ,
p_NEW_ACTIVE_START_DATE => l_temp_inst_asset_rec.ACTIVE_START_DATE,
p_OLD_ACTIVE_END_DATE => l_curr_asset_rec.ACTIVE_END_DATE ,
p_NEW_ACTIVE_END_DATE => l_temp_inst_asset_rec.ACTIVE_END_DATE );
IF (p_instance_asset_rec.update_status = fnd_api.g_miss_char) OR
NVL(p_instance_asset_rec.update_status, fnd_api.g_miss_char) = NVL(l_temp_inst_asset_rec.update_status, fnd_api.g_miss_char) THEN
l_ins_asset_hist_rec.old_fa_book_type_code := NULL;
NVL(l_temp_inst_asset_rec.update_status,fnd_api.g_miss_char) <> NVL(p_instance_asset_rec.update_status,fnd_api.g_miss_char) THEN
l_ins_asset_hist_rec.old_fa_book_type_code := l_temp_inst_asset_rec.update_status ;
l_ins_asset_hist_rec.new_fa_book_type_code := p_instance_asset_rec.update_status ;
CSI_I_ASSETS_H_PKG.Insert_Row (
px_INSTANCE_ASSET_HISTORY_ID => l_instance_asset_hist_id ,
p_INSTANCE_ASSET_ID => p_instance_asset_rec.INSTANCE_ASSET_ID ,
p_TRANSACTION_ID => p_txn_rec.transaction_id ,
p_OLD_INSTANCE_ID => l_ins_asset_hist_rec.old_INSTANCE_ID ,
p_NEW_INSTANCE_ID => l_ins_asset_hist_rec.new_INSTANCE_ID ,
p_OLD_FA_ASSET_ID => l_ins_asset_hist_rec.old_FA_ASSET_ID ,
p_NEW_FA_ASSET_ID => l_ins_asset_hist_rec.new_FA_ASSET_ID ,
p_OLD_ASSET_QUANTITY => l_ins_asset_hist_rec.old_ASSET_QUANTITY ,
p_NEW_ASSET_QUANTITY => l_ins_asset_hist_rec.new_ASSET_QUANTITY ,
p_OLD_FA_BOOK_TYPE_CODE => l_ins_asset_hist_rec.old_FA_BOOK_TYPE_CODE ,
p_NEW_FA_BOOK_TYPE_CODE => l_ins_asset_hist_rec.new_FA_BOOK_TYPE_CODE ,
p_OLD_FA_LOCATION_ID => l_ins_asset_hist_rec.old_FA_LOCATION_ID ,
p_NEW_FA_LOCATION_ID => l_ins_asset_hist_rec.new_FA_LOCATION_ID ,
p_OLD_UPDATE_STATUS => l_ins_asset_hist_rec.old_UPDATE_STATUS ,
p_NEW_UPDATE_STATUS => l_ins_asset_hist_rec.new_UPDATE_STATUS ,
p_OLD_FA_SYNC_FLAG => l_ins_asset_hist_rec.old_fa_sync_flag,
p_NEW_FA_SYNC_FLAG => l_ins_asset_hist_rec.new_fa_sync_flag,
p_OLD_FA_MASS_ADDITION_ID => l_ins_asset_hist_rec.old_fa_mass_addition_id,
p_NEW_FA_MASS_ADDITION_ID => l_ins_asset_hist_rec.new_fa_mass_addition_id,
p_OLD_CREATION_COMPLETE_FLAG => l_ins_asset_hist_rec.old_creation_complete_flag,
p_NEW_CREATION_COMPLETE_FLAG => l_ins_asset_hist_rec.new_creation_complete_flag,
p_FULL_DUMP_FLAG => 'N' ,
p_CREATED_BY => FND_GLOBAL.USER_ID ,
p_CREATION_DATE => SYSDATE ,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID ,
p_LAST_UPDATE_DATE => SYSDATE ,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID ,
p_OBJECT_VERSION_NUMBER => 1 ,
p_OLD_ACTIVE_START_DATE => l_ins_asset_hist_rec.old_ACTIVE_START_DATE ,
p_NEW_ACTIVE_START_DATE => l_ins_asset_hist_rec.new_ACTIVE_START_DATE ,
p_OLD_ACTIVE_END_DATE => l_ins_asset_hist_rec.old_ACTIVE_END_DATE ,
p_NEW_ACTIVE_END_DATE => l_ins_asset_hist_rec.new_ACTIVE_END_DATE );
csi_item_instance_pvt.get_and_update_acct_class
( p_api_version => p_api_version
,p_commit => p_commit
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_instance_id => l_curr_asset_rec.instance_id
,p_instance_expiry_flag => p_instance_asset_rec.check_for_instance_expiry
,p_txn_rec => p_txn_rec
,x_acct_class_code => l_acct_class_code
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
ROLLBACK TO update_instance_asset_pvt;
ROLLBACK TO update_instance_asset_pvt;
ROLLBACK TO update_instance_asset_pvt;
END update_instance_asset;
SELECT ah.INSTANCE_ASSET_HISTORY_ID ,
ah.INSTANCE_ASSET_ID ,
ah.TRANSACTION_ID ,
ah.OLD_INSTANCE_ID ,
ah.NEW_INSTANCE_ID ,
ah.OLD_FA_ASSET_ID ,
ah.NEW_FA_ASSET_ID ,
ah.OLD_ASSET_QUANTITY ,
ah.NEW_ASSET_QUANTITY ,
ah.OLD_FA_BOOK_TYPE_CODE ,
ah.NEW_FA_BOOK_TYPE_CODE ,
ah.OLD_FA_LOCATION_ID ,
ah.NEW_FA_LOCATION_ID ,
ah.OLD_UPDATE_STATUS ,
ah.NEW_UPDATE_STATUS ,
ah.FULL_DUMP_FLAG ,
ah.OBJECT_VERSION_NUMBER ,
ah.SECURITY_GROUP_ID ,
ah.OLD_ACTIVE_START_DATE ,
ah.NEW_ACTIVE_START_DATE ,
ah.OLD_ACTIVE_END_DATE ,
ah.NEW_ACTIVE_END_DATE ,
a.INSTANCE_ID
FROM csi_i_assets_h ah,
csi_i_assets a
WHERE ah.transaction_id = p_txn_id
AND ah.instance_asset_id = a.instance_asset_id;
IF NVL(l_asset_hist_csr.old_update_status,fnd_api.g_miss_char) = NVL(l_asset_hist_csr.new_update_status,fnd_api.g_miss_char)
THEN
l_old_ins_asset_rec.update_status := NULL;
l_new_ins_asset_rec.update_status := NULL;
l_old_ins_asset_rec.update_status := l_asset_hist_csr.old_update_status;
l_new_ins_asset_rec.update_status := l_asset_hist_csr.new_update_status;
l_ins_asset_rec.old_update_status := l_old_ins_asset_rec.update_status ;
l_ins_asset_rec.new_update_status := l_new_ins_asset_rec.update_status ;
SELECT fa_location_id
FROM csi_a_locations
WHERE location_id = l_inst_location_id;
SELECT a.instance_id,null inst_interface_id, a.quantity, a.location_id ,a.location_type_code
FROM csi_item_instances a, csi_i_assets b
WHERE a.instance_id = b.instance_id
AND b.fa_asset_id = p_fa_asset_id
AND b.fa_location_id = p_fa_location_id ;
SELECT a.instance_id,a.inst_interface_id,a.quantity, a.location_id ,a.location_type_code
FROM csi_instance_interface a ,csi_i_asset_interface b
WHERE a.inst_interface_id=b.inst_interface_id
AND b.fa_asset_id = p_fa_asset_id
AND b.fa_location_id = p_fa_location_id
AND a.process_status IN ('R','X')
AND a.source_system_name = nvl(p_source_system_name ,a.source_system_name)
UNION ALL
SELECT a.instance_id,null inst_interface_id, a.quantity, a.location_id ,a.location_type_code
FROM csi_item_instances a, csi_i_assets b
WHERE a.instance_id = b.instance_id
AND b.fa_asset_id = p_fa_asset_id
AND b.fa_location_id = p_fa_location_id
AND NOT EXISTS ( SELECT 1 FROM csi_instance_interface c
WHERE c.instance_id = a.instance_id
AND c.process_status IN ('R','X')
AND c.source_system_name = nvl(p_source_system_name ,c.source_system_name)) ;
SELECT b.fa_asset_id,b.fa_location_id, b.asset_quantity
FROM csi_item_instances a, csi_i_assets b
WHERE a.instance_id = b.instance_id
AND a.instance_id = p_instance_id ;
SELECT b.fa_asset_id,b.fa_location_id,b.asset_quantity
FROM csi_instance_interface a ,csi_i_asset_interface b
WHERE a.inst_interface_id = b.inst_interface_id
AND a.process_status IN ('R','X')
AND (a.instance_id = p_instance_id
OR a.inst_interface_id = p_interface_id )
AND a.source_system_name = nvl(p_source_system_name,a.source_system_name)
UNION ALL
SELECT b.fa_asset_id,b.fa_location_id, b.asset_quantity
FROM csi_item_instances a, csi_i_assets b
WHERE a.instance_id = b.instance_id
AND a.instance_id = p_instance_id
AND NOT EXISTS ( SELECT 1
FROM csi_i_asset_interface c,
csi_instance_interface d
WHERE d.instance_id = a.instance_id
AND c.inst_interface_id = d.inst_interface_id
AND d.source_system_name = nvl(p_source_system_name,d.source_system_name)
AND c.fa_asset_id = b.fa_asset_id
AND d.process_status IN ('R','X')
AND c.fa_location_id = b.fa_location_id );
l_fa_asset_id_tbl.delete;
l_fa_asset_loc_id_tbl.delete;
l_asset_qty_tbl.delete;
SELECT SUM(units_assigned)
FROM fa_distribution_history fadh
WHERE fadh.asset_id = p_fa_asset_id
AND fadh.location_id = p_location_id
AND units_assigned > 0
AND fadh.date_ineffective IS NULL;
l_instance_asset_sync_tbl.DELETE;
l_instance_sync_tbl.delete;
l_tmp_instance_asset_sync_tbl.DELETE(l_tbl_cnt);
l_instance_asset_sync_tbl.DELETE;
l_tmp_instance_asset_sync_tbl.DELETE;
l_instance_sync_tbl.DELETE;
l_instance_asset_sync_tbl.delete;
l_instance_sync_tbl.DELETE;
l_tmp_instance_sync_tbl.DELETE( l_tbl_cnt );
l_instance_sync_tbl.delete;
l_tmp_instance_sync_tbl.delete;