[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT mmt.creation_date creation_date,
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.transaction_type_id transaction_type_id,
mmt.transaction_action_id transaction_action_id,
mmt.transaction_source_id transaction_source_id,
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,
abs(mmt.primary_quantity) mmt_primary_quantity,
abs(mtln.primary_quantity) lot_primary_quantity,
mmt.transaction_set_id transaction_set_id --bug 5376024
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_transaction_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.creation_date creation_date,
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.transaction_type_id transaction_type_id,
mmt.transaction_action_id transaction_action_id,
mmt.transaction_source_id transaction_source_id,
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,
abs(mmt.primary_quantity) mmt_primary_quantity,
abs(mtln.primary_quantity) lot_primary_quantity,
mmt.transaction_set_id transaction_set_id --bug 5376024
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_transaction_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 transaction_id,
transaction_date,
inventory_item_id,
organization_id,
abs(primary_quantity),
transaction_type_id,
transaction_source_type_id,
transaction_action_id,
transaction_source_id,
creation_date
INTO l_txn_ref.transaction_id,
l_txn_ref.transaction_date,
l_txn_ref.inventory_item_id,
l_txn_ref.organization_id,
l_txn_ref.primary_quantity,
l_txn_ref.transaction_type_id,
l_txn_ref.transaction_source_type_id,
l_txn_ref.transaction_action_id,
l_txn_ref.wip_entity_id,
l_txn_ref.creation_date
FROM mtl_material_transactions
WHERE transaction_id = p_transaction_id;
SELECT master_organization_id
INTO l_txn_ref.master_organization_id
FROM mtl_parameters
WHERE organization_id = l_txn_ref.organization_id;
SELECT primary_uom_code,
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--commented for bug#16046156
revision_qty_control_code,
location_control_code,
comms_nl_trackable_flag,
bom_item_type,
segment1,
eam_item_type
INTO l_txn_ref.primary_uom_code,
l_txn_ref.srl_control_code,
l_txn_ref.lot_control_code,
l_txn_ref.rev_control_code,
l_txn_ref.loc_control_code,
l_txn_ref.ib_trackable_flag,
l_txn_ref.bom_item_type,
l_txn_ref.item,
l_txn_ref.eam_item_type
FROM mtl_system_items
WHERE inventory_item_id = l_txn_ref.inventory_item_id
AND organization_id = l_txn_ref.organization_id;
SELECT substr(wip_entity_name,1,50) wip_entity_name,
entity_type
INTO l_txn_ref.wip_entity_name,
l_txn_ref.wip_entity_type
FROM wip_entities
WHERE wip_entity_id = l_txn_ref.wip_entity_id
AND organization_id = l_txn_ref.organization_id;
SELECT primary_item_id,
quantity_completed,
quantity_completed,
status
INTO l_txn_ref.wip_assembly_item_id,
l_txn_ref.wip_start_quantity, -- wo less case compl qty is job qty
l_txn_ref.wip_completed_quantity,
l_txn_ref.wip_status_type
FROM wip_flow_schedules
WHERE wip_entity_id = l_txn_ref.wip_entity_id
AND organization_id = l_txn_ref.organization_id;
SELECT primary_item_id,
start_quantity,
quantity_completed,
job_type,
status_type,
nvl(maintenance_object_source, 0),
source_code,
source_line_id,
maintenance_object_type,
maintenance_object_id
INTO l_txn_ref.wip_assembly_item_id,
l_txn_ref.wip_start_quantity,
l_txn_ref.wip_completed_quantity,
l_txn_ref.wip_job_type,
l_txn_ref.wip_status_type,
l_txn_ref.wip_maint_source_code,
l_txn_ref.wip_source_code,
l_txn_ref.wip_source_line_id,
l_txn_ref.wip_maint_obj_type,
l_txn_ref.wip_maint_obj_id
FROM wip_discrete_jobs
WHERE wip_entity_id = l_txn_ref.wip_entity_id
AND organization_id = l_txn_ref.organization_id;
SELECT quantity + p_quantity,
object_version_number
INTO l_u_instance_rec.quantity,
l_u_instance_rec.object_version_number
FROM csi_item_instances
WHERE instance_id = p_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 => 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 quantity - p_quantity,
object_version_number
INTO l_u_instance_rec.quantity,
l_u_instance_rec.object_version_number
FROM csi_item_instances
WHERE instance_id = l_u_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 => 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);
l_c_parties_tbl.DELETE;
l_c_pty_accts_tbl.DELETE;
SELECT msn.serial_number child_serial_number
FROM mtl_object_genealogy mog,
mtl_serial_numbers msn,
mtl_system_items_b msib
WHERE mog.parent_object_type = 2
AND msib.inventory_item_id = msn.inventory_item_id
AND msib.organization_id = msn.current_organization_id
AND nvl(msib.comms_nl_trackable_flag, 'N') = 'Y'
AND mog.parent_object_id = p_parent_object_id
AND mog.object_type = 2
AND msn.gen_object_id = mog.object_id
AND mog.object_id <> mog.parent_object_id --- Added for bug#16445315
AND sysdate BETWEEN nvl(mog.start_date_active, sysdate-1)
AND nvl(mog.end_date_active, sysdate+1);
l_issues_tbl.DELETE(l_c_ind);
SELECT gen_object_id
INTO l_parent_object_id
FROM mtl_serial_numbers
WHERE inventory_item_id = l_assy_tbl(l_a_ind).inventory_item_id
AND serial_number = l_assy_tbl(l_a_ind).serial_number;
l_issues_tbl.DELETE(l_c_ind);
l_issues_tbl.DELETE(l_c_ind);
SELECT sum(nvl(quantity_per_assembly,0))
INTO l_qty_per_assy
FROM wip_requirement_operations
WHERE organization_id = p_organization_id
AND wip_entity_id = p_wip_entity_id
AND inventory_item_id = p_component_item_id;
SELECT wip.inventory_item_id,
sum(required_quantity) qty_required,
sum(quantity_issued) qty_issued,
nvl(sum(nvl(quantity_per_assembly,0)),0) qty_per_assy
FROM wip_requirement_operations wip, mtl_system_items msi
WHERE wip.wip_entity_id = p_wip_entity_id
AND wip.organization_id = p_organization_id
AND wip.inventory_item_id <> p_assembly_item_id
AND wip.inventory_item_id = msi.inventory_item_id
AND wip.organization_id = msi.organization_id
AND nvl(msi.comms_nl_trackable_flag, 'N') = 'Y'
AND (nvl(quantity_issued,0) > 0
OR
EXISTS (
SELECT 'X' FROM mtl_material_transactions mmt
WHERE mmt.transaction_action_id in (1,34)
AND mmt.transaction_source_type_id = 5
AND mmt.transaction_source_id = wip.wip_entity_id
AND mmt.inventory_item_id = wip.inventory_item_id))
GROUP BY wip.inventory_item_id;
SELECT wip.inventory_item_id,
sum(required_quantity) qty_required,
sum(quantity_issued) qty_issued,
nvl(sum(nvl(quantity_per_assembly,0)),0) qty_per_assy
FROM wip_requirement_operations wip, mtl_system_items msi
WHERE wip.wip_entity_id = p_wip_entity_id
AND wip.organization_id = p_organization_id
AND wip.inventory_item_id <> p_assembly_item_id
AND wip.inventory_item_id = msi.inventory_item_id
AND wip.organization_id = msi.organization_id
AND nvl(msi.comms_nl_trackable_flag, 'N') = 'Y'
AND nvl(quantity_per_assembly, 0) > 0
AND (nvl(quantity_issued,0) > 0
OR
EXISTS (
SELECT 'X' FROM mtl_material_transactions mmt
WHERE mmt.transaction_action_id in (1,34)
AND mmt.transaction_source_type_id = 5
AND mmt.transaction_source_id = wip.wip_entity_id
AND mmt.inventory_item_id = wip.inventory_item_id))
GROUP BY wip.inventory_item_id;
SELECT start_quantity
INTO l_job_quantity
FROM wip_discrete_jobs
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
SELECT sum(nvl(abs(transaction_quantity),0))
INTO l_requirements_tbl(l_ind).issued_quantity
FROM mtl_material_transactions mmt
WHERE mmt.transaction_action_id in (1,34)
AND mmt.transaction_source_type_id = 5
AND mmt.inventory_item_id = l_requirements_tbl(l_ind).inventory_item_id
AND mmt.transaction_source_id = p_wip_entity_id;
SELECT sum(nvl(abs(transaction_quantity),0))
INTO l_requirements_tbl(l_ind).issued_quantity
FROM mtl_material_transactions mmt
WHERE mmt.transaction_action_id in (1,34)
AND mmt.transaction_source_type_id = 5
AND mmt.inventory_item_id = l_requirements_tbl(l_ind).inventory_item_id
AND mmt.transaction_source_id = p_wip_entity_id;
l_c_parties_tbl.DELETE;
l_c_pty_accts_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;
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 => 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);
l_c_parties_tbl.DELETE;
l_c_pty_accts_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;
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 => 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 'Y'
INTO l_rltn_exists
FROM csi_ii_relationships
WHERE subject_id = p_assy_comp_map_tbl(l_ind).comp_instance_id
AND object_id = p_assy_comp_map_tbl(l_ind).assy_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND active_end_date is NULL OR active_end_date > sysdate;
SELECT msn.serial_number child_serial_number,
msn.inventory_item_id child_item_id
FROM mtl_object_genealogy mog,
mtl_serial_numbers msn,
mtl_system_items_b msib
WHERE mog.parent_object_type = 2
AND msib.inventory_item_id = msn.inventory_item_id
AND msib.organization_id = msn.current_organization_id
AND nvl(msib.comms_nl_trackable_flag, 'N') = 'Y'
AND mog.parent_object_id = p_parent_object_id
AND mog.object_type = 2
AND msn.gen_object_id = mog.object_id
AND mog.object_id <> mog.parent_object_id --- Added for bug#16445315
AND sysdate BETWEEN nvl(mog.start_date_active, sysdate-1)
AND nvl(mog.end_date_active, sysdate+1);
SELECT gen_object_id
INTO l_parent_object_id
FROM mtl_serial_numbers
WHERE inventory_item_id = l_assy_tbl(l_ind).inventory_item_id
AND serial_number = l_assy_tbl(l_ind).serial_number;
SELECT instance_id
INTO l_child_instance_id
FROM csi_item_instances
WHERE inventory_item_id = mog_rec.child_item_id
AND serial_number = mog_rec.child_serial_number;
SELECT gen_object_id,
parent_serial_number
INTO l_child_object_id,
l_parent_serial_number
FROM mtl_serial_numbers
WHERE inventory_item_id = p_child_item_id
and serial_number = p_child_serial_number;
SELECT msn.serial_number,
msn.inventory_item_id
INTO l_parent_serial_number,
l_parent_item_id
FROM mtl_object_genealogy mog,
mtl_serial_numbers msn,
mtl_system_items_b msib
WHERE mog.object_type = 2 -- serial genealogy
AND msib.inventory_item_id = msn.inventory_item_id
AND msib.organization_id = msn.current_organization_id
AND nvl(msib.comms_nl_trackable_flag, 'N') = 'Y'
AND mog.object_id = l_child_object_id
AND mog.parent_object_type = 2 -- serial genealogy
AND msn.gen_object_id = mog.parent_object_id
AND sysdate BETWEEN nvl(mog.start_date_active, sysdate-1)
AND nvl(mog.end_date_active, sysdate+1);
SELECT count(*)
INTO l_alloc_count
FROM csi_ii_relationships cir,
csi_item_instances cii
WHERE cir.object_id = p_object_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.instance_id = cir.subject_id
AND cii.inventory_item_id = p_comp_item_id;
l_comp_tbl.DELETE(l_c_ind);
l_comp_tbl.DELETE(l_c_ind);
l_comp_tbl.DELETE(l_c_ind);
l_comp_tbl.DELETE(l_c_ind);
SELECT cir.subject_id, cii.quantity
FROM csi_ii_relationships cir,
csi_item_instances cii
WHERE cir.object_id = p_object_id
AND cir.relationship_type_code = 'COMPONENT-OF'
AND cii.instance_id = cir.subject_id
AND cii.inventory_item_id = p_comp_item_id
AND nvl(cii.active_end_date,sysdate+1) > sysdate; --Added end date condition for bug 5376024
SELECT sum(cii.quantity)
INTO l_alloc_qty
FROM csi_ii_relationships cir,
csi_item_instances cii
WHERE cir.object_id = p_object_id
AND cir.relationship_type_code = 'COMPONENT-OF'
AND cii.instance_id = cir.subject_id
AND cii.inventory_item_id = p_comp_item_id;
SELECT serial_number_control_code
INTO l_comp_serial_code
FROM mtl_system_items
WHERE inventory_item_id = l_requirements_tbl(l_ind).inventory_item_id
AND organization_id = p_txn_ref.organization_id;
SELECT cir.subject_id, cii.quantity
FROM csi_ii_relationships cir,
csi_item_instances cii
WHERE cir.object_id = p_parent_instance_id
AND cir.relationship_type_code = 'COMPONENT-OF'
AND cii.instance_id = cir.subject_id
AND cii.inventory_item_id = p_component_item_id
AND cii.last_wip_job_id = p_wip_entity_id;
SELECT instance_id
INTO l_parent_instance_id
FROM csi_item_instances
WHERE inventory_item_id = l_parent_item_id
AND serial_number = l_parent_serial_number
AND nvl(active_end_date,sysdate+1) > sysdate; --fix for bug 5393515
SELECT sum(nvl(quantity_issued,0))
INTO l_total_qty_issued
FROM wip_requirement_operations
WHERE wip_entity_id = p_txn_ref.wip_entity_id
AND organization_id = p_txn_ref.organization_id
AND inventory_item_id = p_txn_ref.inventory_item_id;
SELECT inventory_item_id,
sum(abs(primary_quantity)) qty_issued
FROM mtl_material_transactions
WHERE transaction_action_id = 1
AND transaction_source_type_id = 5
AND transaction_source_id = p_wip_entity_id
GROUP BY inventory_item_id;
SELECT serial_number_control_code ,
segment1
INTO l_comp_serial_code,
l_comp_item
FROM mtl_system_items
WHERE inventory_item_id = issue_rec.inventory_item_id
AND organization_id = p_organization_id;
SELECT inventory_item_id,
sum(abs(primary_quantity)) qty_issued
FROM mtl_material_transactions
WHERE transaction_action_id = 1
AND transaction_source_type_id = 5
AND transaction_source_id = p_txn_ref.wip_entity_id
GROUP BY inventory_item_id;
SELECT transaction_id
FROM csi_transactions
WHERE inv_material_transaction_id = p_transaction_id;
SELECT transaction_id
FROM mtl_system_items msi,
mtl_material_transactions mmt
WHERE mmt.transaction_source_type_id = 5 -- job/schedule transactions
AND mmt.transaction_source_id = p_wip_entity_id
AND mmt.transaction_action_id in (1, 27, 31, 32, 33, 34) -- ib handled wip actions
AND mmt.creation_date < p_mtl_creation_date
AND mmt.transaction_date > p_migration_date
AND msi.organization_id = mmt.organization_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND nvl(msi.comms_nl_trackable_flag, 'N') = 'Y'
--Added for bug#14374084
AND EXISTS
( SELECT 1 FROM csi_item_instances
WHERE inventory_item_id = mmt.inventory_item_id
AND creation_date < mmt.creation_date) ;
SELECT transaction_id
FROM mtl_material_transactions
WHERE transaction_source_type_id = 5
AND transaction_action_id in (1, 34)
AND transaction_source_id = pc_wip_entity_id;
SELECT transaction_id
FROM mtl_material_transactions
WHERE transaction_action_id = 31
AND transaction_source_type_id = 5
AND transaction_source_id = pc_wip_entity_id;
SELECT 'Y'
INTO l_csi_txn_found
FROM sys.dual
WHERE exists (
SELECT 'X' FROM csi_transactions
WHERE inv_material_transaction_id = compl_rec.transaction_id);
SELECT 'Y'
INTO l_csi_txn_found
FROM sys.dual
WHERE exists (
SELECT 'X' FROM csi_transactions
WHERE inv_material_transaction_id = issue_rec.transaction_id);
SELECT serial_number_control_code
INTO l_assy_serial_code
FROM mtl_system_items
WHERE inventory_item_id = p_txn_ref.wip_assembly_item_id
AND organization_id = p_txn_ref.organization_id;
SELECT nvl(mmt_assem.primary_quantity,0)
INTO l_qty_completed
FROM mtl_material_transactions mmt_assem,
mtl_material_transactions mmt_comp
WHERE mmt_assem.transaction_action_id = 31
AND mmt_assem.transaction_source_type_id = 5
AND mmt_comp.transaction_source_id = p_txn_ref.wip_entity_id
AND mmt_comp.transaction_id = p_txn_ref.transaction_id
AND mmt_comp.completion_transaction_id = mmt_assem.completion_transaction_id; --5225921
SELECT entity_type
INTO l_entity_type
FROM wip_entities
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
SELECT * INTO l_mmt_rec
FROM mtl_material_transactions
WHERE transaction_id = p_transaction_id;
SELECT * INTO l_mmt_rec
FROM mtl_material_transactions
WHERE transaction_id = p_transaction_id;
SELECT * INTO l_mmt_rec
FROM mtl_material_transactions
WHERE transaction_id = p_transaction_id;
SELECT * INTO l_mmt_rec
FROM mtl_material_transactions
WHERE transaction_id = p_transaction_id;
SELECT object_id
INTO l_inst_tbl(l_ind).attribute1
FROM csi_ii_relationships
WHERE subject_id = l_inst_tbl(l_ind).instance_id
AND relationship_type_code = 'COMPONENT-OF';
l_inst_tbl.delete(l_f_ind);
SELECT mmt.inventory_item_id inventory_item_id,
mut.serial_number serial_number
--INTO l_inv_item_id, l_serial_number
FROM mtl_material_transactions mmt,
mtl_unit_transactions mut
WHERE mmt.transaction_id = mut.transaction_id
AND mmt.transaction_set_id = p_transaction_set_id
AND mmt.transaction_type_id = 17;
select cir.subject_id,ci2.quantity
from csi_item_instances ci1, csi_item_instances ci2, csi_ii_relationships cir
where ci1.instance_id = cir.object_id
and ci1.inventory_item_id = p_inventory_item_id
and ci1.serial_number = p_serial_number
and ci2.inventory_item_id = p_mmt_rec.inventory_item_id
and ci2.instance_id = cir.subject_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 sysdate between nvl(ci2.active_start_date, sysdate-1)
and nvl(ci2.active_end_date, sysdate+1);
SELECT 'Y', object_id, active_start_date, active_end_date
INTO l_relation_found , l_parent_instance_id, l_active_start_date,
l_active_end_date
FROM csi_ii_relationships
WHERE subject_id = l_instances_tbl(1).instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
AND nvl(active_end_date, sysdate+1);
l_parties_tbl.delete;
select mmt.inventory_item_id, mut.serial_number
into l_inv_item_id, l_serial_number
from mtl_material_transactions mmt, mtl_unit_transactions mut
where mmt.transaction_id = mut.transaction_id
and mmt.transaction_set_id = p_mmt_rec.transaction_set_id
and mmt.transaction_type_id = 17;
select cir.subject_id,ci2.quantity
into l_instance_id, l_quantity
from csi_item_instances ci1, csi_item_instances ci2, csi_ii_relationships cir
where ci1.instance_id = cir.object_id
and ci1.inventory_item_id = l_inv_item_id
and ci1.serial_number = l_serial_number
and ci2.inventory_item_id = p_mmt_rec.inventory_item_id
and ci2.instance_id = cir.subject_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 sysdate between nvl(ci2.active_start_date, sysdate-1)
and nvl(ci2.active_end_date, sysdate+1);
SELECT 'Y'
INTO l_comp_is_assy
FROM sys.dual
WHERE exists (
SELECT 'X'
FROM csi_item_instances
WHERE last_vld_organization_id = p_mmt_rec.organization_id
AND location_type_code = 'WIP'
AND wip_job_id = p_mmt_rec.transaction_source_id
AND serial_number = p_mmt_rec.serial_number
AND inventory_item_id = p_mmt_rec.inventory_item_id);
SELECT quantity,
last_vld_organization_id
INTO l_sub_instance_qty,
l_vld_organization_id
FROM csi_item_instances
WHERE instance_id = l_sub_instance_id;
UPDATE csi_t_txn_line_details
SET instance_id = l_sub_instance_id
WHERE txn_line_detail_id = l_sub_tld_id;
SELECT relationship_id,
object_version_number
INTO l_iir_rec.relationship_id,
l_iir_rec.object_version_number
FROM csi_ii_relationships
WHERE object_id = l_obj_instance_id
AND subject_id = l_sub_instance_id;
PROCEDURE update_tld_status(
p_tld_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl,
x_return_status OUT NOCOPY varchar2)
IS
BEGIN
IF p_tld_tbl.COUNT > 0 THEN
FOR l_ind IN p_tld_tbl.FIRST .. p_tld_tbl.LAST
LOOP
UPDATE csi_t_txn_line_details
SET processing_status = 'PROCESSED'
WHERE txn_line_detail_id = p_tld_tbl(l_ind).txn_line_detail_id;
END update_tld_status;
SELECT wip_entity_name
INTO l_wip_entity_name
FROM wip_entities
WHERE wip_entity_id = p_wip_entity_id;
debug('update_relationship.COUNT :'||l_u_iir_tbl.COUNT);
p_api_name => 'update_relationship');
debug('No code here yet for update relationship...');
update_tld_status(
p_tld_tbl => l_tld_tbl,
x_return_status => l_return_status);
SELECT transaction_id
FROM mtl_system_items msi,
mtl_material_transactions mmt
WHERE mmt.transaction_source_type_id = 5 -- job/schedule transactions
AND mmt.transaction_source_id = p_wip_entity_id
AND mmt.transaction_action_id in (1, 27, 31, 32, 33, 34) -- ib handled wip actions
AND mmt.transaction_date > p_migration_date
AND msi.organization_id = mmt.organization_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND nvl(msi.comms_nl_trackable_flag, 'N') = 'Y';
SELECT instance_id,
quantity,
serial_number
FROM csi_item_instances
WHERE location_type_code = 'WIP'
AND wip_job_id = p_wip_entity_id
AND instance_id <> p_instance_id;
SELECT wip_entity_name
INTO l_csi_txn_rec.source_header_ref
FROM wip_entities
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
SELECT maintenance_object_id
INTO l_instance_id
FROM wip_discrete_jobs
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
SELECT wip_entity_name
INTO l_csi_txn_rec.source_header_ref
FROM wip_entities
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;