[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(src_change_owner, 'N'),
src_change_owner_to_code,
src_status_id,
nvl(src_reference_reqd, 'N'),
nvl(src_return_reqd,'N'),
nvl(non_src_change_owner, 'N'),
non_src_change_owner_to_code,
non_src_status_id,
nvl(non_src_reference_reqd,'N'),
nvl(non_src_return_reqd,'N')
INTO l_sub_type_rec.src_change_owner,
l_sub_type_rec.src_change_owner_code,
l_sub_type_rec.src_status_id,
l_sub_type_rec.src_reference_reqd,
l_sub_type_rec.src_return_reqd,
l_sub_type_rec.nsrc_change_owner,
l_sub_type_rec.nsrc_change_owner_code,
l_sub_type_rec.nsrc_status_id,
l_sub_type_rec.nsrc_reference_reqd,
l_sub_type_rec.nsrc_return_reqd
FROM csi_ib_txn_types
WHERE sub_type_id = p_sub_type_id;
SELECT quantity,
inventory_item_id
INTO l_u_instance_rec.quantity,
l_u_instance_rec.inventory_item_id
FROM csi_item_instances
WHERE instance_id = l_u_instance_rec.instance_id;
l_cps_tbl.delete;
SELECT serial_number_control_code,
Inventory_item_id,
organization_id,
bom_item_type
INTO l_ship_order_line_rec.serial_code,
l_ship_order_line_rec.inv_item_id ,
l_ship_order_line_rec.inv_org_id,
l_ship_order_line_rec.bom_item_type
FROM mtl_system_items
WHERE inventory_item_id = l_u_instance_rec.inventory_item_id
AND organization_id = l_order_line_rec.ship_from_org_id;
l_auto_split_instances.delete;
l_cps_tbl.delete;
SELECT processing_status
INTO l_processing_status
FROM csi_t_transaction_lines
WHERE source_transaction_table = 'OE_ORDER_LINES_ALL'
AND source_transaction_id = p_parent_line_id;
SELECT object_version_number,
instance_usage_code,
location_type_code,
quantity
INTO l_u_instance_rec.object_version_number,
l_instance_usage_code ,
l_location_type_code,
l_quantity
FROM csi_item_instances
WHERE instance_id = l_u_instance_rec.instance_id;
SELECT object_id
INTO l_parent_instance_id
FROM csi_ii_relationships
WHERE subject_id = p_instance_rec.instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
AND nvl(active_end_date, sysdate+1);
debug(' Inside API :csi_item_instance_pub.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 => px_csi_txn_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 location_type_code,
instance_usage_code
INTO l_instance_tbl(inst_ind).location_type_code,
l_instance_tbl(inst_ind).instance_usage_code
FROM csi_item_instances
WHERE instance_id = l_instance_tbl(inst_ind).instance_id;
SELECT quantity,
object_version_number
INTO l_instance_quantity,
l_instance_ovn
FROM csi_item_instances
WHERE instance_id = p_instance_id;
SELECT object_id
INTO l_parent_instance_id
FROM csi_ii_relationships
WHERE subject_id = l_instance_rec.instance_id
AND relationship_type_code = 'COMPONENT-OF';
debug(' loop thru to split. allocate and update');
l_c_parties_tbl.DELETE;
l_c_pty_accts_tbl.DELETE;
l_ii_rltns_tbl.delete;
SELECT object_version_number
INTO l_u_instance_rec.object_version_number
FROM csi_item_instances
WHERE instance_id = l_u_instance_rec.instance_id;
debug(' Inside API :csi_item_instance_pub.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 => px_csi_txn_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 location_type_code,
instance_usage_code
INTO l_splitted_instances(x_ind).location_type_code,
l_splitted_instances(x_ind).instance_usage_code
FROM csi_item_instances
WHERE instance_id = l_splitted_instances(x_ind).instance_id;
SELECT line_id
FROM oe_order_lines_all
WHERE link_to_line_id = p_parent_line_id
and nvl(cancelled_flag, 'N') <> 'Y' -- added for Bug 2946778. shegde
ORDER BY line_number, shipment_number, option_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 = p_om_vld_org_id;
l_line_tbl.DELETE;
l_line_tbl_nxt_lvl.delete;
l_line_tbl.DELETE;
SELECT sub_type_id
INTO x_txn_sub_type_id
FROM csi_txn_sub_types
WHERE transaction_type_id = p_txn_type_id
AND default_flag = 'Y';
update csi_t_txn_line_details
set quantity = 1,
processing_status = 'IN_PROCESS' ,
source_txn_line_detail_id = p_line_dtl_tbl(l_td_ind).txn_line_detail_id
where txn_line_detail_id = p_line_dtl_tbl(l_td_ind).txn_line_detail_id;
update csi_t_txn_line_details
set processing_status = 'IN_PROCESS'
where txn_line_detail_id = p_line_dtl_tbl(l_td_ind).txn_line_detail_id;
update csi_t_txn_line_details
set quantity = p_quantity_ratio,
processing_status = 'IN_PROCESS'
where txn_line_detail_id = px_line_dtl_tbl(l_td_ind).txn_line_detail_id;
UPDATE csi_t_txn_line_details
SET processing_status = 'IN_PROCESS'
WHERE txn_line_detail_id = px_line_dtl_tbl(l_td_ind).txn_line_detail_id;
SELECT 'Y'
INTO l_sub_model_flag
FROM sys.dual
WHERE exists (
SELECT 'X'
FROM bom_cto_order_lines
WHERE ato_line_id = p_ato_line_id
AND parent_ato_line_id = p_line_id);
SELECT ato_line_id
INTO l_ato_line_id
FROM oe_order_lines_all
WHERE line_id=p_top_model_line_id;
SELECT link_to_line_id ,
org_id
INTO l_parent_line_id,
l_org_id
FROM oe_order_lines_all
WHERE line_id = p_current_line_id;
SELECT inventory_item_id ,
link_to_line_id
INTO l_inventory_item_id ,
l_next_parent_line_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 = p_om_vld_org_id;
SELECT inventory_item_id,
instance_id,
serial_number,
location_type_code,
quantity
FROM csi_item_instances
WHERE inventory_item_id = p_parent_item_id
AND last_oe_order_line_id = p_parent_line_id;
SELECT object_version_number,
location_type_code
INTO l_inst_object_ver_num,
l_location_type_code
FROM csi_item_instances
WHERE instance_id = p_instance_id;
SELECT active_start_date
INTO l_u_instance_rec.active_start_date
FROM csi_item_instances
WHERE instance_id = p_instance_id;
SELECT instance_party_id
INTO l_pty_dtl_tbl(l_pc_ind).contact_party_id
FROM csi_i_parties
WHERE instance_id = l_u_instance_rec.instance_id
AND relationship_type_code = l_pty_dtl_tbl(l_p_ind).relationship_type_code
AND ((active_end_date is null ) OR
(active_end_date > sysdate));
SELECT instance_party_id
INTO l_pty_dtl_tbl(l_pc_ind).contact_party_id
FROM csi_i_parties
WHERE instance_id = l_u_instance_rec.instance_id
AND party_id = l_pty_dtl_tbl(l_p_ind).party_source_id -- old party
AND party_source_table = l_pty_dtl_tbl(l_p_ind).party_source_table
AND nvl(contact_flag,'N') = nvl(l_pty_dtl_tbl(l_p_ind).contact_flag,'N')
AND relationship_type_code = l_pty_dtl_tbl(l_p_ind).relationship_type_code
AND ((active_end_date is null ) OR
(active_end_date > sysdate));
SELECT nvl(src_change_owner, 'N'),
src_change_owner_to_code,
src_status_id
INTO l_src_change_owner,
l_src_change_owner_to_code,
l_src_status_id
FROM csi_ib_txn_types
WHERE sub_type_id = l_sub_type_id;
SELECT instance_party_id,
object_version_number
INTO l_instance_party_id,
l_pty_object_ver_num
FROM csi_i_parties
WHERE instance_id = p_instance_id
AND relationship_type_code = 'OWNER';
SELECT ip_account_id,
object_version_number
INTO l_ip_account_id,
l_acct_object_ver_num
FROM csi_ip_accounts
WHERE instance_party_id = l_instance_party_id
AND relationship_type_code = 'OWNER';
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_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 => px_csi_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 instance_id,
quantity,
serial_number,
location_type_code,
instance_usage_code
FROM csi_item_instances
WHERE inventory_item_id = p_inventory_item_id
AND ((location_type_code = 'WIP' AND wip_job_id = p_wip_entity_id)
OR
(instance_usage_code = 'IN_RELATIONSHIP' AND last_wip_job_id = p_wip_entity_id
AND NOT LOCATION_TYPE_CODE = 'INVENTORY')) --Changed for Bug 13977903
AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
AND nvl(active_end_date, sysdate+1);
SELECT instance_id,
quantity,
serial_number,
location_type_code,
instance_usage_code
FROM csi_item_instances
WHERE inventory_item_id = p_inventory_item_id
AND ((location_type_code = 'WIP' AND wip_job_id = p_wip_entity_id)
OR
(instance_usage_code = 'IN_RELATIONSHIP' AND last_wip_job_id = p_wip_entity_id
AND NOT LOCATION_TYPE_CODE = 'INVENTORY')) --Changed for Bug 13977903
AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
AND nvl(active_end_date, sysdate+1);
SELECT object_id
INTO l_parent_instance_id
FROM csi_ii_relationships
WHERE subject_id = px_wip_instances(l_ind).instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
AND nvl(active_end_date, sysdate+1);
px_wip_instances.DELETE(l_ind);
SELECT instance_id
FROM csi_item_instances
WHERE inventory_item_id = p_inventory_item_id
AND last_oe_order_line_id = p_split_from_line_id;
px_wip_instances.DELETE(px_ind);
l_temp_wip_instances.DELETE;
l_n_wip_instances.DELETE;
l_temp_wip_instances.DELETE;
l_n_wip_instances.DELETE;
SELECT mmt.creation_date mtl_creation_date,
mmt.transaction_id mtl_txn_id,
to_char(mmt.transaction_date,'dd-mm-yyyy hh24:mi:ss') mtl_txn_date,
mmt.organization_id organization_id,
mmt.transaction_type_id mtl_type_id,
mtt.transaction_type_name mtl_txn_name,
mmt.transaction_action_id mtl_action_id,
mmt.transaction_source_type_id mtl_source_type_id,
mmt.transaction_source_id mtl_source_id
FROM mtl_unit_transactions mut,
mtl_material_transactions mmt,
mtl_transaction_types mtt
WHERE mut.serial_number = p_serial_number
AND mut.inventory_item_id = p_item_id
AND mmt.transaction_id = mut.transaction_id
AND mtt.transaction_type_id = mmt.transaction_type_id
UNION
SELECT mmt.creation_date mtl_creation_date,
mmt.transaction_id mtl_txn_id,
to_char(mmt.transaction_date,'dd-mm-yy hh24:mi:ss') mtl_txn_date,
mmt.organization_id organization_id,
mmt.transaction_type_id mtl_type_id,
mtt.transaction_type_name mtl_txn_name,
mmt.transaction_action_id mtl_action_id,
mmt.transaction_source_type_id mtl_source_type_id,
mmt.transaction_source_id mtl_source_id
FROM mtl_unit_transactions mut,
mtl_transaction_lot_numbers mtln,
mtl_material_transactions mmt,
mtl_transaction_types mtt
WHERE mut.serial_number = p_serial_number
AND mut.inventory_item_id = p_item_id
AND mtln.serial_transaction_id = mut.transaction_id
AND mmt.transaction_id = mtln.transaction_id
AND mtt.transaction_type_id = mmt.transaction_type_id
ORDER BY 1 desc, 2 desc;
SELECT instance_id,
serial_number
FROM csi_item_instances
WHERE inventory_item_id = p_item_id
AND last_oe_order_line_id = p_line_id;
SELECT wip_entity_id,
organization_id,
request_id
FROM wip_discrete_jobs
WHERE primary_item_id = p_config_rec.item_id
AND organization_id = p_config_rec.ship_organization_id
AND source_line_id = p_source_line_id
AND status_type <> 7 -- excluding the cancelled wip jobs
ORDER by wip_entity_id desc;
SELECT mut.serial_number,
mut.inventory_item_id
FROM mtl_material_transactions mmt,
mtl_unit_transactions mut
WHERE mmt.transaction_source_type_id = 5
AND mmt.transaction_action_id = 1
AND mmt.inventory_item_id = px_config_rec.sub_config_item_id
AND mmt.transaction_source_id = px_config_rec.config_wip_job_id
AND mut.transaction_id = mmt.transaction_id
AND mut.inventory_item_id = mmt.inventory_item_id
UNION
SELECT mut.serial_number,
mut.inventory_item_id
FROM mtl_material_transactions mmt,
mtl_transaction_lot_numbers mtln,
mtl_unit_transactions mut
WHERE mmt.transaction_source_type_id = 5
AND mmt.transaction_action_id = 1
AND mmt.inventory_item_id = px_config_rec.sub_config_item_id
AND mmt.transaction_source_id = px_config_rec.config_wip_job_id
AND mtln.transaction_id = mmt.transaction_id
AND mtln.inventory_item_id = mmt.inventory_item_id
AND mut.transaction_id = mtln.serial_transaction_id
AND mut.inventory_item_id = mtln.inventory_item_id;
SELECT serial_number_control_code
INTO l_config_rec.sub_model_serial_code
FROM mtl_system_items
WHERE inventory_item_id = l_config_rec.sub_config_item_id
AND organization_id = l_config_rec.ship_organization_id;
SELECT wip_entity_id,
organization_id
INTO l_config_rec.sub_config_wip_job_id,
l_config_rec.sub_config_wip_org_id
FROM wip_discrete_jobs
WHERE primary_item_id = l_config_rec.sub_config_item_id
AND request_id = l_config_rec.request_id
AND rownum = 1;
SELECT oel.line_id,
oel.inventory_item_id,
oel.ship_from_org_id,
oel.ordered_quantity,
oel.split_from_line_id
INTO l_config_rec.line_id,
l_config_rec.item_id,
l_config_rec.ship_organization_id,
l_config_rec.order_quantity,
l_config_rec.split_from_line_id
FROM oe_order_lines_all oel
WHERE oel.header_id = p_ato_header_id
AND oel.link_to_line_id = p_ato_line_id
AND oel.item_type_code = 'CONFIG';
SELECT oel.line_id,
oel.inventory_item_id,
oel.ship_from_org_id,
oel.ordered_quantity,
oel.split_from_line_id
INTO l_config_rec.line_id,
l_config_rec.item_id,
l_config_rec.ship_organization_id,
l_config_rec.order_quantity,
l_config_rec.split_from_line_id
FROM oe_order_lines_all oel
WHERE oel.header_id = p_ato_header_id
AND oel.link_to_line_id = p_ato_line_id
AND oel.item_type_code = 'CONFIG'
AND oel.split_from_line_id is null;
SELECT serial_number_control_code
INTO l_config_rec.serial_code
FROM mtl_system_items
WHERE inventory_item_id = l_config_rec.item_id
AND organization_id = l_config_rec.ship_organization_id;
SELECT parent_ato_line_id
INTO l_parent_ato_line_id
FROM bom_cto_order_lines
WHERE line_id = p_line_id;
SELECT config_item_id,
wip_supply_type
INTO l_config_rec.sub_config_item_id,
l_config_rec.sub_model_wip_supply_type
FROM bom_cto_order_lines
WHERE line_id = l_config_rec.sub_model_line_id;
SELECT instance_id,
location_type_code
FROM csi_item_instances
WHERE inventory_item_id = p_config_rec.item_id
AND last_oe_order_line_id = p_config_rec.line_id;
SELECT instance_id,
serial_number,
location_type_code
FROM csi_item_instances
WHERE inventory_item_id = p_config_rec.item_id
AND last_oe_order_line_id = p_config_rec.line_id;
SELECT serial_number,
to_serial_number
FROM wsh_deliverables_v
WHERE source_line_id = p_config_rec.line_id
AND serial_number is not null;
SELECT cii_sub.instance_id,
cii_sub.serial_number,
cii_sub.location_type_code
FROM csi_item_instances cii_obj,
csi_ii_relationships cir,
csi_item_instances cii_sub
WHERE cii_obj.inventory_item_id = p_config_rec.item_id
AND cii_obj.last_oe_order_line_id = p_config_rec.line_id
AND cir.object_id = cii_obj.instance_id
AND cir.relationship_type_code = 'COMPONENT-OF'
AND cii_sub.instance_id = cir.subject_id
AND cii_sub.inventory_item_id = p_config_rec.sub_config_item_id;
SELECT cii.instance_id,
cii.serial_number,
cii.location_type_code
FROM csi_item_instances cii
WHERE cii.inventory_item_id = p_config_rec.sub_config_item_id
AND ((cii.location_type_code = 'WIP'
AND
cii.wip_job_id = p_config_rec.config_wip_job_id)
OR
(cii.last_wip_job_id = p_config_rec.config_wip_job_id));
SELECT instance_id ,
location_type_code
INTO l_instance_id,
l_location_type_code
FROM csi_item_instances
WHERE inventory_item_id = p_config_rec.item_id
AND serial_number = l_serial_number;
SELECT instance_id,
location_type_code
INTO l_instance_id,
l_location_type_code
FROM csi_item_instances
WHERE inventory_item_id = p_config_rec.item_id
AND serial_number = l_serial_number;
SELECT * INTO l_order_line_rec
FROM oe_order_lines_all
WHERE line_id = p_config_rec.sub_model_line_id;
SELECT * INTO l_order_hdr_rec
FROM oe_order_headers_all
WHERE header_id = l_order_line_rec.header_id;
SELECT ct.transaction_type_id
FROM csi_item_instances_h cih,
csi_transactions ct
WHERE cih.instance_id = p_instance_id
AND ct.transaction_id = cih.transaction_id
AND NOT (ct.transaction_type_id = 51 AND ct.source_header_ref_id = p_header_id)
ORDER BY ct.transaction_date desc;
SELECT cii.instance_id
FROM csi_item_instances cii,
csi_ii_relationships cir
WHERE cir.object_id = p_config_instance_id
AND cir.relationship_type_code = 'COMPONENT-OF'
AND cii.instance_id = cir.subject_id
AND cii.inventory_item_id = p_option_item_id
AND sysdate BETWEEN nvl(cii.active_start_date, sysdate-1)
AND nvl(cii.active_end_date, sysdate+1);
SELECT object_version_number
INTO l_inst_object_ver_num
FROM csi_item_instances
WHERE instance_id = p_instance_id;
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_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 => px_csi_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);
debug('wip issued component instance updated with the om info. instance_id : '||
l_u_instance_rec.instance_id);
SELECT msi.serial_number_control_code
FROM mtl_system_items msi,
oe_order_lines_all oel
WHERE oel.link_to_line_id = p_ato_line_id
AND oel.item_type_code = 'CONFIG'
AND msi.organization_id = oel.ship_from_org_id
AND msi.inventory_item_id = oel.inventory_item_id;
SELECT object_version_number
INTO l_exp_rltns_rec.object_version_number
FROM csi_ii_relationships
WHERE relationship_id = l_exp_rltns_rec.relationship_id;
px_config_instances.DELETE(px_ind);
px_src_tld_tbl.DELETE(px_ind);
SELECT quantity ,
last_vld_organization_id
INTO l_instance_quantity ,
l_vld_organization_id
FROM csi_item_instances
WHERE instance_id = px_ii_rltns_tbl(px_ind).subject_id;
SELECT relationship_id,
object_version_number
INTO px_ii_rltns_tbl(px_new_ind).relationship_id,
px_ii_rltns_tbl(px_new_ind).object_version_number
FROM csi_ii_relationships
WHERE object_id = px_ii_rltns_tbl(px_new_ind).object_id
AND subject_id = px_ii_rltns_tbl(px_new_ind).subject_id
AND relationship_type_code = 'COMPONENT-OF';
px_ii_rltns_tbl.DELETE(px_ind);
px_ii_rltns_tbl.DELETE(px_ind);
px_ii_rltns_tbl.DELETE(px_ind);
SELECT cii.instance_id
FROM csi_item_instances cii
WHERE cii.inventory_item_id = p_option_line_rec.inventory_item_id
AND cii.last_oe_order_line_id = p_option_line_rec.line_id
AND cii.location_type_code = 'HZ_PARTY_SITES'
AND cii.instance_usage_code IN ( 'OUT_OF_ENTERPRISE','IN_RELATIONSHIP') -- Changed for bug#14267193
AND not exists (
SELECT 'x' FROM csi_t_txn_line_details ctld
WHERE ctld.transaction_line_id = p_transaction_line_id
AND ctld.source_transaction_flag = 'N'
AND ctld.instance_id = cii.instance_id);
SELECT 'Y' INTO l_option_instance
FROM csi_item_instances cii
WHERE cii.instance_id = px_ii_rltns_tbl(px_ind).subject_id
AND cii.inventory_item_id = p_option_line_rec.inventory_item_id;
SELECT relationship_id,
object_version_number
INTO l_ii_rltns_tbl(l_ind).relationship_id,
l_ii_rltns_tbl(l_ind).object_version_number
FROM csi_ii_relationships
WHERE subject_id = l_wip_instances(w_ind).instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND sysdate between nvl(active_start_date, sysdate-1)
and nvl(active_end_date, sysdate+1);
SELECT 'Y' INTO l_phantom
FROM sys.dual
WHERE EXISTS (
SELECT '1' FROM wip_requirement_operations
WHERE wip_entity_id = p_wip_entity_id
AND inventory_item_id = p_option_item_id
AND required_quantity > 0
AND wip_supply_type = 6); --phantoms
SELECT 'Y' INTO l_phantom
FROM csi_item_instances
WHERE inventory_item_id = p_option_item_id
AND last_oe_order_line_id = NVL(p_line_id,-1);
SELECT wip_supply_type,
quantity_issued
INTO l_wip_supply_type,
l_quantity_issued
FROM wip_requirement_operations
WHERE wip_entity_id = p_wip_entity_id
AND inventory_item_id = p_option_item_id
AND required_quantity > 0
AND rownum = 1;
SELECT msi.SHIPPABLE_ITEM_FLAG
INTO l_shippable_item_flag
FROM mtl_system_items_b msi, oe_order_lines_all eol
WHERE msi.ORGANIZATION_ID = eol.SHIP_FROM_ORG_ID
AND msi.INVENTORY_ITEM_ID = p_option_item_id
AND eol.LINE_ID = p_line_id;
SELECT 'Y' INTO l_phantom
FROM csi_item_instances
WHERE inventory_item_id = p_option_item_id
AND last_oe_order_line_id = NVL(p_line_id,-1);
debug(' Non Shippable: wip requirements deleted/altered/substituted - Instance exists, WIP processing = true' );
debug(' Non Shippable: wip requirements deleted/altered/substituted - Instance does not exists, WIP processing = false' );
debug(' Non Shippable: wip requirements deleted/altered/substituted - Instance exists, WIP processing = true' );
debug(' wip requirements deleted/altered/substituted. will just fulfill the line.');
SELECT bom.assembly_item_id parent_item_id
,bet.component_item_id component_item_id
,bet.organization_id organization_id
,bet.component_quantity component_quantity
,bet.extended_quantity extended_quantity
,bet.plan_level plan_level
--,substr(bet.sort_order,1,(length(sort_order)-l_bom_sortcode_width)) parent_sort_order
,bet.sort_order child_sort_order
,msi.serial_number_control_code serial_number_control_code
,msi.primary_uom_code primary_uom_code
,mp.master_organization_id master_organization_id
,wro.quantity_issued quantity
FROM bom_small_expl_temp bet
,bom_bill_of_materials bom
,mtl_system_items msi
,mtl_system_items msip
,mtl_parameters mp
,wip_requirement_operations wro
WHERE bet.group_id = p_group_id
AND bet.plan_level > 0 -- Ignore Top Most
AND bet.component_item_id = msi.inventory_item_id
AND bet.organization_id = msi.organization_id
AND bet.bill_sequence_id = bom.bill_sequence_id
AND bet.extended_quantity > 0
AND bom.assembly_item_id = msip.inventory_item_id
AND bom.organization_id = msip.organization_id
AND msi.organization_id = mp.organization_id
AND wro.wip_entity_id = p_wip_job_id
AND wro.inventory_item_id = bet.component_item_id
AND wro.required_quantity > 0 --Added for Bug 12709987
--AND bom.assembly_item_id = p_option_item_id
AND msi.comms_nl_trackable_flag = 'Y'
ORDER BY bet.sort_order;
SELECT 'Y'
INTO l_bom_found
FROM bom_bill_of_materials
WHERE assembly_item_id = p_option_item_id
AND organization_id = p_organization_id
AND alternate_bom_designator is NULL;
SELECT Bom_Explosion_Temp_S.NextVal
INTO l_group_id
FROM sys.dual;
SELECT bom.assembly_item_id parent_item_id ,
bet.component_item_id component_item_id ,
bet.organization_id organization_id ,
bet.component_quantity component_quantity ,
bet.extended_quantity extended_quantity ,
bet.plan_level plan_level ,
--SUBSTR(bet.sort_order,1,(LENGTH(sort_order)-l_bom_sortcode_width)) parent_sort_order ,
bet.sort_order child_sort_order ,
msi.serial_number_control_code serial_number_control_code ,
msi.primary_uom_code primary_uom_code ,
mp.master_organization_id master_organization_id
FROM bom_small_expl_temp bet ,
bom_bill_of_materials bom ,
mtl_system_items msi ,
mtl_system_items msip ,
mtl_parameters mp
WHERE bet.group_id = p_group_id
AND bet.plan_level > 0 -- Ignore Top Most
AND bet.component_item_id = msi.inventory_item_id
AND bet.organization_id = msi.organization_id
AND bet.bill_sequence_id = bom.bill_sequence_id
AND bet.extended_quantity > 0
AND bom.assembly_item_id = msip.inventory_item_id
AND bom.organization_id = msip.organization_id
AND msi.organization_id = mp.organization_id
AND msi.comms_nl_trackable_flag = 'Y'
ORDER BY bet.sort_order;
SELECT Bom_Explosion_Temp_S.NextVal INTO l_group_id FROM sys.dual;
SELECT cir.subject_id,
cir.relationship_id,
cir.object_version_number
FROM csi_item_instances cii_sub,
csi_ii_relationships cir
WHERE cir.object_id = p_top_config_instance_id
AND cir.relationship_type_code = 'COMPONENT-OF'
AND sysdate between nvl(cir.active_start_date, sysdate-1)
and nvl(cir.active_end_date, sysdate+1)
AND cii_sub.instance_id = cir.subject_id
AND cii_sub.inventory_item_id = p_sub_config_item_id;
SELECT instance_id
INTO x_config_instance.instance_id
FROM csi_item_instances
WHERE instance_id = l_iir_tbl(iir_ind).object_id
AND inventory_item_id = p_config_rec.item_id;
l_oc_tld_tbl.DELETE;
SELECT CII.instance_id
INTO l_parent_inst_id
FROM csi_item_instances CII
WHERE CII.last_oe_order_line_id = l_parent_line_rec.line_id;
l_child_line_tbl.DELETE;
SELECT serial_number_control_code
INTO l_option_serial_code
FROM mtl_system_items
WHERE inventory_item_id = l_child_line_tbl(cl_ind).inventory_item_id
AND organization_id = l_child_line_tbl(cl_ind).ship_from_org_id;
l_t_iir_tbl.DELETE;
px_instance_tbl.DELETE(px_ind);
px_instance_tbl.DELETE(px_ind);
px_instance_tbl.DELETE(px_ind);
select sum(ordered_quantity)
into l_order_line_qty
from oe_order_lines_all
where link_to_line_id = l_child_line_tbl(l_ind).link_to_line_id
and inventory_item_id = l_child_line_tbl(l_ind).inventory_item_id
and model_remnant_flag = 'Y';
l_temp_instance_hdr_tbl.DELETE;
l_t_iir_tbl.DELETE;
select sum(ordered_quantity)
into l_order_line_qty
from oe_order_lines_all
where link_to_line_id = l_child_line_tbl(l_ind).link_to_line_id
and inventory_item_id = l_child_line_tbl(l_ind).inventory_item_id
and model_remnant_flag = 'Y';
l_temp_instance_hdr_tbl.DELETE;
l_t_iir_tbl.DELETE;
SELECT serial_number_control_code
INTO l_option_serial_code
FROM mtl_system_items
WHERE inventory_item_id = p_order_line_rec.inventory_item_id
AND organization_id = p_order_line_rec.ship_from_org_id;
l_parent_instances.DELETE;
SELECT cii.instance_id,
cir.relationship_id,
cir.object_version_number
FROM csi_item_instances cii,
csi_ii_relationships cir
WHERE cir.object_id = p_config_instance_id
AND cir.relationship_type_code = 'COMPONENT-OF'
AND cii.instance_id = cir.subject_id
AND cii.inventory_item_id = p_class_item_id;
l_delete_flag varchar2(1);
PROCEDURE srl_get_and_delete(
p_instance_id IN number,
p_class_option_ratio IN number,
px_class_instances IN OUT nocopy parent_instances,
x_class_instance OUT nocopy parent_instance)
IS
l_ind binary_integer := 0;
px_class_instances.DELETE(l_ind);
END srl_get_and_delete;
srl_get_and_delete (
p_instance_id => class_inst_rec.instance_id,
p_class_option_ratio => p_class_option_ratio,
px_class_instances => px_class_instances,
x_class_instance => l_class_instance);
SELECT instance_id,
serial_number,
location_type_code
FROM csi_item_instances
WHERE instance_id = p_instance_id
AND inventory_item_id = p_config_rec.item_id;
SELECT serial_number_control_code
INTO l_option_serial_code
FROM mtl_system_items
WHERE inventory_item_id = p_order_line_rec.inventory_item_id
AND organization_id = p_order_line_rec.ship_from_org_id;
l_parent_instances.DELETE;
SELECT transaction_line_id
INTO l_transaction_line_id
FROM csi_t_transaction_lines
WHERE source_transaction_table = 'OE_ORDER_LINES_ALL'
AND source_transaction_id = p_order_line_rec.line_id;
SELECT transaction_line_id
INTO l_transaction_line_id
FROM csi_t_transaction_lines
WHERE source_transaction_table = 'OE_ORDER_LINES_ALL'
AND source_transaction_id = p_order_line_rec.top_model_line_id;
SELECT ordered_quantity
INTO l_mdl_ordered_qty
FROM oe_order_lines_all
WHERE line_id = p_order_line_rec.top_model_line_id;
SELECT sum(ordered_quantity)/l_mdl_ordered_qty
INTO l_qty_ratio
FROM oe_order_lines_all
WHERE link_to_line_id = p_order_line_rec.link_to_line_id
AND inventory_item_id = p_order_line_rec.inventory_item_id;
SELECT to_char(p_order_line_rec.top_model_line_id)||':'||
to_char(p_order_line_rec.line_id)||':'||
to_char(p_order_line_rec.inventory_item_id)||':'||
decode(nvl(p_order_line_rec.item_revision, '###'), '###',
null, p_order_line_rec.item_revision||':')||
to_char(l_qty_ratio)||':'||
p_order_line_rec.order_quantity_uom ||':'||
p_order_line_rec.ordered_quantity --added for bug5096435
INTO l_model_hierarchy
FROM sys.dual;
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 RMA Fulfillment.');
PROCEDURE query_tld_and_update_ib(
p_order_header_rec IN oe_order_headers_all%rowtype,
p_order_line_rec IN csi_order_ship_pub.order_line_rec, --fix for bug5589710
px_default_info_rec IN OUT NOCOPY default_info_rec,
px_csi_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
px_error_rec IN OUT NOCOPY csi_datastructures_pub.transaction_error_rec,
x_return_status OUT NOCOPY varchar2)
IS
l_tl_query_rec csi_t_datastructures_grp.txn_line_query_rec;
api_log('query_tld_and_update_ib');
SELECT party_site_id
INTO l_cur_party_site_id
FROM hz_cust_acct_sites_all c,
hz_cust_site_uses_all u
WHERE c.cust_acct_site_id = u.cust_acct_site_id
AND u.site_use_id = px_default_info_rec.current_party_site_id;
SELECT party_site_id
INTO l_inst_party_site_id
FROM hz_cust_acct_sites_all c,
hz_cust_site_uses_all u
WHERE c.cust_acct_site_id = u.cust_acct_site_id
AND u.site_use_id = px_default_info_rec.install_party_site_id;
csi_order_ship_pub.update_install_base(
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_p_tl_rec,
p_txn_line_detail_tbl => l_p_tld_tbl,
p_txn_party_detail_tbl => l_p_tpd_tbl,
p_txn_pty_acct_dtl_tbl => l_p_tpa_tbl,
p_txn_org_assgn_tbl => l_p_toa_tbl,
p_txn_ii_rltns_tbl => l_p_tiir_tbl,
p_txn_ext_attrib_vals_tbl => l_p_teav_tbl,
p_txn_systems_tbl => l_p_tsys_tbl,
p_pricing_attribs_tbl => l_p_pa_tbl,
p_order_line_rec => l_p_order_line_rec,
p_trx_rec => px_csi_txn_rec,
p_source => 'FULFILLMENT',
p_validate_only => 'N',
px_error_rec => px_error_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
UPDATE csi_t_transaction_lines
SET processing_status = 'PROCESSED'
WHERE transaction_line_id = l_p_tl_rec.transaction_line_id;
END query_tld_and_update_ib;
SELECT OOL.LINE_ID
FROM OE_ORDER_LINES_ALL OOL,
MTL_SYSTEM_ITEMS MSI
WHERE OOL.HEADER_ID = p_header_id
AND OOL.top_model_line_id = p_top_model_line_id
AND OOL.FLOW_STATUS_CODE NOT IN ('CANCELLED')
AND OOL.SHIPPABLE_FLAG = 'Y'
AND MSI.inventory_item_id = OOL.inventory_item_id
AND MSI.organization_id = OOL.ship_from_org_id
AND MSI.comms_nl_trackable_flag = 'Y';
SELECT 'Y' INTO l_dummy
FROM CSI_TRANSACTIONS
WHERE SOURCE_LINE_REF_ID = shippable_order_rec.line_id
AND TRANSACTION_TYPE_ID = 51;
SELECT *
INTO l_order_line_rec
FROM oe_order_lines_all
WHERE line_id = p_order_line_id;
SELECT *
INTO l_order_header_rec
FROM oe_order_headers_all
WHERE header_id = l_order_line_rec.header_id;
SELECT HCAS.party_site_id
INTO l_party_site_id
FROM hz_cust_site_uses_all HCSU,
hz_cust_acct_sites_all HCAS
WHERE HCSU.site_use_id = l_current_site_use_id
AND HCAS.cust_acct_site_id = HCSU.cust_acct_site_id;
SELECT HCAS.party_site_id
INTO l_party_site_id
FROM hz_cust_site_uses_all HCSU,
hz_cust_acct_sites_all HCAS
WHERE HCSU.site_use_id = l_install_site_use_id
AND HCAS.cust_acct_site_id = HCSU.cust_acct_site_id;
SELECT party_id
INTO l_owner_party_id
FROM hz_cust_accounts
WHERE cust_account_id = l_end_customer_id;
SELECT nvl(shippable_item_flag ,'N')
INTO l_shippable_item_flag
FROM MTL_SYSTEM_ITEMS_B
WHERE inventory_item_id = l_order_line_rec.inventory_item_id
AND organization_id = l_order_line_rec.ship_from_org_id;
SELECT changed_instance_id,ctl.transaction_line_id
INTO l_ul_instance_rec.instance_id,l_ul_txn_line_id
FROM CSI_T_TRANSACTION_LINES ctl,
CSI_T_TXN_LINE_DETAILS ctld
WHERE ctl.source_transaction_table = 'CONFIGURATOR'
AND ctl.config_session_hdr_id = l_om_session_key.session_hdr_id
AND ctl.config_session_rev_num = l_om_session_key.session_rev_num
AND ctl.config_session_item_id = l_om_session_key.session_item_id
AND ctld.transaction_line_id = ctl.transaction_line_id
AND ctld.source_transaction_flag = 'Y';
select object_version_number
into l_ul_instance_rec.object_version_number
from CSI_ITEM_INSTANCES
where instance_id = l_ul_instance_rec.instance_id;
debug(' Inside API :csi_item_instance_pub.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_ul_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_txn_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);
UPDATE csi_t_txn_line_details
SET processing_status = 'PROCESSED'
WHERE transaction_line_id = l_ul_txn_line_id;
SELECT nvl(shippable_item_flag ,'N'),
nvl(mtl_transactions_enabled_flag, 'N'),
serial_number_control_code,
lot_control_code,
revision_qty_control_code,
location_control_code,
comms_nl_trackable_flag,
bom_item_type,
reservable_type,
pick_components_flag,
primary_uom_code
INTO l_shippable_item_flag,
l_inv_transactable_flag,
l_serial_code,
l_lot_code,
l_revision_control_code,
l_locator_control_code,
l_ib_trackable_flag,
l_bom_item_type,
l_reservable_type,
l_pick_components_flag,
l_primary_uom_code
FROM mtl_system_items
WHERE inventory_item_id = l_order_line_rec.inventory_item_id
AND organization_id = l_order_line_rec.ship_from_org_id;
SELECT ato_line_id
INTO l_ato_line_id
FROM oe_order_lines_all
WHERE line_id=l_order_line_rec.link_to_line_id;
select sum(ordered_quantity)
into l_order_line_qty
from oe_order_lines_all
where link_to_line_id = l_order_line_rec.link_to_line_id
and inventory_item_id = l_order_line_rec.inventory_item_id
and model_remnant_flag = 'Y';
SELECT 'Y'
INTO l_cascade_eligible
FROM csi_t_transaction_lines
WHERE source_transaction_table = l_src_txn_table
AND source_transaction_id = l_order_line_rec.top_model_line_id;
SELECT ordered_quantity
INTO l_mdl_ordered_qty
FROM oe_order_lines_all
WHERE line_id = l_order_line_rec.top_model_line_id;
SELECT sum(ordered_quantity)/l_mdl_ordered_qty
INTO l_qty_ratio
FROM oe_order_lines_all
WHERE link_to_line_id = l_order_line_rec.link_to_line_id
AND inventory_item_id = l_order_line_rec.inventory_item_id;
SELECT to_char(l_order_line_rec.top_model_line_id)||':'||
to_char(l_order_line_rec.line_id)||':'||
to_char(l_order_line_rec.inventory_item_id)||':'||
decode(nvl(l_order_line_rec.item_revision, '###'),
'###', null, l_order_line_rec.item_revision||':')||
to_char(l_qty_ratio)||':'||
l_order_line_rec.order_quantity_uom ||':'||
l_order_line_rec.ordered_quantity --added for bug5096435
INTO l_model_hierarchy
FROM sys.dual;
SELECT processing_status ,
transaction_line_id
INTO l_processing_status,
l_transaction_line_id
FROM csi_t_transaction_lines
WHERE source_transaction_table = l_src_txn_table
AND source_transaction_id = p_order_line_id;
UPDATE csi_t_transaction_lines
SET processing_status = 'IN_PROCESS'
WHERE transaction_line_id = l_transaction_line_id;
UPDATE csi_t_transaction_lines
SET processing_status = 'IN_PROCESS'
WHERE transaction_line_id = l_txn_line_rec.transaction_line_id;
UPDATE csi_t_txn_line_details
SET processing_status = 'IN_PROCESS'
WHERE txn_line_detail_id = l_line_dtl_tbl(l_td_ind).txn_line_detail_id;
UPDATE csi_t_txn_line_details
SET processing_status = 'IN_PROCESS'
WHERE transaction_line_id = l_txn_line_rec.transaction_line_id
AND source_transaction_flag = 'N';
query_tld_and_update_ib(
p_order_header_rec => l_order_header_rec,
p_order_line_rec => l_p_order_line_rec, --fix for bug 5589710
px_default_info_rec => l_default_info_rec,
px_csi_txn_rec => l_csi_txn_rec,
px_error_rec => l_error_rec,
x_return_status => l_return_status);
UPDATE csi_t_transaction_lines
SET processing_status = 'ERROR'
WHERE source_transaction_id = p_order_line_id
AND source_transaction_table = 'OE_ORDER_LINES_ALL';
csi_utl_pkg.update_txn_line_dtl (
p_source_trx_id => p_order_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_order_line_id
AND source_transaction_table = 'OE_ORDER_LINES_ALL';
csi_utl_pkg.update_txn_line_dtl (
p_source_trx_id => p_order_line_id,
p_source_trx_table => 'OE_ORDER_LINES_ALL',
p_api_name => l_api_name,
p_error_message => l_error_message );
SELECT transaction_line_id
INTO l_trx_line_id
FROM csi_t_transaction_lines
WHERE source_transaction_id = l_c_tl_rec.source_transaction_id
AND source_transaction_table = l_c_tl_rec.source_transaction_table
AND source_transaction_type_id = l_c_tl_rec.source_transaction_type_id;
csi_t_transaction_lines_pkg.insert_row(
px_transaction_line_id => l_c_tl_rec.transaction_line_id,
p_source_transaction_type_id => l_c_tl_rec.source_transaction_type_id,
p_source_transaction_table => l_c_tl_rec.source_transaction_table,
---Added (Start) for m-to-m enhancements
p_source_txn_header_id => l_c_tl_rec.source_txn_header_id,
---Added (End) for m-to-m enhancements
p_source_transaction_id => l_c_tl_rec.source_transaction_id,
-- Added for CZ Integration (Begin)
p_config_session_hdr_id => l_c_tl_rec.config_session_hdr_id ,
p_config_session_rev_num => l_c_tl_rec.config_session_rev_num ,
p_config_session_item_id => l_c_tl_rec.config_session_item_id ,
p_config_valid_status => l_c_tl_rec.config_valid_status ,
p_source_transaction_status => l_c_tl_rec.source_transaction_status ,
-- Added for CZ Integration (End)
p_error_code => l_c_tl_rec.error_code,
p_error_explanation => l_c_tl_rec.error_explanation,
p_processing_status => 'SUBMIT',
p_attribute1 => l_c_tl_rec.attribute1,
p_attribute2 => l_c_tl_rec.attribute2,
p_attribute3 => l_c_tl_rec.attribute3,
p_attribute4 => l_c_tl_rec.attribute4,
p_attribute5 => l_c_tl_rec.attribute5,
p_attribute6 => l_c_tl_rec.attribute6,
p_attribute7 => l_c_tl_rec.attribute7,
p_attribute8 => l_c_tl_rec.attribute8,
p_attribute9 => l_c_tl_rec.attribute9,
p_attribute10 => l_c_tl_rec.attribute10,
p_attribute11 => l_c_tl_rec.attribute11,
p_attribute12 => l_c_tl_rec.attribute12,
p_attribute13 => l_c_tl_rec.attribute13,
p_attribute14 => l_c_tl_rec.attribute14,
p_attribute15 => l_c_tl_rec.attribute15,
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.0,
p_context => l_c_tl_rec.context);
fnd_message.set_token('MESSAGE','insert_row failed '||sqlerrm);
SELECT party_site_id
INTO l_party_site_id
FROM hz_cust_acct_sites_all c,
hz_cust_site_uses_all u
WHERE c.cust_acct_site_id = u.cust_acct_site_id
AND u.site_use_id = x_order_shipment_tbl(i).ib_current_loc_id;
SELECT party_site_id
INTO l_install_party_site_id
FROM hz_cust_acct_sites_all c,
hz_cust_site_uses_all u
WHERE c.cust_acct_site_id = u.cust_acct_site_id
AND u.site_use_id = x_order_shipment_tbl(i).ib_install_loc_id;
l_c_tld_tbl.delete;
l_c_tpd_tbl.delete;
l_c_tpad_tbl.delete;
l_c_toa_tbl.delete;
l_c_teav_tbl.delete;
l_c_ts_tbl.delete;
l_c_tiir_tbl.delete;
SELECT ool.line_id,
ool.header_id,
ool.item_type_code,
ool.cust_po_number,
ool.line_type_id,
ool.ato_line_id,
ool.top_model_line_id,
ool.link_to_line_id,
NVL(ool.invoice_to_contact_id ,ooh.invoice_to_contact_id ) invoice_to_contact_id ,
NVL(ool.ship_to_contact_id, ooh.ship_to_contact_id) ship_to_contact_id,
NVL(ool.ship_from_org_id, ooh.ship_from_org_id) ship_from_org_id ,
NVL(ool.sold_to_org_id, ooh.sold_to_org_id) sold_to_org_id ,
NVL(ool.sold_from_org_id, ooh.sold_from_org_id) sold_from_org_id ,
NVL(ool.ship_to_org_id, ooh.ship_to_org_id) ship_to_org_id ,
NVL(ool.invoice_to_org_id, ooh.invoice_to_org_id) invoice_to_org_id ,
NVL(ool.deliver_to_org_id, ooh.deliver_to_org_id) deliver_to_org_id ,
ool.ordered_quantity,
ool.shipped_quantity ord_line_shipped_qty,
ool.order_quantity_uom,
mmt.inventory_item_id inventory_item_id,
mmt.organization_id inv_organization_id,
mmt.revision revision,
mmt.subinventory_code subinventory,
mmt.locator_id locator_id,
null lot_number,
mut.serial_number serial_number,
abs(mmt.transaction_quantity) shipped_quantity,
mmt.transaction_uom,
mmt.transaction_date,
msi.lot_control_code,
msi.serial_number_control_code,
msi.reservable_type,
haou.location_id hr_location_id,
msei.location_id subinv_location_id,
to_char(null) ib_owner,
to_number(null) end_customer_id,
to_char(null) ib_install_loc,
to_number(null) ib_install_loc_id,
to_char(null) ib_current_loc,
to_number(null) ib_current_loc_id
FROM oe_order_headers_all ooh,
oe_order_lines_all ool,
mtl_system_items msi,
mtl_unit_transactions mut,
mtl_material_transactions mmt,
mtl_secondary_inventories msei,
hr_all_organization_units haou
WHERE mmt.transaction_id = p_mtl_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
AND mmt.organization_id = haou.organization_id(+)
AND mmt.subinventory_code = msei.secondary_inventory_name(+)
AND mmt.organization_id = msei.organization_id(+)
AND ool.line_id = mmt.trx_source_line_id
AND ooh.header_id = ool.header_id
UNION
SELECT ool.line_id,
ool.header_id,
ool.item_type_code,
ool.cust_po_number,
ool.line_type_id,
ool.ato_line_id,
ool.top_model_line_id,
ool.link_to_line_id,
NVL(ool.invoice_to_contact_id, ooh.invoice_to_contact_id ) invoice_to_contact_id ,
NVL(ool.ship_to_contact_id, ooh.ship_to_contact_id) ship_to_contact_id,
NVL(ool.ship_from_org_id , ooh.ship_from_org_id) ship_from_org_id ,
NVL(ool.sold_to_org_id , ooh.sold_to_org_id) sold_to_org_id ,
NVL(ool.sold_from_org_id, ooh.sold_from_org_id) sold_from_org_id ,
NVL(ool.ship_to_org_id , ooh.ship_to_org_id) ship_to_org_id ,
NVL(ool.invoice_to_org_id, ooh.invoice_to_org_id) invoice_to_org_id ,
NVL(ool.deliver_to_org_id, ooh.deliver_to_org_id) deliver_to_org_id ,
ool.ordered_quantity,
ool.shipped_quantity ord_line_shipped_qty,
ool.order_quantity_uom ,
mmt.inventory_item_id inventory_item_id,
mmt.organization_id inv_organization_id,
mmt.revision revision,
mmt.subinventory_code subinventory,
mmt.locator_id locator_id,
mtln.lot_number lot_number,
mut.serial_number serial_number,
abs(mtln.transaction_quantity) shipped_quantity,
mmt.transaction_uom,
mmt.transaction_date,
msi.lot_control_code,
msi.serial_number_control_code,
msi.reservable_type,
haou.location_id hr_location_id,
msei.location_id subinv_location_id,
to_char(null) ib_owner,
to_number(null) end_customer_id,
to_char(null) ib_install_loc,
to_number(null) ib_install_loc_id,
to_char(null) ib_current_loc,
to_number(null) ib_current_loc_id
FROM oe_order_headers_all ooh,
oe_order_lines_all ool,
mtl_system_items msi,
mtl_unit_transactions mut,
mtl_transaction_lot_numbers mtln,
mtl_material_transactions mmt,
mtl_secondary_inventories msei,
hr_all_organization_units haou
WHERE mmt.transaction_id = p_mtl_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
AND mmt.organization_id = haou.organization_id(+)
AND mmt.subinventory_code = msei.secondary_inventory_name(+)
AND mmt.organization_id = msei.organization_id(+)
AND mmt.trx_source_line_id = ool.line_id
AND ool.header_id = ooh.header_id;
SELECT trx_source_line_id
INTO p_order_line_id
FROM mtl_material_transactions
WHERE transaction_id = p_mtl_txn_id;
SELECT ship_from_org_id
INTO l_ship_from_org_id
FROM oe_order_lines_all
WHERE line_id = p_order_line_id;
SELECT nvl(shippable_item_flag ,'N'),
nvl(mtl_transactions_enabled_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_inv_transactable_flag,
l_serial_code,
l_lot_code,
l_revision_control_code,
l_locator_control_code,
l_ib_trackable_flag
FROM mtl_system_items
WHERE inventory_item_id = l_order_line_rec.inv_item_id
AND organization_id = l_ship_from_org_id;
SELECT instance_id,
object_version_number,
active_end_date,
location_type_code
INTO l_exp_instance_id,
l_exp_obj_ver_num,
l_exp_active_end_date,
l_exp_loc_type_code
FROM csi_item_instances
WHERE inventory_item_id = mmt_rec.inventory_item_id
AND serial_number = mmt_rec.serial_number;
SELECT object_version_number
INTO p_party_tbl(1).object_version_number
FROM csi_i_parties
WHERE instance_party_id = l_inst_party_id;
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 => p_instance_rec,
p_ext_attrib_values_tbl => p_ext_attrib_values_tbl,
p_party_tbl => p_party_tbl,
p_account_tbl => p_party_account_tbl,
p_pricing_attrib_tbl => p_pricing_attrib_tbl,
p_org_assignments_tbl => p_org_assignments_tbl,
p_txn_rec => p_txn_rec,
p_asset_assignment_tbl => p_asset_assignment_tbl,
x_instance_id_lst => x_instance_id_lst,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
SELECT object_version_number
INTO l_exp_instance_rec.object_version_number
FROM csi_item_instances
WHERE instance_id = l_exp_instance_id;
select multi_org_flag
into l_multi_org_flag
from FND_PRODUCT_GROUPS;
l_exp_line_tbl.delete;
l_exp_old_line_tbl.delete;
select mp.master_organization_id
into l_organization_id
from MTL_PARAMETERS mp
where mp.organization_id = l_organization_id;
select mp.master_organization_id
into l_organization_id
from MTL_PARAMETERS mp
where mp.organization_id = l_organization_id;
l_exp_line_tbl.delete;
l_exp_old_line_tbl.delete;
select mp.master_organization_id
into l_organization_id
from MTL_PARAMETERS mp
where mp.organization_id = l_organization_id;
select mp.master_organization_id
into l_organization_id
from MTL_PARAMETERS mp
where mp.organization_id = l_organization_id;
** Procedure Name : Update_Profile
** Author : srramakr
**
** This Procedure is to update the Profile CSI_PROCESS_FULFILL_LINES to N, so that
** the Process Old Fulfill Order Lines program does not get executed.
** It basically checks for fulfillable lines created prior to moving into 11.5.6
** and not yet fulfilled and still remain open.
** If the none of the order lines fall in the above category, it updates the profile
** to N.
**************************************************************************************/
--
PROCEDURE Update_profile (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER)
IS
CURSOR OE_LINE_CUR(p_freeze_date DATE) IS
SELECT line_id,inventory_item_id,org_id,shippable_flag
FROM OE_ORDER_LINES_ALL
WHERE creation_date <= p_freeze_date
AND nvl(fulfilled_flag,'N') <> 'Y'
AND open_flag = 'Y';
select profile_option_id
into l_profile_option_id
from FND_PROFILE_OPTIONS
where upper(profile_option_name) = 'CSI_PROCESS_FULFILL_LINES';
select multi_org_flag
into l_multi_org_flag
from FND_PRODUCT_GROUPS;
select mp.master_organization_id
into l_organization_id
from MTL_PARAMETERS mp
where mp.organization_id = l_organization_id;
select mp.master_organization_id
into l_organization_id
from MTL_PARAMETERS mp
where mp.organization_id = l_organization_id;
UPDATE fnd_profile_option_values
SET profile_option_value = 'N'
WHERE profile_option_id = l_profile_option_id
AND application_id=542 --fix for the bug 4907945
AND level_id = 10001;
END Update_Profile;