[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT party_id ,
instance_party_id,
object_version_number
INTO px_inst_pa_rec.party_id,
px_inst_pa_rec.instance_party_id,
px_inst_pa_rec.pty_obj_version
FROM csi_i_parties
WHERE instance_id = px_inst_pa_rec.instance_id
AND relationship_type_code = 'OWNER'
AND sysdate between nvl(active_end_date,sysdate) and sysdate+1 ;
SELECT party_account_id,
ip_account_id,
object_version_number
INTO px_inst_pa_rec.account_id,
px_inst_pa_rec.ip_account_id,
px_inst_pa_rec.acct_obj_version
FROM csi_ip_accounts
WHERE instance_party_id = px_inst_pa_rec.instance_party_id
AND relationship_type_code = 'OWNER';
SELECT sub_type_id
INTO x_sub_type_id
FROM csi_source_ib_types -- SQL repository changes.
WHERE transaction_type_id = p_transaction_type_id
AND default_flag = 'Y';
SELECT *
INTO x_sub_type_rec
FROM csi_txn_sub_types
WHERE transaction_type_id = p_transaction_type_id
AND sub_type_id = p_sub_type_id;
SELECT inventory_item_id,
organization_id
INTO l_item_control_rec.inventory_item_id,
l_item_control_rec.organization_id
FROM mtl_material_transactions
WHERE transaction_id = p_mtl_txn_id;
SELECT serial_number_control_code,
--lot_control_code,
nvl(csi_utl_pkg.get_lot_ctrl_code(p_mtl_txn_id),lot_control_code) lot_control_code,-- Added for bug#14835893
revision_qty_control_code,
bom_item_type,
primary_uom_code,
base_item_id,
pick_components_flag
INTO l_item_control_rec.serial_control_code,
l_item_control_rec.lot_control_code,
l_item_control_rec.revision_control_code,
l_item_control_rec.bom_item_type,
l_item_control_rec.primary_uom_code,
l_item_control_rec.model_item_id,
l_item_control_rec.pick_components_flag
FROM mtl_system_items
WHERE inventory_item_id = l_item_control_rec.inventory_item_id
AND organization_id = l_item_control_rec.organization_id;
SELECT distinct serial_number_control_code
INTO l_tmp_id1
FROM mtl_system_items_b
WHERE inventory_item_id = l_item_control_rec.inventory_item_id;
SELECT trx_source_line_id
INTO l_rma_line_id
FROM mtl_material_transactions
WHERE transaction_id = p_mtl_txn_id;
SELECT nvl(oel.IB_OWNER,oeh.IB_OWNER),
nvl(oel.IB_INSTALLED_AT_LOCATION,oeh.IB_INSTALLED_AT_LOCATION),
nvl(oel.IB_CURRENT_LOCATION,oeh.IB_CURRENT_LOCATION),
nvl(oel.END_CUSTOMER_ID,oeh.END_CUSTOMER_ID),
nvl(oel.END_CUSTOMER_CONTACT_ID,oeh.END_CUSTOMER_CONTACT_ID),
nvl(oel.END_CUSTOMER_SITE_USE_ID,oeh.END_CUSTOMER_SITE_USE_ID),
oeh.sold_to_site_use_id
INTO x_partner_order_rec.IB_OWNER,
x_partner_order_rec.IB_INSTALLED_AT_LOCATION,
x_partner_order_rec.IB_CURRENT_LOCATION,
x_partner_order_rec.END_CUSTOMER_ID,
x_partner_order_rec.END_CUSTOMER_CONTACT_ID,
x_partner_order_rec.END_CUSTOMER_SITE_USE_ID,
x_partner_order_rec.SOLD_TO_SITE_USE_ID
FROM oe_order_lines_all oel,
oe_order_headers_all oeh
WHERE oel.line_id = l_rma_line_id
AND oeh.header_id = oel.header_id;
SELECT party_id,
status
INTO l_party_id,
l_account_status
FROM hz_cust_accounts
WHERE cust_account_id = x_partner_order_rec.END_CUSTOMER_ID;
SELECT trx_source_line_id
INTO x_src_order_rec.rma_line_id
FROM mtl_material_transactions
WHERE transaction_id = p_mtl_txn_id;
SELECT nvl(reference_line_id , fnd_api.g_miss_num)
INTO x_src_order_rec.original_order_line_id
FROM oe_order_lines_all
WHERE line_id = x_src_order_rec.rma_line_id;
SELECT nvl(oel.sold_to_org_id ,oeh.sold_to_org_id) ,
nvl(oel.ship_to_org_id,oeh.ship_to_org_id),
nvl(oel.invoice_to_org_id,oeh.invoice_to_org_id), -- Modified SQL to add headers and to also read Invoice to since that needs to be atleast required on RMA's - Self bug. shegde
ordered_quantity
INTO x_src_order_rec.customer_account_id,
l_ship_to_org,
l_invoice_to_org,
x_src_order_rec.original_order_qty
FROM oe_order_lines_all oel, oe_order_headers_all oeh
WHERE line_id = x_src_order_rec.rma_line_id
AND oeh.header_id = oel.header_id;
SELECT party_id
INTO x_src_order_rec.party_id
FROM hz_cust_accounts
WHERE cust_account_id = x_src_order_rec.customer_account_id;
SELECT HCAS.party_site_id
INTO x_src_order_rec.customer_location_id
FROM hz_cust_site_uses_all HCSU,
hz_cust_acct_sites_all HCAS
WHERE HCSU.site_use_id = l_cust_acct_site_use_id
AND HCAS.cust_acct_site_id = HCSU.cust_acct_site_id;
SELECT mmt.transaction_id transaction_id,
mmt.inventory_item_id inventory_item_id,
mmt.organization_id organization_id,
mmt.subinventory_code subinventory_code,
mmt.revision revision,
mmt.transaction_quantity transaction_quantity,
mmt.transaction_uom transaction_uom,
mmt.locator_id locator_id,
mmt.transaction_date transaction_date,
mut.serial_number serial_number,
mtln.lot_number lot_number,
msi.location_id subinv_location_id,
haou.location_id hr_location_id,
mmt.primary_quantity mmt_primary_quantity,
mtln.primary_quantity lot_primary_quantity,
mmt.trx_source_line_id oe_line_id,
mmt.transaction_type_id transaction_type_id,
mmt.creation_date creation_date -- bug 4026148
FROM hr_all_organization_units haou,
mtl_transaction_lot_numbers mtln,
mtl_unit_transactions mut,
mtl_secondary_inventories msi,
mtl_material_transactions mmt
WHERE mmt.transaction_id = p_mtl_txn_id
AND mmt.transaction_id = mut.transaction_id(+)
AND mmt.transaction_id = mtln.transaction_id(+)
AND mmt.subinventory_code = msi.secondary_inventory_name
AND mmt.organization_id = msi.organization_id
AND haou.organization_id = mmt.organization_id;
SELECT mmt.transaction_id transaction_id,
mmt.inventory_item_id inventory_item_id,
mmt.organization_id organization_id,
mmt.subinventory_code subinventory_code,
mmt.revision revision,
mmt.transaction_quantity transaction_quantity,
mmt.transaction_uom transaction_uom,
mmt.locator_id locator_id,
mmt.transaction_date transaction_date,
mut.serial_number serial_number,
mtln.lot_number lot_number,
msi.location_id subinv_location_id,
haou.location_id hr_location_id,
mmt.primary_quantity mmt_primary_quantity,
mtln.primary_quantity lot_primary_quantity,
mmt.trx_source_line_id oe_line_id,
mmt.transaction_type_id transaction_type_id,
mmt.creation_date creation_date -- bug 4026148
FROM hr_all_organization_units haou,
mtl_transaction_lot_numbers mtln,
mtl_unit_transactions mut,
mtl_secondary_inventories msi,
mtl_material_transactions mmt
WHERE mmt.transaction_id = p_mtl_txn_id
AND mmt.subinventory_code = msi.secondary_inventory_name
AND mmt.organization_id = msi.organization_id
AND mtln.transaction_id = mmt.transaction_id
AND mut.transaction_id = mtln.serial_transaction_id
AND mmt.organization_id = haou.organization_id;
SELECT mmt.transaction_id transaction_id,
mmt.inventory_item_id inventory_item_id,
mmt.organization_id organization_id,
mmt.subinventory_code subinventory_code,
mmt.revision revision,
mmt.transaction_quantity transaction_quantity,
mmt.transaction_uom transaction_uom,
mmt.locator_id locator_id,
mmt.transaction_date transaction_date,
null serial_number,
mtln.lot_number lot_number,
msi.location_id subinv_location_id,
haou.location_id hr_location_id,
mmt.primary_quantity mmt_primary_quantity,
mtln.primary_quantity lot_primary_quantity,
mmt.trx_source_line_id oe_line_id,
mmt.transaction_type_id transaction_type_id,
mmt.creation_date creation_date -- bug 4026148
FROM hr_all_organization_units haou,
mtl_transaction_lot_numbers mtln,
mtl_secondary_inventories msi,
mtl_material_transactions mmt
WHERE mmt.transaction_id = p_mtl_txn_id
AND mmt.transaction_id = mtln.transaction_id(+)
AND mmt.subinventory_code = msi.secondary_inventory_name
AND mmt.organization_id = msi.organization_id
AND haou.organization_id = mmt.organization_id;
/* Commented for now to avoid select on wip entities. Assumption being any instance being RMAed with location as WIP are created only as a result of a multi wip job.
Begin
SELECT entity_type
INTO l_wip_entity_type
FROM wip_entities
WHERE wip_entity_id = l_instance_header_tbl(1).wip_job_id
AND organization_id = l_instance_header_tbl(1).vld_organization_id;
SELECT start_quantity
INTO l_assm_qty
FROM wip_discrete_jobs
WHERE wip_entity_id = l_instance_header_tbl(1).wip_job_id
AND organization_id = l_instance_header_tbl(1).vld_organization_id;
SELECT master_organization_id
INTO l_master_organization_id
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT quantity
INTO l_instance_quantity
FROM csi_item_instances
WHERE instance_id = l_instances_tbl(l_ind).instance_id;
IF p_item_control_rec.serial_control_code = 1 THEN -- pass g_miss so that process txn API does not double update source instance
l_instances_tbl(l_ind).instance_id := fnd_api.g_miss_num;
for bug 3094905 . updated routines are as below */
PROCEDURE match_mtl_txn_for_txn_dtl(
px_txn_dtl_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_rec,
px_mtl_txn_tbl IN OUT NOCOPY mtl_txn_tbl,
px_tld_inst_tbl IN OUT NOCOPY tld_inst_tbl,
p_item_control_rec IN item_control_rec,
p_match_qty IN number,
x_match_flag OUT NOCOPY varchar2,
x_match_basis OUT NOCOPY varchar2,
x_return_status OUT NOCOPY varchar2)
IS
l_mtl_txn_rec mtl_txn_rec;
Select quantity
into l_inst_qty
from csi_item_instances
where instance_id = l_mtl_txn_rec.instance_id
and sysdate < nvl(active_end_date, sysdate+1); -- changed for bug#14364807
l_match_flag := 'Y'; -- This is done so that transaction details can be updated
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 while matching txn del with mtl txn.');
selects only the transaction sub type and source instance reference is not required
for bug 4570399*/
px_line_dtl_tbl(1).processing_status:='IN_PROCESS';
selects only the transaction sub type and source instance reference is not required
for bug 4570399*/
px_line_dtl_tbl(1).processing_status:='IN_PROCESS';
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 => px_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 while matching txn del with mtl txn.');
debug('Instance updates to be processed for Multiple serial control codes.. '||l_tld_inst_tbl.count);
l_upd_inst_tbl.delete;
SELECT object_version_number, active_end_date
INTO l_obj_ver_num, l_end_date
FROM csi_item_instances
WHERE instance_id = l_tld_inst_tbl(i).instance_id;
l_pi_ind := l_nsrl_qty; -- update as many srl instances as the rcpt qty
SELECT quantity, active_end_date, object_version_number
INTO l_quantity1, l_end_date, l_obj_ver_num
FROM csi_item_instances
WHERE instance_id = l_tld_inst_tbl(m).instance_id;
debug('Multiple Serial control codes. Instances for Final Update:'||l_upd_inst_tbl.count);
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_ext_attrib_values_tbl => l_u_ext_attrib_val_tbl,
p_party_tbl => l_u_party_tbl,
p_account_tbl => l_u_party_acct_tbl,
p_pricing_attrib_tbl => l_u_pricing_attribs_tbl,
p_org_assignments_tbl => l_u_org_units_tbl,
p_txn_rec => l_txn_rec,
p_asset_assignment_tbl => l_u_inst_asset_tbl,
x_instance_id_lst => l_u_inst_id_lst,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
SELECT party_id ,
instance_party_id,
object_version_number
INTO l_cur_owner_party_id,
l_owner_pty_ip_id,
l_owner_pty_obj_ver_num
FROM csi_i_parties
WHERE instance_id = l_instances_tbl(i_ind).instance_id
AND relationship_type_code = 'OWNER';
SELECT party_account_id,
ip_account_id,
object_version_number
INTO l_cur_owner_acct_id,
l_owner_acct_ipa_id,
l_owner_acct_obj_ver_num
FROM csi_ip_accounts
WHERE instance_party_id = l_owner_pty_ip_id
AND relationship_type_code = 'OWNER';
SELECT object_version_number
INTO l_chg_instance_rec.object_version_number
FROM csi_item_instances
WHERE instance_id = l_instances_tbl(i_ind).instance_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_txn_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_txn_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 quantity, active_end_date
INTO l_quantity2, l_active_end_date
FROM csi_item_instances
WHERE instance_id = l_instances_tbl(i_ind).instance_id;
SELECT object_version_number
INTO l_exp_instance_rec.object_version_number
FROM csi_item_instances
WHERE instance_id = l_instances_tbl(i_ind).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_exp_instance_rec,
p_ext_attrib_values_tbl => l_u_ext_attrib_val_tbl,
p_party_tbl => l_u_party_tbl,
p_account_tbl => l_u_party_acct_tbl,
p_pricing_attrib_tbl => l_u_pricing_attribs_tbl,
p_org_assignments_tbl => l_u_org_units_tbl,
p_txn_rec => l_txn_rec,
p_asset_assignment_tbl => l_u_inst_asset_tbl,
x_instance_id_lst => l_u_inst_id_lst,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
UPDATE csi_t_txn_line_details a
SET error_code = NULL,
error_explanation = NULL ,
processing_status = 'PROCESSED'
WHERE a.processing_status = l_literal1
AND a.source_transaction_flag = 'Y'
AND a.inventory_item_id = l_item_control_rec.inventory_item_id
AND a.transaction_line_id in (SELECT 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 = l_rma_order_rec.source_line_id
AND b.source_transaction_table = l_literal2 );
debug('Failed to Update the Transaction Details data');
SELECT line_id,
header_id
INTO l_split_txn_line_rec.source_transaction_id,
l_split_txn_line_rec.source_txn_header_id
FROM oe_order_lines_all
WHERE split_from_line_id = l_rma_order_rec.source_line_id --l_src_mtl_txn_tbl(1).oe_line_id
AND header_id = l_rma_order_rec.source_header_id ;
SELECT transaction_id,
transaction_date,
transaction_type_id,
trx_source_line_id
INTO x_mtl_trx_type.transaction_id,
x_mtl_trx_type.transaction_date,
x_mtl_trx_type.transaction_type_id,
x_mtl_trx_type.source_line_id
FROM mtl_material_transactions
WHERE transaction_id = p_transaction_id;
SELECT ooh.header_id,
ooh.order_number,
ool.line_id,
ool.line_number||'.'||ool.shipment_number
INTO x_mtl_trx_type.source_header_id,
x_mtl_trx_type.source_header_ref,
x_mtl_trx_type.source_line_id,
x_mtl_trx_type.source_line_ref
FROM oe_order_headers_all ooh,
oe_order_lines_all ool
WHERE ool.line_id = x_mtl_trx_type.source_line_id
AND ool.header_id = ooh.header_id;