[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT oel.line_number ,
oel.line_id ,
oeh.order_number,
oeh.header_id
INTO x_rma_line_rec.source_line_ref,
x_rma_line_rec.source_line_ref_id,
x_rma_line_rec.source_header_ref,
x_rma_line_rec.source_header_ref_id
FROM oe_order_lines_all oel ,
oe_order_headers_all oeh
WHERE oeh.header_id = oel.header_id
AND oel.line_id = p_rma_line_id;
SELECT oel.line_id, oel.header_id, oeh.order_number,
nvl(oel.sold_from_org_id, oeh.sold_from_org_id),
nvl(oel.sold_to_org_id, oeh.sold_to_org_id),
nvl(oel.ship_from_org_id, oeh.ship_from_org_id),
nvl(oel.invoice_to_contact_id, oeh.invoice_to_contact_id ),
nvl(oel.ship_to_contact_id, oeh.ship_to_contact_id ),
oel.line_number, oel.option_number, oel.shipment_number,
oel.inventory_item_id, oel.item_type_code, oel.shippable_flag,
oel.org_id, oel.ordered_quantity, oel.fulfilled_quantity,
oel.fulfillment_date, oel.line_category_code
INTO l_rma_line_rec.line_id, l_rma_line_rec.header_id,
l_rma_header_rec.order_number, l_rma_line_rec.sold_from_org_id,
l_rma_line_rec.sold_to_org_id, l_rma_line_rec.ship_from_org_id,
l_rma_line_rec.invoice_to_contact_id, l_rma_line_rec.ship_to_contact_id,
l_rma_line_rec.line_number, l_rma_line_rec.option_number,
l_rma_line_rec.shipment_number, l_rma_line_rec.inventory_item_id,
l_rma_line_rec.item_type_code, l_rma_line_rec.shippable_flag,
l_rma_line_rec.org_id, l_rma_line_rec.ordered_quantity,
l_rma_line_rec.fulfilled_quantity, l_rma_line_rec.fulfillment_date,
l_rma_line_rec.line_category_code
FROM oe_order_lines_all oel, oe_order_headers_all oeh
WHERE line_id = p_rma_line_id
AND oel.header_id = oeh.header_id;
SELECT nvl(shippable_item_flag ,'N'),
serial_number_control_code,
lot_control_code,
revision_qty_control_code,
location_control_code,
comms_nl_trackable_flag
INTO l_shippable_item_flag,
l_error_rec.src_serial_num_ctrl_code,
l_error_rec.src_lot_ctrl_code ,
l_error_rec.src_rev_qty_ctrl_code,
l_error_rec.src_location_ctrl_code,
l_error_rec.comms_nl_trackable_flag
FROM mtl_system_items
WHERE inventory_item_id = l_rma_line_rec.inventory_item_id
AND organization_id = l_orgn_id;
SELECT processing_status
INTO l_processing_status
FROM csi_t_transaction_lines
WHERE source_transaction_table = l_src_txn_table
AND source_transaction_id = p_rma_line_id;
UPDATE csi_t_transaction_lines
SET processing_status = 'IN_PROCESS'
WHERE source_transaction_table = l_src_txn_table
AND source_transaction_id = p_rma_line_id;
SELECT transaction_line_id
INTO l_g_txn_line_rec.transaction_line_id
FROM csi_t_transaction_lines
WHERE source_transaction_table = l_src_txn_table
AND source_transaction_id = p_rma_line_id;
UPDATE csi_t_transaction_lines
SET processing_status = 'ERROR'
WHERE source_transaction_id = p_rma_line_id
AND source_transaction_table = 'OE_ORDER_LINES_ALL';
csi_utl_pkg.update_txn_line_dtl (
p_source_trx_id => p_rma_line_id,
p_source_trx_table => 'OE_ORDER_LINES_ALL',
p_api_name => l_api_name,
p_error_message => l_error_message );
UPDATE csi_t_transaction_lines
SET processing_status = 'ERROR'
WHERE source_transaction_id = p_rma_line_id
AND source_transaction_table = 'OE_ORDER_LINES_ALL';
csi_utl_pkg.update_txn_line_dtl (
p_source_trx_id => p_rma_line_id,
p_source_trx_table => 'OE_ORDER_LINES_ALL',
p_api_name => l_api_name,
p_error_message => l_error_message );
SELECT internal_party_id, ownership_override_at_txn
INTO l_internal_party_id, l_pty_override_flag
FROM csi_install_parameters;
SELECT party_id
INTO l_src_txn_owner_pty_id
FROM hz_cust_accounts_all
WHERE cust_account_id = p_rma_line_rec.sold_to_org_id;
Select object_id
Into l_curr_object_id
from csi_ii_relationships
Where object_id = l_object_inst_id
And sysdate between nvl(active_end_date, sysdate) and sysdate+1;
SELECT ct.TRANSACTION_TYPE_ID
INTO l_last_txn_type_id
FROM csi_transactions ct
WHERE ct.TRANSACTION_ID = (SELECT Max(ct2.TRANSACTION_ID)
FROM csi_item_instances_h cih, csi_transactions ct2
WHERE cih.transaction_id = ct2.transaction_id
AND cih.instance_id = l_instance_rec.instance_id
AND ct2.transaction_type_id = 53
AND ct2.source_header_ref_id = p_rma_line_rec.header_id);
/* Commented the call as part of fix for Bug 2733128. Added call to Update_Item_Instance instead
csi_t_gen_utility_pvt.dump_api_info(
p_pkg_name => 'csi_party_relationships_pub',
p_api_name => 'update_inst_party_relationship');
csi_party_relationships_pub.update_inst_party_relationship (
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_party_tbl => l_upd_parties_tbl,
p_party_account_tbl => l_upd_pty_accts_tbl,
p_txn_rec => l_csi_trxn_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
p_api_name => 'update_item_instance');
csi_item_instance_pub.update_item_instance(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_rec => l_chg_instance_rec,
p_ext_attrib_values_tbl => l_chg_ext_attrib_val_tbl,
p_party_tbl => l_upd_parties_tbl,
p_account_tbl => l_upd_pty_accts_tbl,
p_pricing_attrib_tbl => l_chg_pricing_attribs_tbl,
p_org_assignments_tbl => l_chg_org_units_tbl,
p_txn_rec => l_csi_trxn_rec,
p_asset_assignment_tbl => l_chg_inst_asset_tbl,
x_instance_id_lst => l_chg_inst_id_lst,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
Select inventory_item_id, flow_status_code, sold_to_org_id
Into l_orig_rma_item_id, l_orig_rma_status, l_orig_rma_owner_id
From oe_order_lines_all
Where line_id = l_txn_line_dtl_rec.reference_source_line_id
And header_id = l_txn_line_dtl_rec.reference_source_id;
debug('This Line had a Receiving Node and hence would have had updated IB...');
SELECT object_version_number
INTO l_u_instance_rec.object_version_number
FROM csi_item_instances
WHERE instance_id = l_instance_rec.instance_id;
p_api_name => 'update_item_instance');
csi_item_instance_pub.update_item_instance(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_rec => l_u_instance_rec,
p_party_tbl => l_u_parties_tbl,
p_account_tbl => l_u_pty_accts_tbl,
p_org_assignments_tbl => l_u_org_units_tbl,
p_ext_attrib_values_tbl => l_u_ea_values_tbl,
p_pricing_attrib_tbl => l_u_pricing_tbl,
p_asset_assignment_tbl => l_u_assets_tbl,
p_txn_rec => l_csi_trxn_rec,
x_instance_id_lst => l_u_instance_ids_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
Select serial_number_control_code
into l_item_srl_code
from mtl_system_items_b
where inventory_item_id = l_txn_line_dtl_rec.inventory_item_id
and organization_id = l_dest_location_rec.inv_organization_id;--l_txn_line_dtl_rec.inv_organization_id;
csi_t_txn_details_grp.update_txn_line_dtls(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_txn_line_rec => l_u_txn_line_rec,
p_txn_line_detail_tbl => l_u_line_dtl_tbl,
px_txn_ii_rltns_tbl => l_u_ii_rltns_tbl,
px_txn_party_detail_tbl => l_u_pty_dtl_tbl,
px_txn_pty_acct_detail_tbl => l_u_pty_acct_tbl,
px_txn_org_assgn_tbl => l_u_org_assgn_tbl,
px_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('Update txn line dtls failed for Fulfill RMA Line.');
select cil.lock_id,cil.lock_status,
cil.config_inst_rev_num
into l_lock_id,l_lock_status,
l_locked_inst_rev_num
from CSI_ITEM_INSTANCE_LOCKS cil
where cil.instance_id = l_p_instances_tbl(J).instance_id
and cil.lock_status <> 0;
select config_inst_hdr_id,config_inst_item_id,config_inst_rev_num,
instance_usage_code,active_end_date
into l_p_instances_tbl(J).config_inst_hdr_id,
l_p_instances_tbl(J).config_inst_item_id,
l_p_instances_tbl(J).config_inst_rev_num,
l_p_instances_tbl(J).instance_usage_code,
l_p_instances_tbl(J).active_end_date
from CSI_ITEM_INSTANCES
where instance_id = l_p_instances_tbl(J).instance_id;
l_unlock_inst_tbl.DELETE;
-- Update any pending TLD for the same config keys (fetched from lock table)
-- with the instance_id so that when regular fulfillment happens for this
-- tangible item (DISCONNECT), only the order line_id will be updated in the item instance
Update CSI_T_TXN_LINE_DETAILS
Set changed_instance_id = l_p_instances_tbl(J).instance_id
,overriding_csi_txn_id = l_csi_trxn_rec.transaction_id
Where config_inst_hdr_id = l_p_instances_tbl(J).config_inst_hdr_id
and config_inst_item_id = l_p_instances_tbl(J).config_inst_item_id
and config_inst_rev_num = l_locked_inst_rev_num
and nvl(processing_status,'$#$') = 'SUBMIT';
UPDATE csi_t_txn_line_details a
SET error_code = NULL,
error_explanation = NULL,
processing_status = 'PROCESSED',
csi_transaction_id = l_csi_trxn_rec.transaction_id
WHERE a.processing_status = l_literal1
AND a.transaction_line_id in (SELECT b.transaction_line_id
FROM csi_t_transaction_lines b
WHERE -- a.transaction_line_id = b.transaction_line_id AND -- Commented for Perf Bug 4311676
b.source_transaction_id = p_rma_line_rec.line_id
AND b.source_transaction_table = l_literal2);
debug('Txn details update failed');