[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT sub_type_id
INTO x_sub_type_id
FROM csi_txn_sub_types
WHERE transaction_type_id = p_transaction_type_id
AND default_flag = 'Y';
SELECT inventory_item_id ,
link_to_line_id ,
org_id
INTO l_inventory_item_id ,
l_next_parent_line_id,
l_org_id
FROM oe_order_lines_all
WHERE line_id = l_parent_line_id;
SELECT nvl(msi.comms_nl_trackable_flag, 'N')
INTO l_ib_trackable_flag
FROM mtl_system_items msi
WHERE msi.inventory_item_id = l_inventory_item_id
AND msi.organization_id = l_organization_id;
SELECT line_id
FROM oe_order_lines_all
WHERE link_to_line_id = p_parent_line_id
ORDER BY line_number, shipment_number;
l_line_tbl_nxt_lvl.delete;
SELECT nvl(msi.comms_nl_trackable_flag,'N')
INTO l_ib_trackable_flag
FROM mtl_system_items msi
WHERE msi.inventory_item_id = l_line_tbl(l_ind).inventory_item_id
AND msi.organization_id = l_organization_id;
l_line_tbl.DELETE;
SELECT serial_number_control_code,
-- lot_control_code,
nvl(csi_utl_pkg.get_lot_ctrl_code(p_transaction_id),lot_control_code) lot_control_code,-- Added for bug#14835893
location_control_code,
revision_qty_control_code,
comms_nl_trackable_flag,
shippable_item_flag,
inventory_item_flag,
stock_enabled_flag,
bom_item_type,
pick_components_flag,
base_item_id,
primary_uom_code
INTO x_item_attrib_rec.serial_control_code,
x_item_attrib_rec.lot_control_code,
x_item_attrib_rec.locator_control_code,
x_item_attrib_rec.revision_control_code,
x_item_attrib_rec.ib_trackable_flag,
x_item_attrib_rec.shippable_flag,
x_item_attrib_rec.inv_item_flag,
x_item_attrib_rec.stockable_flag,
x_item_attrib_rec.bom_item_type,
x_item_attrib_rec.pick_components_flag,
x_item_attrib_rec.model_item_id,
x_item_attrib_rec.primary_uom_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
select 'X'
into l_exists
from all_tab_columns
where table_name = 'MTL_SYSTEM_ITEMS_B'
and column_name = 'IB_ITEM_INSTANCE_CLASS'
and OWNER = l_oracle_schema
and rownum < 2;
select 'X'
into l_exists
from user_synonyms syn,
all_tab_columns col
where syn.synonym_name = 'MTL_SYSTEM_ITEMS_B'
and col.column_name = 'IB_ITEM_INSTANCE_CLASS'
and col.OWNER = l_oracle_schema
AND col.owner = syn.table_owner
AND col.table_name = syn.table_name
and rownum < 2;
l_sql_stmt := 'select ib_item_instance_class from MTL_SYSTEM_ITEMS_B '||
'where inventory_item_id = :item_id '||
'and organization_id = :vld_org_id';
select 'X'
into l_exists
from all_tab_columns
where table_name = 'MTL_SYSTEM_ITEMS_B'
and column_name = 'CONFIG_MODEL_TYPE'
and OWNER = l_oracle_schema
and rownum < 2;
select 'X'
into l_exists
from user_synonyms syn,
all_tab_columns col
where syn.synonym_name = 'MTL_SYSTEM_ITEMS_B'
and col.column_name = 'CONFIG_MODEL_TYPE'
and col.OWNER = l_oracle_schema
AND col.owner = syn.table_owner
AND col.table_name = syn.table_name
and rownum < 2;
l_sql_stmt := 'select config_model_type from MTL_SYSTEM_ITEMS_B '||
'where inventory_item_id = :item_id '||
'and organization_id = :vld_org_id';
SELECT nvl(negative_inv_receipt_code,2)
INTO x_item_attrib_rec.negative_balances_code
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT pricing_context,
pricing_attribute1,
pricing_attribute2,
pricing_attribute3,
pricing_attribute4,
pricing_attribute5,
pricing_attribute6,
pricing_attribute7,
pricing_attribute8,
pricing_attribute9,
pricing_attribute10,
pricing_attribute11,
pricing_attribute12,
pricing_attribute13,
pricing_attribute14,
pricing_attribute15,
pricing_attribute16,
pricing_attribute17,
pricing_attribute18,
pricing_attribute19,
pricing_attribute20,
pricing_attribute21,
pricing_attribute22,
pricing_attribute23,
pricing_attribute24,
pricing_attribute25,
pricing_attribute26,
pricing_attribute27,
pricing_attribute28,
pricing_attribute29,
pricing_attribute30,
pricing_attribute31,
pricing_attribute32,
pricing_attribute33,
pricing_attribute34,
pricing_attribute35,
pricing_attribute36,
pricing_attribute37,
pricing_attribute38,
pricing_attribute39,
pricing_attribute40,
pricing_attribute41,
pricing_attribute42,
pricing_attribute43,
pricing_attribute44,
pricing_attribute45,
pricing_attribute46,
pricing_attribute47,
pricing_attribute48,
pricing_attribute49,
pricing_attribute50,
pricing_attribute51,
pricing_attribute52,
pricing_attribute53,
pricing_attribute54,
pricing_attribute55,
pricing_attribute56,
pricing_attribute57,
pricing_attribute58,
pricing_attribute59,
pricing_attribute60,
pricing_attribute61,
pricing_attribute62,
pricing_attribute63,
pricing_attribute64,
pricing_attribute65,
pricing_attribute66,
pricing_attribute67,
pricing_attribute68,
pricing_attribute69,
pricing_attribute70,
pricing_attribute71,
pricing_attribute72,
pricing_attribute73,
pricing_attribute74,
pricing_attribute75,
pricing_attribute76,
pricing_attribute77,
pricing_attribute78,
pricing_attribute79,
pricing_attribute80,
pricing_attribute81,
pricing_attribute82,
pricing_attribute83,
pricing_attribute84,
pricing_attribute85,
pricing_attribute86,
pricing_attribute87,
pricing_attribute88,
pricing_attribute89,
pricing_attribute90,
pricing_attribute91,
pricing_attribute92,
pricing_attribute93,
pricing_attribute94,
pricing_attribute95,
pricing_attribute96,
pricing_attribute97,
pricing_attribute98,
pricing_attribute99,
pricing_attribute100
FROM oe_order_price_attribs
WHERE line_id = p_line_id ;
SELECT source_transaction_type_id,
transaction_line_id,
source_transaction_table,
config_session_hdr_id,
config_session_item_id,
config_session_rev_num
INTO x_txn_line_rec.source_transaction_type_id,
x_txn_line_rec.transaction_line_id,
x_txn_line_rec.source_transaction_table,
x_txn_line_rec.config_session_hdr_id,
x_txn_line_rec.config_session_item_id,
x_txn_line_rec.config_session_rev_num
FROM csi_t_transaction_lines
WHERE transaction_line_id = l_g_line_dtl_tbl(1).transaction_line_id;
SELECT config_header_id config_session_hdr_id,
config_rev_nbr config_session_rev_num,
configuration_id config_session_item_id
FROM oe_order_lines_all
WHERE header_id = p_header_id;
SELECT processing_status
INTO l_processing_status
FROM csi_t_transaction_lines
WHERE transaction_line_id = l_rltns_tbl(l_ind).transaction_line_id;
SELECT 'Y' INTO l_dummy
FROM csi_item_instances
WHERE config_inst_hdr_id = l_rltns_tbl(l_ind).obj_config_inst_hdr_id
--AND config_inst_rev_num = l_rltns_tbl(l_ind).obj_config_inst_rev_num
AND config_inst_item_id = l_rltns_tbl(l_ind).obj_config_inst_item_id;
SELECT 'Y' INTO l_dummy
FROM CSI_T_TXN_LINE_DETAILS
WHERE txn_line_detail_id = l_rltns_tbl(l_ind).object_id
AND instance_id IS NOT NULL;
SELECT 'Y' INTO l_dummy
FROM csi_item_instances
WHERE config_inst_hdr_id = l_rltns_tbl(l_ind).sub_config_inst_hdr_id
--AND config_inst_rev_num = l_rltns_tbl(l_ind).sub_config_inst_rev_num
AND config_inst_item_id = l_rltns_tbl(l_ind).sub_config_inst_item_id;
SELECT 'Y' INTO l_dummy
FROM CSI_T_TXN_LINE_DETAILS
WHERE txn_line_detail_id = l_rltns_tbl(l_ind).subject_id
AND instance_id IS NOT NULL;
SELECT instance_id
INTO l_tld_tbl(l_ind).instance_id
FROM csi_item_instances
WHERE config_inst_hdr_id = l_tld_tbl(l_ind).config_inst_hdr_id
AND config_inst_item_id = l_tld_tbl(l_ind).config_inst_item_id;
SELECT instance_id
INTO l_tld_tbl(l_ind).instance_id
FROM csi_item_instances
WHERE config_inst_hdr_id = l_tld_tbl(l_ind).config_inst_hdr_id
AND config_inst_item_id = l_tld_tbl(l_ind).config_inst_item_id;
SELECT * INTO l_line_rec
FROM oe_order_lines_all
WHERE line_id = p_order_line_id;
SELECT * INTO l_hdr_rec
FROM oe_order_headers_all
WHERE header_id = l_line_rec.header_id;
SELECT transaction_date
INTO x_source_line_rec.fulfilled_date
FROM mtl_material_transactions
WHERE transaction_type_id = l_drop_ship_txn_type_id
AND trx_source_line_id = l_line_rec.line_id
AND rownum = 1;
SELECT party_id
INTO x_source_line_rec.owner_party_id
FROM hz_cust_accounts
WHERE cust_account_id = x_source_line_rec.owner_party_account_id;
SELECT hcas.party_site_id
INTO x_source_line_rec.ship_to_party_site_id
FROM hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas
WHERE hcsu.site_use_id = x_source_line_rec.ib_current_loc_id -- ship_to_address_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id;
SELECT hcas.party_site_id
INTO x_source_line_rec.install_to_party_site_id
FROM hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas
WHERE hcsu.site_use_id = x_source_line_rec.ib_install_loc_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id;
SELECT hzr.subject_id
INTO x_source_line_rec.ship_to_contact_party_id
FROM hz_relationships hzr,
hz_cust_account_roles hzar
WHERE hzar.cust_account_role_id = x_source_line_rec.ship_to_contact_id
AND hzr.party_id = hzar.party_id
AND hzr.subject_table_name = 'HZ_PARTIES'
AND hzr.object_table_name = 'HZ_PARTIES'
AND hzr.directional_flag = 'F';
SELECT hzr.subject_id
INTO x_source_line_rec.bill_to_contact_party_id
FROM hz_relationships hzr,
hz_cust_account_roles hzar
WHERE hzar.cust_account_role_id = x_source_line_rec.bill_to_contact_id
AND hzr.party_id = hzar.party_id
AND hzr.subject_table_name = 'HZ_PARTIES'
AND hzr.object_table_name = 'HZ_PARTIES'
AND hzr.directional_flag = 'F';
SELECT *
FROM oke_k_deliverables_vl
WHERE deliverable_id = p_dlv_id;
SELECT source_header_id,
source_line_id,
source_line_number,
org_id,
organization_id,
customer_id,
ship_to_site_use_id,
ship_to_contact_id,
cust_po_number,
inventory_item_id,
revision,
src_requested_quantity,
src_requested_quantity_uom,
date_scheduled,
top_model_line_id,
ato_line_id
FROM wsh_delivery_details_ob_grp_v
WHERE delivery_detail_id = p_src_line_id;
SELECT contract_number
INTO x_source_header_rec.source_header_ref
FROM okc_k_headers_all_b --fix for bug5358612
WHERE id = wsh_rec.source_header_id;
SELECT party_id
INTO x_source_line_rec.owner_party_id
FROM hz_cust_accounts
WHERE cust_account_id = x_source_line_rec.owner_party_account_id;
SELECT hcas.party_site_id
INTO x_source_line_rec.ship_to_party_site_id
FROM hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas
WHERE hcsu.site_use_id = x_source_line_rec.ship_to_address_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id;
SELECT hzr.subject_id
INTO x_source_line_rec.ship_to_contact_party_id
FROM hz_relationships hzr,
hz_cust_account_roles hzar
WHERE hzar.cust_account_role_id = x_source_line_rec.ship_to_contact_id
AND hzr.party_id = hzar.party_id
AND hzr.subject_table_name = 'HZ_PARTIES'
AND hzr.object_table_name = 'HZ_PARTIES'
AND hzr.directional_flag = 'F';
SELECT obj_config_inst_hdr_id inst_hdr_id,
obj_config_inst_item_id inst_item_id,
obj_config_inst_rev_num inst_rev_num
FROM csi_t_ii_relationships
WHERE sub_config_inst_hdr_id = p_instance_key.inst_hdr_id
AND sub_config_inst_item_id = p_instance_key.inst_item_id
AND sub_config_inst_rev_num = p_instance_key.inst_rev_num
UNION
SELECT sub_config_inst_hdr_id inst_hdr_id,
sub_config_inst_item_id inst_item_id,
sub_config_inst_rev_num inst_rev_num
FROM csi_t_ii_relationships
WHERE obj_config_inst_hdr_id = p_instance_key.inst_hdr_id
AND obj_config_inst_item_id = p_instance_key.inst_item_id
AND obj_config_inst_rev_num = p_instance_key.inst_rev_num;
SELECT location_id,
location_type_code
INTO l_location_id,
l_location_type_code
FROM csi_t_txn_line_details
WHERE config_inst_hdr_id = partner_rec.inst_hdr_id
AND config_inst_item_id = partner_rec.inst_item_id
AND config_inst_rev_num = partner_rec.inst_rev_num;
SELECT location_id ,
location_type_code
INTO l_location_id,
l_location_type_code
FROM csi_item_instances
WHERE config_inst_hdr_id = partner_rec.inst_hdr_id
AND config_inst_item_id = partner_rec.inst_item_id;
SELECT LAST_VLD_ORGANIZATION_ID
INTO l_vld_organization_id
FROM csi_item_instances
WHERE instance_id = p_txn_line_dtl_rec.instance_id;
SELECT inv_master_organization_id
INTO l_inv_master_organization_id
FROM csi_item_instances
WHERE instance_id = p_txn_line_dtl_rec.instance_id;
SELECT instance_usage_code
INTO l_instance_usage_code
FROM csi_item_instances
WHERE instance_id = l_i_rec.instance_id;
SELECT instance_party_id
INTO p_txn_party_tbl(l_pc_ind).contact_party_id
FROM csi_i_parties
WHERE instance_id = l_i_rec.instance_id
AND relationship_type_code = p_txn_party_tbl(l_p_ind).relationship_type_code
AND ((active_end_date is null ) OR
(active_end_date > sysdate));
SELECT attribute_value_id
INTO l_eav_tbl(eav_ind).attribute_value_id
FROM csi_iea_values
WHERE attribute_id = p_txn_eav_tbl(l_eav_ind).attribute_source_id
AND instance_id = l_i_rec.instance_id;
SELECT * INTO l_csi_param_rec
FROM csi_install_parameters;
SELECT object_version_number,
serial_number ,
active_end_date
INTO px_instance_rec.object_version_number,
px_instance_rec.serial_number,
l_active_end_date
FROM csi_item_instances
WHERE instance_id = px_instance_rec.instance_id;
SELECT instance_party_id,
object_version_number
INTO px_party_tbl(l_p_ind).instance_party_id,
px_party_tbl(l_p_ind).object_version_number
FROM csi_i_parties
WHERE instance_id = px_instance_rec.instance_id
AND party_source_table = px_party_tbl(l_p_ind).party_source_table
AND relationship_type_code = px_party_tbl(l_p_ind).relationship_type_code;
SELECT instance_party_id,
object_version_number
INTO px_party_tbl(l_p_ind).instance_party_id,
px_party_tbl(l_p_ind).object_version_number
FROM csi_i_parties
WHERE instance_id = px_instance_rec.instance_id
AND party_id = px_party_tbl(l_p_ind).party_id
AND party_source_table = px_party_tbl(l_p_ind).party_source_table
AND relationship_type_code = px_party_tbl(l_p_ind).relationship_type_code;
SELECT ip_account_id,
object_version_number
INTO px_party_acct_tbl(l_pa_ind).ip_account_id,
px_party_acct_tbl(l_pa_ind).object_version_number
FROM csi_ip_accounts
WHERE instance_party_id = px_party_acct_tbl(l_pa_ind).instance_party_id
AND relationship_type_code = px_party_acct_tbl(l_pa_ind).relationship_type_code;
SELECT ip_account_id,
object_version_number
INTO px_party_acct_tbl(l_pa_ind).ip_account_id,
px_party_acct_tbl(l_pa_ind).object_version_number
FROM csi_ip_accounts
WHERE instance_party_id = px_party_acct_tbl(l_pa_ind).instance_party_id
AND party_account_id = px_party_acct_tbl(l_pa_ind).party_account_id
AND relationship_type_code = px_party_acct_tbl(l_pa_ind).relationship_type_code;
SELECT object_version_number
INTO px_party_acct_tbl(l_pa_ind).object_version_number
FROM csi_ip_accounts
WHERE ip_account_id = px_party_acct_tbl(l_pa_ind).ip_account_id;
SELECT object_version_number
INTO px_party_tbl(l_p_ind).object_version_number
FROM csi_i_parties
WHERE instance_party_id = px_party_tbl(l_p_ind).instance_party_id;
SELECT instance_ou_id,
object_version_number
INTO px_org_units_tbl(l_ind).instance_ou_id,
px_org_units_tbl(l_ind).object_version_number
FROM csi_i_org_assignments
WHERE instance_id = px_org_units_tbl(l_ind).instance_id
AND operating_unit_id = px_org_units_tbl(l_ind).operating_unit_id
AND relationship_type_code = px_org_units_tbl(l_ind).relationship_type_code;
SELECT object_version_number
INTO px_org_units_tbl(l_ind).instance_ou_id
FROM csi_i_org_assignments
WHERE instance_ou_id = px_org_units_tbl(l_ind).instance_ou_id;
SELECT object_version_number
INTO px_eav_tbl(l_ind).object_version_number
FROM csi_iea_values
WHERE attribute_value_id = px_eav_tbl(l_ind).attribute_value_id;
SELECT pricing_attribute_id ,
object_version_number
INTO px_pricing_tbl(l_ind).pricing_attribute_id ,
px_pricing_tbl(l_ind).object_version_number
FROM csi_i_pricing_attribs
WHERE instance_id = px_instance_rec.instance_id
AND pricing_context = px_pricing_tbl(l_ind).pricing_context ;
SELECT object_version_number
INTO px_pricing_tbl(l_ind).object_version_number
FROM csi_i_pricing_attribs
WHERE pricing_attribute_id = px_pricing_tbl(l_ind).pricing_attribute_id;
SELECT instance_id,active_end_date
INTO l_sub_instance_id,l_sub_end_date --Fix for bug 5956280
FROM csi_item_instances
WHERE config_inst_hdr_id = l_r_tbl(l_ii_ind).sub_config_inst_hdr_id
--AND config_inst_rev_num = l_r_tbl(l_ii_ind).sub_config_inst_rev_num
AND config_inst_item_id = l_r_tbl(l_ii_ind).sub_config_inst_item_id;
SELECT instance_id,active_end_date
INTO l_sub_instance_id,l_sub_end_date --Fix for bug 5956280
FROM CSI_T_TXN_LINE_DETAILS
WHERE txn_line_detail_id = l_r_tbl(l_ii_ind).subject_id;
SELECT instance_id,active_end_date
INTO l_obj_instance_id,l_obj_end_date --Fix for bug 5956280
FROM csi_item_instances
WHERE config_inst_hdr_id = l_r_tbl(l_ii_ind).obj_config_inst_hdr_id
--AND config_inst_rev_num = l_r_tbl(l_ii_ind).obj_config_inst_rev_num
AND config_inst_item_id = l_r_tbl(l_ii_ind).obj_config_inst_item_id;
SELECT instance_id,active_end_date
INTO l_obj_instance_id,l_obj_end_date --Fix for bug 5956280
FROM CSI_T_TXN_LINE_DETAILS
WHERE txn_line_detail_id = l_r_tbl(l_ii_ind).object_id;
SELECT relationship_id,
object_version_number,
active_end_date
INTO l_r_tbl(l_ii_ind).csi_inst_relationship_id,
l_r_tbl(l_ii_ind).object_version_number,
l_rel_end_date
FROM csi_ii_relationships
WHERE subject_id = l_sub_instance_id
AND object_id = l_obj_instance_id;
debug(' update_ii_rltns :'||x_u_ii_rltns_tbl.count);
PROCEDURE update_td_status(
p_txn_line_rec IN csi_t_datastructures_grp.txn_line_rec,
p_txn_line_dtl_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl,
p_processing_status IN varchar2,
x_return_status OUT NOCOPY varchar2)
IS
l_tl_rec csi_t_datastructures_grp.txn_line_rec;
api_log('update_td_status');
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_tl_rec,
p_txn_line_detail_tbl => l_td_tbl,
px_txn_party_detail_tbl => l_pd_tbl,
px_txn_pty_acct_detail_tbl => l_pa_tbl,
px_txn_org_assgn_tbl => l_oa_tbl,
px_txn_ext_attrib_vals_tbl => l_ea_tbl,
px_txn_ii_rltns_tbl => l_ir_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
END update_td_status;
SELECT active_end_date
INTO l_rlt_active_end_date
FROM csi_ii_relationships
WHERE relationship_id=
(SELECT max(relationship_id)
FROM csi_ii_relationships
WHERE subject_id = px_txn_line_dtl_tbl(l_td_ind).instance_id
AND relationship_type_code = l_rel_type_code); -- srramakr added
SELECT *
INTO l_sub_type_rec
FROM csi_txn_sub_types
WHERE transaction_type_id = px_txn_line_rec.source_transaction_type_id
AND sub_type_id = px_txn_line_dtl_tbl(l_td_ind).sub_type_id;
/* moved this update_relationship set above to address the issue with
expire the relationship first before processing the children. The
core API while expiring the parent instance automatically end dates
the child instances. We are stopping that by expiring the relationsip
first thus by hiding the shildren from the API.
*/
IF px_txn_ii_rltns_tbl.COUNT > 0 THEN
build_relationship_tbl(
p_txn_ii_rltns_tbl => px_txn_ii_rltns_tbl,
p_txn_line_dtl_tbl => px_txn_line_dtl_tbl,
x_c_ii_rltns_tbl => l_c_ii_rltns_tbl,
x_u_ii_rltns_tbl => l_u_ii_rltns_tbl,
x_return_status => l_return_status);
/* update the existing relationships */
IF l_u_ii_rltns_tbl.COUNT > 0 THEN
/* to stamp the source transaction date as end date */
FOR l_ind IN l_u_ii_rltns_tbl.FIRST .. l_u_ii_rltns_tbl.LAST
LOOP
debug(' Relationship_ID :'|| l_u_ii_rltns_tbl(l_ind).relationship_id);
p_api_name => 'update_relationship');
csi_ii_relationships_pub.update_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_relationship_tbl => l_u_ii_rltns_tbl,
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);
SELECT 'N'
INTO l_item_instance_expired
FROM csi_item_instances
WHERE instance_id = l_u_instance_rec.instance_id
AND ((active_end_date IS NULL) OR (active_end_date >= SYSDATE));
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_txn_rec => l_csi_txn_rec,
p_instance_rec => l_u_instance_rec,
p_party_tbl => l_u_party_tbl,
p_account_tbl => l_u_party_acct_tbl,
p_org_assignments_tbl => l_u_org_units_tbl,
p_ext_attrib_values_tbl => l_u_eav_tbl,
p_pricing_attrib_tbl => l_u_pricing_tbl,
p_asset_assignment_tbl => l_u_inst_asset_tbl,
x_instance_id_lst => l_u_inst_id_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
/* Setting values for Explode BOM in UPDATE */
IF l_u_instance_rec.quantity = 1 THEN
l_bom_std_item_rec.instance_id := l_u_instance_rec.instance_id ;
update_td_status(
p_txn_line_rec => px_txn_line_rec,
p_txn_line_dtl_tbl => px_txn_line_dtl_tbl,
p_processing_status => 'PROCESSED',
x_return_status => l_return_status);
SELECT 'Y' INTO l_td_found
FROM csi_t_transaction_lines
WHERE source_transaction_table = 'CONFIGURATOR'
AND config_session_hdr_id = p_config_session_key.session_hdr_id
AND config_session_rev_num = p_config_session_key.session_rev_num
AND config_session_item_id = p_config_session_key.session_item_id;
SELECT model_instantiation_type
INTO l_mdl_instantiation_type
FROM cz_config_items_v
WHERE config_hdr_id = p_config_session_key.session_hdr_id
AND config_rev_nbr = p_config_session_key.session_rev_num
AND config_item_id = p_config_session_key.session_item_id;
SELECT 'Y'
INTO l_bom_found
FROM bom_bill_of_materials
WHERE assembly_item_id = p_std_item_rec.inventory_item_id
AND organization_id = p_std_item_rec.vld_organization_id
AND alternate_bom_designator is NULL; -- added for bug 2443204. Checking only primary bom when multiple could have been defined.
SELECT 'Y'
INTO l_found_child
FROM csi_ii_relationships
WHERE object_id = p_instance_id
AND relationship_type_code = 'COMPONENT-OF';
SELECT mmt.trx_source_line_id trx_source_line_id,
mmt.inventory_item_id inventory_item_id,
mmt.organization_id organization_id,
mmt.revision revision,
mmt.subinventory_code subinventory_code,
mmt.locator_id locator_id,
null lot_number,
mut.serial_number serial_number,
abs(mmt.transaction_quantity) transaction_quantity,
mmt.transaction_uom transaction_uom,
mmt.transaction_date transaction_date,
msi.lot_control_code lot_control_code,
msi.serial_number_control_code serial_control_code,
msi.primary_uom_code primary_uom,
abs(mmt.primary_quantity) primary_quantity,
mmt.transaction_type_id transaction_type_id,
mmt.transaction_action_id transaction_action_id
FROM mtl_system_items msi,
mtl_unit_transactions mut,
mtl_material_transactions mmt
WHERE mmt.transaction_id = p_txn_id
AND mmt.transaction_id = mut.transaction_id(+)
AND msi.organization_id = mmt.organization_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND msi.lot_control_code = 1 -- no lot case
UNION
SELECT mmt.trx_source_line_id trx_source_line_id,
mmt.inventory_item_id inventory_item_id,
mmt.organization_id organization_id,
mmt.revision revision,
mmt.subinventory_code subinventory_code,
mmt.locator_id locator_id,
mtln.lot_number lot_number,
mut.serial_number serial_number,
abs(mtln.transaction_quantity) transaction_quantity,
mmt.transaction_uom transaction_uom,
mmt.transaction_date transaction_date,
msi.lot_control_code lot_control_code,
msi.serial_number_control_code serial_control_code,
msi.primary_uom_code primary_uom,
abs(mtln.primary_quantity) primary_quantity,
mmt.transaction_type_id transaction_type_id,
mmt.transaction_action_id transaction_action_id
FROM mtl_system_items msi,
mtl_unit_transactions mut,
mtl_transaction_lot_numbers mtln,
mtl_material_transactions mmt
WHERE mmt.transaction_id = p_txn_id
AND mmt.transaction_id = mtln.transaction_id(+)
AND mtln.serial_transaction_id = mut.transaction_id(+)
AND msi.organization_id = mmt.organization_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND msi.lot_control_code = 2; -- lot control case
l_create_update_flag varchar2(1);
l_create_update_flag := 'U';
l_create_update_flag := 'C';
px_mtl_txn_tbl(l_ind).create_update_flag := l_create_update_flag;
SELECT location_id
INTO l_location_id
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND secondary_inventory_name = p_subinventory_code;
SELECT location_id
INTO l_location_id
FROM hr_organization_units
WHERE organization_id = p_organization_id;
SELECT object_version_number,
quantity - p_quantity,
active_end_date
INTO l_instance_rec.object_version_number,
l_instance_rec.quantity,
l_instance_rec.active_end_date
FROM csi_item_instances
WHERE instance_id = l_instance_rec.instance_id;
SELECT instance_status_id
INTO l_instance_rec.instance_status_id
FROM csi_instance_statuses
WHERE name = fnd_profile.value('csi_default_instance_status');
p_api_name => 'update_item_instance',
p_pkg_name => 'csi_item_instance_pub');
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_instance_rec,
p_ext_attrib_values_tbl => l_ext_attrib_val_tbl,
p_party_tbl => l_party_tbl,
p_account_tbl => l_party_acct_tbl,
p_pricing_attrib_tbl => l_pricing_attribs_tbl,
p_org_assignments_tbl => l_org_units_tbl,
p_txn_rec => px_txn_rec,
p_asset_assignment_tbl => l_inst_asset_tbl,
x_instance_id_lst => l_inst_id_lst,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
IF p_mtl_txn_tbl(l_ind).create_update_flag = 'C' THEN
IF nvl(p_mtl_txn_tbl(l_ind).instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
decrement_inventory_instnace(
p_instance_id => p_mtl_txn_tbl(l_ind).instance_id,
p_quantity => p_mtl_txn_tbl(l_ind).primary_quantity,
px_txn_rec => px_txn_rec,
x_return_status => l_return_status);
SELECT owner_party_account_id,
owner_party_id
INTO l_owner_party_acct_id,
l_owner_party_id
FROM csi_item_instances
WHERE instance_id = p_instance_id;
SELECT party_source_table
INTO l_party_source_table
FROM csi_i_parties
WHERE instance_id = p_instance_id
AND relationship_type_code = 'OWNER';
SELECT instance_id
INTO l_td_tbl(l_td_ind).instance_id
FROM csi_item_instances
WHERE config_inst_hdr_id = l_td_tbl(l_td_ind).config_inst_hdr_id
AND config_inst_item_id = l_td_tbl(l_td_ind).config_inst_item_id;