[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT source_transaction_type_id
into l_src_transaction_type_id
from CSI_T_TRANSACTION_LINES
WHERE transaction_line_id = p_txn_line_dtl_rec.transaction_line_id;
/* SELECT decode(nvl(p_txn_line_dtl_rec.processing_status,fnd_api.g_miss_char),
fnd_api.g_miss_char, 'SUBMIT', p_txn_line_dtl_rec.processing_status)
INTO l_processing_status
FROM sys.dual;
SELECT decode(nvl(p_txn_line_dtl_rec.preserve_detail_flag,fnd_api.g_miss_char),
fnd_api.g_miss_char, 'Y', p_txn_line_dtl_rec.preserve_detail_flag)
INTO l_preserve_detail_flag
FROM sys.dual;*/
Select nvl(ownership_cascade_at_txn, 'N')
Into l_cascade_owner_flag
From csi_install_parameters;
Select serial_number, instance_id, lot_number
Into p_txn_line_dtl_rec.serial_number, p_txn_line_dtl_rec.instance_id,
p_txn_line_dtl_rec.lot_number
From csi_item_instances
Where config_inst_hdr_id = p_txn_line_dtl_rec.config_inst_hdr_id
AND config_inst_item_id = p_txn_line_dtl_rec.config_inst_item_id;
Select serial_number, instance_id, lot_number
Into p_txn_line_dtl_rec.serial_number, p_txn_line_dtl_rec.instance_id,
p_txn_line_dtl_rec.lot_number
From csi_item_instances
Where inventory_item_id = p_txn_line_dtl_rec.inventory_item_id
and serial_number = p_txn_line_dtl_rec.serial_number;
Select serial_number, instance_id, lot_number
Into p_txn_line_dtl_rec.serial_number, p_txn_line_dtl_rec.instance_id,
p_txn_line_dtl_rec.lot_number
From csi_item_instances
Where instance_id = p_txn_line_dtl_rec.instance_id;
p_api_name => 'insert_row',
p_pkg_name => 'csi_t_txn_line_details_pkg');
csi_t_txn_line_details_pkg.insert_row(
px_txn_line_detail_id => l_txn_line_detail_id,
p_transaction_line_id => p_txn_line_dtl_rec.transaction_line_id,
p_sub_type_id => p_txn_line_dtl_rec.sub_type_id,
p_instance_exists_flag => p_txn_line_dtl_rec.instance_exists_flag,
p_source_transaction_flag => p_txn_line_dtl_rec.source_transaction_flag,
p_instance_id => p_txn_line_dtl_rec.instance_id,
p_csi_system_id => p_txn_line_dtl_rec.csi_system_id,
p_inventory_item_id => p_txn_line_dtl_rec.inventory_item_id,
p_inv_organization_id => p_txn_line_dtl_rec.inv_organization_id,
p_inventory_revision => p_txn_line_dtl_rec.inventory_revision,
p_instance_type_code => p_txn_line_dtl_rec.instance_type_code,
p_item_condition_id => p_txn_line_dtl_rec.item_condition_id,
p_quantity => p_txn_line_dtl_rec.quantity,
p_unit_of_measure => p_txn_line_dtl_rec.unit_of_measure,
p_qty_remaining => p_txn_line_dtl_rec.qty_remaining,
p_serial_number => p_txn_line_dtl_rec.serial_number,
p_lot_number => p_txn_line_dtl_rec.lot_number,
p_mfg_serial_number_flag => p_txn_line_dtl_rec.mfg_serial_number_flag,
p_location_type_code => p_txn_line_dtl_rec.location_type_code,
p_location_id => p_txn_line_dtl_rec.location_id,
p_installation_date => p_txn_line_dtl_rec.installation_date,
p_in_service_date => p_txn_line_dtl_rec.in_service_date,
p_external_reference => p_txn_line_dtl_rec.external_reference,
p_version_label => p_txn_line_dtl_rec.version_label,
p_transaction_system_id => p_txn_line_dtl_rec.transaction_system_id,
p_sellable_flag => p_txn_line_dtl_rec.sellable_flag,
p_return_by_date => p_txn_line_dtl_rec.return_by_date,
p_active_start_date => p_txn_line_dtl_rec.active_start_date,
p_active_end_date => p_txn_line_dtl_rec.active_end_date,
p_preserve_detail_flag => l_preserve_detail_flag,
p_changed_instance_id => p_txn_line_dtl_rec.changed_instance_id,
p_reference_source_id => p_txn_line_dtl_rec.reference_source_id,
p_reference_source_line_id => p_txn_line_dtl_rec.reference_source_line_id,
p_reference_source_date => p_txn_line_dtl_rec.reference_source_date,
p_csi_transaction_id => p_txn_line_dtl_rec.csi_transaction_id,
p_source_txn_line_detail_id => p_txn_line_dtl_rec.source_txn_line_detail_id,
p_inv_mtl_transaction_id => p_txn_line_dtl_rec.inv_mtl_transaction_id,
p_processing_status => l_processing_status,
p_error_code => p_txn_line_dtl_rec.error_code,
p_error_explanation => p_txn_line_dtl_rec.error_explanation,
-- Added for CZ Integration (Begin)
p_config_inst_hdr_id => p_txn_line_dtl_rec.config_inst_hdr_id ,
p_config_inst_rev_num => p_txn_line_dtl_rec.config_inst_rev_num ,
p_config_inst_item_id => p_txn_line_dtl_rec.config_inst_item_id ,
p_config_inst_baseline_rev_num => p_txn_line_dtl_rec.config_inst_baseline_rev_num ,
p_target_commitment_date => p_txn_line_dtl_rec.target_commitment_date ,
p_instance_description => p_txn_line_dtl_rec.instance_description ,
-- Added for CZ Integration (End)
-- Added for Partner Ordering (Begin)
p_install_location_type_code => p_txn_line_dtl_rec.install_location_type_code,
p_install_location_id => p_txn_line_dtl_rec.install_location_id,
-- Added for Partner Ordering (End)
p_cascade_owner_flag => l_cascade_owner_flag, -- bug 2972082
p_attribute1 => p_txn_line_dtl_rec.attribute1,
p_attribute2 => p_txn_line_dtl_rec.attribute2,
p_attribute3 => p_txn_line_dtl_rec.attribute3,
p_attribute4 => p_txn_line_dtl_rec.attribute4,
p_attribute5 => p_txn_line_dtl_rec.attribute5,
p_attribute6 => p_txn_line_dtl_rec.attribute6,
p_attribute7 => p_txn_line_dtl_rec.attribute7,
p_attribute8 => p_txn_line_dtl_rec.attribute8,
p_attribute9 => p_txn_line_dtl_rec.attribute9,
p_attribute10 => p_txn_line_dtl_rec.attribute10,
p_attribute11 => p_txn_line_dtl_rec.attribute11,
p_attribute12 => p_txn_line_dtl_rec.attribute12,
p_attribute13 => p_txn_line_dtl_rec.attribute13,
p_attribute14 => p_txn_line_dtl_rec.attribute14,
p_attribute15 => p_txn_line_dtl_rec.attribute15,
p_created_by => g_user_id,
p_creation_date => sysdate,
p_last_updated_by => g_user_id,
p_last_update_date => sysdate,
p_last_update_login => g_login_id,
p_object_version_number => 1.0,
p_context => p_txn_line_dtl_rec.context,
p_parent_instance_id => p_txn_line_dtl_rec.parent_instance_id,
p_assc_txn_line_detail_id => p_txn_line_dtl_rec.assc_txn_line_detail_id,
p_overriding_csi_txn_id => p_txn_line_dtl_rec.overriding_csi_txn_id,
p_instance_status_id => p_txn_line_dtl_rec.instance_status_id);
'csi_t_txn_line_details_pkg.insert_row Failed. '||substr(sqlerrm,1,200));
update csi_t_party_details
set contact_party_id = l_contact_party_id
where txn_party_detail_id = px_txn_party_dtl_tbl(cont_ind).txn_party_detail_id;
/* This procedure is used to update the transaction line details. */
PROCEDURE update_txn_line_dtls (
p_api_version IN NUMBER,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
p_txn_line_rec IN csi_t_datastructures_grp.txn_line_rec,
p_txn_line_detail_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl,
px_txn_ii_rltns_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ii_rltns_tbl,
px_txn_party_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_party_detail_tbl,
px_txn_pty_acct_detail_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_pty_acct_detail_tbl,
px_txn_org_assgn_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
px_txn_ext_attrib_vals_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_txn_line_dtls';
SELECT *
FROM csi_t_txn_line_details
WHERE txn_line_detail_id = p_line_dtl_id;
SELECT *
FROM csi_t_party_details
WHERE txn_line_detail_id = p_line_dtl_id;
SELECT *
FROM csi_t_org_assignments
WHERE txn_line_detail_id = p_line_dtl_id;
SELECT *
FROM csi_t_extend_attribs
WHERE txn_line_detail_id = p_line_dtl_id;
SAVEPOINT update_txn_line_dtls;
csi_t_txn_line_dtls_pvt.update_txn_line(
p_txn_line_rec => p_txn_line_rec,
x_return_status => l_return_status);
debug('Update transaction lines failed.');
SELECT 'Y'
into l_found
FROM cz_config_items_v
WHERE instance_hdr_id = l_td_rec.config_inst_hdr_id
AND instance_rev_nbr = l_td_rec.config_inst_rev_num
AND config_item_id = l_td_rec.config_inst_item_id;
/* not sure why the following are required for an update OR validating sub type - commenting
csi_t_vldn_routines_pvt.check_reqd_param(
p_value => l_td_rec.inventory_item_id,
p_param_name => 'l_td_rec.inventory_item_id',
p_api_name => l_api_name);
/* not sure why the following are required for an update OR the tld is sourced - commenting
in any case, for validating serial, lot, revision etc WHEN CHANGED
we are checking for the same again...
-- mandate item , organization and quantity and uom
csi_t_vldn_routines_pvt.check_reqd_param(
p_value => l_td_rec.inv_organization_id,
p_param_name => 'l_td_rec.inv_organization_id',
p_api_name => l_api_name);
SELECT source_transaction_type_id
into l_src_transaction_type_id
from CSI_T_TRANSACTION_LINES
WHERE transaction_line_id = l_td_rec.transaction_line_id;
l_td_rec.last_updated_by := g_user_id;
l_td_rec.last_update_date := sysdate;
l_td_rec.last_update_login := g_login_id;
/* I have to do this update statement here because all the child entities
are processed before the txn line detail is updated */
update csi_t_txn_line_details
set instance_id = l_td_rec.instance_id,
instance_exists_flag = l_td_rec.instance_exists_flag
where txn_line_detail_id = l_td_rec.txn_line_detail_id;
SELECT instance_party_id
INTO l_instance_party_id
FROM csi_i_parties
WHERE instance_id = l_td_rec.instance_id -- new instance
AND party_id = l_pty_cur_rec.party_source_id -- old party
AND party_source_table = l_pty_cur_rec.party_source_table
AND relationship_type_code = l_pty_cur_rec.relationship_type_code
AND nvl(contact_flag,'N') = nvl(l_pty_cur_rec.contact_flag,'N')
AND sysdate between nvl(active_start_date, sysdate-1)
and nvl(active_end_date, sysdate+1);
SELECT csiip.instance_party_id
INTO l_instance_party_id
FROM
csi_i_parties csiip,
csi_i_parties csiipc,
csi_t_party_details csitpd,
csi_t_party_details csitpdc
WHERE csitpdc.party_SOURCE_id = l_pty_cur_rec.party_source_id
AND csiipc.instance_id=l_td_rec.instance_id
AND csiip.contact_ip_id=csiipc.instance_party_id
AND csitpdc.contact_party_id=csitpd.txn_party_detail_id
AND csitpdc.party_source_table = csiip.party_source_table
AND csitpdc.relationship_type_code = csiip.relationship_type_code
AND nvl(csitpdc.contact_flag,'N')= nvl(csiip.contact_flag,'N')
AND csitpdc.party_SOURCE_id=csiip.party_id
AND csitpdc.party_source_table = l_pty_cur_rec.party_source_table
AND csitpdc.relationship_type_code = l_pty_cur_rec.relationship_type_code
AND csitpdc.txn_party_detail_id= l_pty_cur_rec.txn_party_detail_id
AND nvl(csitpdc.contact_flag,'N') =nvl(l_pty_cur_rec.contact_flag,'N')
AND csitpd.party_source_id= csiipc.party_id
AND csitpd.party_source_table= csiipc.party_source_table
AND csitpd.relationship_type_code = csiipc.relationship_type_code
AND csitpd.contact_flag = csiipc.contact_flag
AND sysdate between nvl(csiip.active_start_date, sysdate-1)
AND nvl(csiip.active_end_date, sysdate+1);
SELECT instance_party_id
INTO l_instance_party_id
FROM csi_i_parties
WHERE instance_id = l_td_rec.instance_id
AND relationship_type_code = 'OWNER';
csi_t_txn_parties_grp.update_txn_party_dtls(
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_txn_party_detail_tbl => l_pty_tbl,
px_txn_pty_acct_detail_tbl => l_pty_acc_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT instance_ou_id
INTO l_instance_ou_id
FROM csi_i_org_assignments
WHERE instance_id = l_td_rec.instance_id
AND operating_unit_id = l_oa_cur_rec.operating_unit_id;
UPDATE csi_t_org_assignments
SET instance_ou_id = l_instance_ou_id
WHERE txn_operating_unit_id = l_oa_cur_rec.txn_operating_unit_id;
SELECT attribute_value_id
INTO l_attrib_source_id
FROM csi_iea_values
WHERE instance_id = l_td_rec.instance_id
AND attribute_value = l_ea_cur_rec.attribute_value;
UPDATE csi_t_extend_attribs
SET attrib_source_id = l_attrib_source_id
WHERE txn_attrib_detail_id =
l_ea_cur_rec.txn_attrib_detail_id;
csi_t_txn_parties_pvt.delete_txn_party_dtls(
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_txn_party_ids_tbl => l_pty_ids_tbl,
x_txn_pty_acct_ids_tbl => l_x_pty_acct_ids_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
csi_t_txn_parties_pvt.delete_txn_pty_acct_dtls(
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_txn_pty_acct_ids_tbl => l_pty_acct_ids_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
csi_t_txn_rltnshps_pvt.delete_txn_ii_rltns_dtls(
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_txn_ii_rltns_ids_tbl => l_iir_ids_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
csi_t_txn_ous_pvt.delete_txn_org_assgn_dtls(
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_txn_org_assgn_ids_tbl => l_oa_ids_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
csi_t_txn_attribs_pvt.delete_txn_ext_attrib_dtls(
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_txn_ext_attrib_ids_tbl => l_ea_ids_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
Select serial_number, instance_id, lot_number
Into l_td_rec.serial_number, l_td_rec.instance_id,
l_td_rec.lot_number
From csi_item_instances
Where inventory_item_id = l_td_rec.inventory_item_id
and serial_number = l_td_rec.serial_number;
Select serial_number, instance_id, lot_number
Into l_td_rec.serial_number, l_td_rec.instance_id,
l_td_rec.lot_number
From csi_item_instances
Where instance_id = l_td_rec.instance_id;
p_api_name => 'update_row',
p_pkg_name => 'csi_t_txn_line_details_pkg');
csi_t_txn_line_details_pkg.update_row(
p_txn_line_detail_id => l_td_rec.txn_line_detail_id,
p_transaction_line_id => l_td_rec.transaction_line_id,
p_sub_type_id => l_td_rec.sub_type_id,
p_instance_exists_flag => l_td_rec.instance_exists_flag,
p_source_transaction_flag => l_td_rec.source_transaction_flag,
p_instance_id => l_td_rec.instance_id,
p_csi_system_id => l_td_rec.csi_system_id,
p_inventory_item_id => l_td_rec.inventory_item_id,
p_inv_organization_id => l_td_rec.inv_organization_id,
p_inventory_revision => l_td_rec.inventory_revision,
p_instance_type_code => l_td_rec.instance_type_code,
p_item_condition_id => l_td_rec.item_condition_id,
p_quantity => l_td_rec.quantity,
p_unit_of_measure => l_td_rec.unit_of_measure,
p_qty_remaining => l_td_rec.qty_remaining,
p_serial_number => l_td_rec.serial_number,
p_lot_number => l_td_rec.lot_number,
p_mfg_serial_number_flag => l_td_rec.mfg_serial_number_flag,
p_location_type_code => l_td_rec.location_type_code,
p_location_id => l_td_rec.location_id,
p_installation_date => l_td_rec.installation_date,
p_in_service_date => l_td_rec.in_service_date,
p_external_reference => l_td_rec.external_reference,
p_version_label => l_td_rec.version_label,
p_transaction_system_id => l_td_rec.transaction_system_id,
p_sellable_flag => l_td_rec.sellable_flag,
p_return_by_date => l_td_rec.return_by_date,
p_active_start_date => l_td_rec.active_start_date,
p_active_end_date => l_td_rec.active_end_date,
p_preserve_detail_flag => l_td_rec.preserve_detail_flag,
p_changed_instance_id => l_td_rec.changed_instance_id,
p_reference_source_id => l_td_rec.reference_source_id,
p_reference_source_line_id => l_td_rec.reference_source_line_id,
p_reference_source_date => l_td_rec.reference_source_date,
p_csi_transaction_id => l_td_rec.csi_transaction_id,
p_source_txn_line_detail_id => l_td_rec.source_txn_line_detail_id,
p_inv_mtl_transaction_id => l_td_rec.inv_mtl_transaction_id,
p_processing_status => l_td_rec.processing_status,
p_error_code => l_td_rec.error_code,
p_error_explanation => l_td_rec.error_explanation,
-- Added for CZ Integration (Begin)
p_config_inst_hdr_id => l_td_rec.config_inst_hdr_id ,
p_config_inst_rev_num => l_td_rec.config_inst_rev_num ,
p_config_inst_item_id => l_td_rec.config_inst_item_id ,
p_config_inst_baseline_rev_num => l_td_rec.config_inst_baseline_rev_num ,
p_target_commitment_date => l_td_rec.target_commitment_date ,
p_instance_description => l_td_rec.instance_description ,
-- Added for CZ Integration (End)
-- Added for partner ordering
p_install_location_type_code => l_td_rec.install_location_type_code,
p_install_location_id => l_td_rec.install_location_id,
-- Added for partner ordering
p_cascade_owner_flag => l_td_rec.cascade_owner_flag,
p_attribute1 => l_td_rec.attribute1,
p_attribute2 => l_td_rec.attribute2,
p_attribute3 => l_td_rec.attribute3,
p_attribute4 => l_td_rec.attribute4,
p_attribute5 => l_td_rec.attribute5,
p_attribute6 => l_td_rec.attribute6,
p_attribute7 => l_td_rec.attribute7,
p_attribute8 => l_td_rec.attribute8,
p_attribute9 => l_td_rec.attribute9,
p_attribute10 => l_td_rec.attribute10,
p_attribute11 => l_td_rec.attribute11,
p_attribute12 => l_td_rec.attribute12,
p_attribute13 => l_td_rec.attribute13,
p_attribute14 => l_td_rec.attribute14,
p_attribute15 => l_td_rec.attribute15,
p_created_by => l_td_rec.created_by,
p_creation_date => l_td_rec.creation_date,
p_last_updated_by => l_td_rec.last_updated_by,
p_last_update_date => l_td_rec.last_update_date,
p_last_update_login => l_td_rec.last_update_login,
p_object_version_number => l_td_rec.object_version_number,
p_context => l_td_rec.context,
p_parent_instance_id => l_td_rec.parent_instance_id,
p_assc_txn_line_detail_id => l_td_rec.assc_txn_line_detail_id,
p_overriding_csi_txn_id => l_td_rec.overriding_csi_txn_id,
p_instance_status_id => l_td_rec.instance_status_id);
'csi_t_txn_line_details_pkg.update_row Failed. '||substr(sqlerrm,1,200));
debug('PTY Record No.: '||l_ind||' marked for update.');
END IF; -- update/create pty
update csi_t_party_details
set contact_party_id = l_contact_party_id
where txn_party_detail_id = l_c_pty_tbl(cont_ind).txn_party_detail_id;
csi_t_txn_parties_grp.update_txn_party_dtls(
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_txn_party_detail_tbl => l_u_pty_tbl,
px_txn_pty_acct_detail_tbl => l_u_pty_acct_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
debug('RLTNS Record No.: '||l_ind||' marked for update.');
csi_t_txn_rltnshps_grp.update_txn_ii_rltns_dtls (
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_txn_ii_rltns_tbl => l_u_ii_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
debug('ORG Record No.: '||l_ind||' marked for update.');
csi_t_txn_ous_grp.update_txn_org_assgn_dtls(
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_txn_org_assgn_tbl => l_u_oa_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
debug('EAV Record No.: '||l_ind||' marked for update.');
csi_t_txn_attribs_pvt.update_txn_ext_attrib_dtls(
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_txn_ext_attrib_vals_tbl => l_u_eav_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
debug('Transaction details updated successfully.');
ROLLBACK TO update_txn_line_dtls;
ROLLBACK TO update_txn_line_dtls;
ROLLBACK TO Update_Txn_Line_Dtls;
END update_txn_line_dtls;
p_dtl_select_stmt IN varchar2,
x_line_dtls_tbl OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
x_return_status OUT NOCOPY varchar2)
IS
l_dtl_qry_cur_id integer;
dbms_sql.parse(l_dtl_qry_cur_id, p_dtl_select_stmt , dbms_sql.native);
PROCEDURE build_line_dtls_select(
p_txn_line_detail_query_rec in csi_t_datastructures_grp.txn_line_detail_query_rec,
x_dtl_select_stmt OUT NOCOPY varchar2,
x_dtl_where_clause OUT NOCOPY varchar2,
x_return_status OUT NOCOPY varchar2)
IS
l_select_stmt varchar2(32767);
api_log('build_line_dtls_select');
l_select_stmt :=
'select txn_line_detail_id, transaction_line_id, sub_type_id, instance_exists_flag, '||
' source_transaction_flag, instance_id, changed_instance_id, '||
' csi_system_id, inventory_item_id, inventory_revision, '||
' inv_organization_id, item_condition_id, instance_type_code, '||
' quantity, unit_of_measure, qty_remaining, serial_number, '||
' mfg_serial_number_flag, lot_number, location_type_code, location_id, '||
' installation_date, in_service_date, external_reference, '||
' transaction_system_id, sellable_flag, version_label, return_by_date, '||
' active_start_date, active_end_date, preserve_detail_flag, reference_source_id, '||
' reference_source_date, csi_transaction_id, processing_status, error_code, '||
' error_explanation, context, attribute1, attribute2, attribute3, attribute4, '||
' attribute5, attribute6, attribute7, attribute8, attribute9, attribute10, '||
' attribute11, attribute12, attribute13, attribute14, attribute15, '||
' object_version_number, source_txn_line_detail_id, inv_mtl_transaction_id '||
' , config_inst_hdr_id, config_inst_rev_num , config_inst_item_id , target_commitment_date, '||
' instance_description , config_inst_baseline_rev_num , reference_source_line_id, '||
' install_location_type_code,install_location_id, '|| ' cascade_owner_flag, '||
' parent_instance_id, assc_txn_line_detail_id, '||
' overriding_csi_txn_id, instance_status_id '||
' from csi_t_txn_line_details ';
x_dtl_select_stmt := l_select_stmt;
END build_line_dtls_select;
PROCEDURE build_txn_lines_select(
p_txn_line_query_rec IN csi_t_datastructures_grp.txn_line_query_rec,
x_lines_select_stmt OUT NOCOPY varchar2,
x_lines_restrict OUT NOCOPY varchar2,
x_return_status OUT NOCOPY varchar2)
IS
l_select_stmt varchar2(32767);
api_log('build_txn_lines_select');
l_select_stmt :=
' transaction_line_id in (select transaction_line_id from csi_t_transaction_lines ';
l_select_stmt := l_select_stmt||l_where_clause||')';
x_lines_select_stmt := l_select_stmt;
END build_txn_lines_select;
l_dtl_select_stmt varchar2(32767);
build_line_dtls_select(
p_txn_line_detail_query_rec => p_txn_line_detail_query_rec,
x_dtl_select_stmt => l_dtl_select_stmt,
x_dtl_where_clause => l_dtl_where_clause,
x_return_status => l_return_status);
build_txn_lines_select(
p_txn_line_query_rec => p_txn_line_query_rec,
x_lines_select_stmt => l_lines_restrict_clause,
x_lines_restrict => l_lines_restrict,
x_return_status => l_return_status);
l_dtl_select_stmt := l_dtl_select_stmt||' where '||l_dtl_where_clause;
l_dtl_select_stmt := l_dtl_select_stmt||' and '||l_lines_restrict_clause;
l_dtl_select_stmt := l_dtl_select_stmt||' where '||l_lines_restrict_clause;
p_dtl_select_stmt => l_dtl_select_stmt,
x_line_dtls_tbl => x_txn_line_dtl_tbl,
x_return_status => l_return_status);
PROCEDURE delete_txn_line_dtls(
p_api_version IN NUMBER,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
p_txn_line_detail_ids_tbl IN csi_t_datastructures_grp.txn_line_detail_ids_tbl,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_txn_line_dtls';
SAVEPOINT delete_txn_line_dtls;
csi_t_txn_line_details_pkg.delete_row(
p_txn_line_detail_id => p_txn_line_detail_ids_tbl(l_ind).
txn_line_detail_id);
ROLLBACK TO delete_txn_line_dtls;
ROLLBACK TO delete_txn_line_dtls;
rollback to delete_txn_line_dtls;
END delete_txn_line_dtls;
PROCEDURE update_txn_line(
p_txn_line_rec IN csi_t_datastructures_grp.txn_line_rec,
x_return_status OUT NOCOPY varchar2)
IS
CURSOR txn_line_cur (p_txn_line_id in number)is
SELECT *
FROM csi_t_transaction_lines
WHERE transaction_line_id = p_txn_line_id;
l_api_name varchar2(30) := 'update_txn_line';
SELECT transaction_line_id
INTO l_txn_line_rec.transaction_line_id
FROM csi_t_transaction_lines
WHERE source_transaction_table = p_txn_line_rec.source_transaction_table
AND source_transaction_id = p_txn_line_rec.source_transaction_id;
p_api_name => 'update_txn_line_dtls');
x_transaction_line_rec => l_transaction_line_rec, -- changed for Mass Update R12
x_return_status => l_return_status);
p_api_name => 'update_row',
p_pkg_name => 'csi_t_transaction_lines_pkg');
csi_t_transaction_lines_pkg.update_row(
p_transaction_line_id => l_txn_line_rec.transaction_line_id,
p_source_transaction_type_id => l_txn_line_rec.source_transaction_type_id,
p_source_transaction_table => l_txn_line_rec.source_transaction_table,
p_source_txn_header_id => l_txn_line_rec.source_txn_header_id,
p_source_transaction_id => l_txn_line_rec.source_transaction_id,
p_error_code => l_txn_line_rec.error_code,
p_error_explanation => l_txn_line_rec.error_explanation,
-- Added for CZ Integration (Begin)
p_config_session_hdr_id => l_txn_line_rec.config_session_hdr_id ,
p_config_session_rev_num => l_txn_line_rec.config_session_rev_num ,
p_config_session_item_id => l_txn_line_rec.config_session_item_id ,
p_config_valid_status => l_txn_line_rec.config_valid_status ,
p_source_transaction_status => l_txn_line_rec.source_transaction_status ,
-- Added for CZ Integration (End)
p_processing_status => l_txn_line_rec.processing_status,
p_attribute1 => l_txn_line_rec.attribute1,
p_attribute2 => l_txn_line_rec.attribute2,
p_attribute3 => l_txn_line_rec.attribute3,
p_attribute4 => l_txn_line_rec.attribute4,
p_attribute5 => l_txn_line_rec.attribute5,
p_attribute6 => l_txn_line_rec.attribute6,
p_attribute7 => l_txn_line_rec.attribute7,
p_attribute8 => l_txn_line_rec.attribute8,
p_attribute9 => l_txn_line_rec.attribute9,
p_attribute10 => l_txn_line_rec.attribute10,
p_attribute11 => l_txn_line_rec.attribute11,
p_attribute12 => l_txn_line_rec.attribute12,
p_attribute13 => l_txn_line_rec.attribute13,
p_attribute14 => l_txn_line_rec.attribute14,
p_attribute15 => l_txn_line_rec.attribute15,
p_created_by => l_cur_rec.created_by,
p_creation_date => l_cur_rec.creation_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_txn_line_rec.object_version_number,
p_context => l_txn_line_rec.context);
END update_txn_line;