[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select msg_id,
msg_code,
msg_status,
body_text,
creation_date,
description
From xnp_msgs
Where (msg_code Like 'CSI%' OR msg_code Like 'CSE%')
And nvl(msg_status, 'READY') <> 'PROCESSED'
And recipient_name Is Null;
Insert Into csi_xnp_msgs_temp(
msg_id,
msg_code,
msg_text,
msg_status,
source_id,
source_type,
creation_date,
description,
process_flag
)
Values
(
msg_rec.msg_id,
msg_rec.msg_code,
l_msg_text,
msg_rec.msg_status,
l_source_id,
l_source_type,
msg_rec.creation_date,
msg_rec.description,
'Y'
);
Select moq.organization_id organization_id
, moq.inventory_item_id inventory_item_id
, moq.revision revision
, moq.subinventory_code subinventory_code
, moq.locator_id locator_id
, moq.lot_number lot_number
, msi.primary_uom_code primary_uom_code
, SUM(moq.transaction_quantity) onhand_qty
From
mtl_system_items msi
, mtl_onhand_quantities moq
Where msi.inventory_item_id = moq.inventory_item_id
And msi.organization_id = moq.organization_id
And msi.serial_number_control_code in (1,6) -- No Serial control and at SO Issue Items
Group By
moq.organization_id
, moq.inventory_item_id
, moq.revision
, moq.subinventory_code
, moq.locator_id
, moq.lot_number
, msi.primary_uom_code;
Select master_organization_id
Into v_mast_org_id
From MTL_PARAMETERS
Where organization_id = organization_id_mig(i);
Select comms_nl_trackable_flag
Into v_nl_trackable
From MTL_SYSTEM_ITEMS
Where inventory_item_id = inventory_item_id_mig(i)
And organization_id = v_mast_org_id; -- check should it be org. id
Select Count(*)
Into l_error_count
From CSI_TXN_ERRORS csi,
MTL_MATERIAL_TRANSACTIONS mmt
Where csi.inv_material_transaction_id Is Not Null
And csi.inv_material_transaction_id = mmt.transaction_id
And csi.processed_flag IN ('E','R')
And mmt.inventory_item_id = inventory_item_id_mig(i)
And mmt.organization_id = organization_id_mig(i);
Select Count(*)
Into l_error_count
From CSI_XNP_MSGS_TEMP xnp,
MTL_MATERIAL_TRANSACTIONS mmt
Where xnp.source_id = mmt.transaction_id
And xnp.source_type = 'MTL_TRANSACTION_ID'
And mmt.inventory_item_id = inventory_item_id_mig(i)
And mmt.organization_id = organization_id_mig(i)
And nvl(xnp.msg_status, 'READY') <> 'PROCESSED';
Select instance_id
, inventory_item_id
, inventory_revision
, lot_number
, quantity
, active_end_date
, location_type_code
, inv_organization_id
, inv_subinventory_name
, inv_locator_id
, instance_usage_code
Into v_inst_id
, v_inv_item_id
, v_inv_rev
, v_inv_lot_num
, v_inst_qty
, v_end_date
, v_loc_type
, v_inv_org_id
, v_inv_subinv_name
, v_inv_locator_id
, v_inst_usage
From csi_item_instances
Where inventory_item_id = inventory_item_id_mig(i)
And last_vld_organization_id = organization_id_mig(i)
And location_type_code = 'INVENTORY'
And instance_usage_code = 'IN_INVENTORY'
And inv_subinventory_name = subinv_mig(i)
And nvl(inv_locator_id,-999) = nvl(locator_id_mig(i),-999)
And nvl(inventory_revision,'$#$') = nvl(revision_mig(i),'$#$')
And nvl(lot_number,'$#$') = nvl(lot_mig(i),'$#$')
And ((active_end_date IS NULL) OR (active_end_date > SYSDATE));
Insert Into CSI_INV_DISCREPANCY_TEMP
(
discrepancy_id
,inventory_item_id
,serial_number
,inv_revision
,inv_lot_number
,inv_quantity
,inv_organization_id
,inv_subinventory_name
,inv_locator_id
,instance_id
,ii_revision
,ii_lot_number
,ii_quantity
,ii_organization_id
,ii_subinventory_name
,ii_locator_id
,ii_location_type_code
,instance_usage_code
,master_org_trackable_flag
,child_org_trackable_flag
)
Values
(
csi_inv_discrepency_temp_s.Nextval
,inventory_item_id_mig(i)
,Null
,revision_mig(i)
,lot_mig(i)
,quantity_mig(i)
,organization_id_mig(i)
,subinv_mig(i)
,locator_id_mig(i)
,v_inst_id
,v_inv_rev
,v_inv_lot_num
,v_inst_qty
,v_inv_org_id
,v_inv_subinv_name
,v_inv_locator_id
,v_loc_type
,v_inst_usage
,v_nl_trackable
,v_nl_trackable
);
v_err_msg := 'Unable to Insert a record into the IB_INV_SYNC table'||SUBSTR(sqlerrm,1,1000);
End; -- end of insert into ib_inv_sync table
SELECT last_vld_organization_id,
inv_organization_id,
inv_subinventory_name,
inventory_revision,
inv_locator_id,
location_type_code,
instance_usage_code,
location_id,
lot_number
INTO l_vld_organization_id,
l_inv_organization_id,
l_inv_subinventory_name,
l_inventory_revision,
l_inv_locator_id,
l_location_type_code,
l_instance_usage_code,
l_location_id,
l_lot_number
FROM csi_item_instances
WHERE instance_id = p_instance_rec.instance_id;
SELECT msn.serial_number serial_number,
msn.inventory_item_id inventory_item_id,
msk.concatenated_segments item_name,
msn.current_organization_id organization_id,
msn.revision revision,
msn.current_subinventory_code subinventory_code,
msn.current_locator_id locator_id,
msn.lot_number lot_number,
msi.primary_uom_code uom_code,
msi.serial_number_control_code serial_code,
msi.lot_control_code lot_code
FROM mtl_system_items msi,
mtl_serial_numbers msn,
mtl_system_items_kfv msk
WHERE msi.inventory_item_id = msn.inventory_item_id
AND msi.organization_id = msn.current_organization_id
AND msi.inventory_item_id = msk.inventory_item_id
AND msi.organization_id = msk.organization_id
AND msi.serial_number_control_code IN (2,5)
AND msn.current_status = 3
AND EXISTS (
SELECT '1'
FROM mtl_parameters mp,
mtl_system_items msi_mast
WHERE mp.organization_id = msi.organization_id
AND msi_mast.inventory_item_id = msi.inventory_item_id
AND msi_mast.organization_id = mp.master_organization_id)
-- AND nvl(msi_mast.comms_nl_trackable_flag,'N') = 'Y') --commented to query all the non-trackable items
AND EXISTS (
SELECT '1'
FROM mtl_onhand_quantities moq
WHERE moq.inventory_item_id = msn.inventory_item_id
AND moq.organization_id = msn.current_organization_id
AND moq.subinventory_code = msn.current_subinventory_code
AND nvl(moq.locator_id,-999) = nvl(msn.current_locator_id,-999)
AND nvl(moq.lot_number,'$#$') = nvl(msn.lot_number,'$#$')
AND nvl(moq.revision,'$#$') = nvl(msn.revision,'$#$') );
SELECT /*+ parallel(mut) parallel(mmt) parallel(mtt) */
mmt.transaction_id mtl_txn_id,
mmt.transaction_date mtl_txn_date,
mmt.inventory_item_id item_id,
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,
mmt.trx_source_line_id mtl_source_line_id,
mmt.transaction_quantity mtl_txn_qty,
mtt.type_class mtl_type_class,
mmt.transfer_transaction_id mtl_xfer_txn_id,
to_char(null) lot_number,
to_char(mmt.transaction_date,'dd-mm-yy hh24:mi:ss') mtl_txn_char_date
FROM mtl_unit_transactions mut,
mtl_material_transactions mmt,
mtl_transaction_types mtt
WHERE p_lot_code = g_no_lot
AND 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 /*+ parallel(mut) parallel(mtln) parallel(mmt) parallel(mtt) */
mmt.transaction_id mtl_txn_id,
mmt.transaction_date mtl_txn_date,
mmt.inventory_item_id item_id,
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,
mmt.trx_source_line_id mtl_source_line_id,
mmt.transaction_quantity mtl_txn_qty,
mtt.type_class mtl_type_class,
mmt.transfer_transaction_id mtl_xfer_txn_id,
mtln.lot_number lot_number,
to_char(mmt.transaction_date,'dd-mm-yy hh24:mi:ss') mtl_txn_char_date
FROM mtl_unit_transactions mut,
mtl_transaction_lot_numbers mtln,
mtl_material_transactions mmt,
mtl_transaction_types mtt
WHERE p_lot_code = g_lot
AND 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;
SELECT master_organization_id
INTO v_mast_org_id
FROM MTL_PARAMETERS
WHERE organization_id = srl_rec.organization_id;
SELECT comms_nl_trackable_flag
INTO v_nl_trackable
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = srl_rec.inventory_item_id
AND organization_id = v_mast_org_id;
SELECT count(1)
INTO l_inst_count
FROM CSI_ITEM_INSTANCES
WHERE inventory_item_id = srl_rec.inventory_item_id
AND ((active_end_date IS NULL) OR (active_end_date > 'SYSDATE'))
AND ROWNUM < 2;
SELECT instance_id ,
object_version_number
INTO l_instance_rec.instance_id,
l_instance_rec.object_version_number
FROM CSI_ITEM_INSTANCES
WHERE inventory_item_id = srl_rec.inventory_item_id
AND serial_number = srl_rec.serial_number;
SELECT concatenated_segments
INTO v_item_name
FROM MTL_SYSTEM_ITEMS_KFV
WHERE inventory_item_id = srl_rec.inventory_item_id
AND organization_id = srl_rec.organization_id;
SELECT last_vld_organization_id,
inventory_item_id,
inv_organization_id,
inv_subinventory_name,
inventory_revision,
inv_locator_id,
location_type_code,
instance_usage_code,
location_id,
lot_number,
serial_number,
quantity
INTO l_vld_organization_id,
l_inventory_item_id,
l_inv_organization_id,
l_inv_subinventory_name,
l_inventory_revision,
l_inv_locator_id,
l_location_type_code,
l_instance_usage_code,
l_location_id,
l_lot_number,
l_serial_number,
l_quantity
FROM CSI_ITEM_INSTANCES
WHERE instance_id = l_instance_rec.instance_id;
SELECT COUNT(*)
INTO l_error_count
FROM CSI_TXN_ERRORS csi
WHERE csi.inv_material_transaction_id IS NOT NULL
AND csi.inv_material_transaction_id = all_txn.mtl_txn_id
AND csi.processed_flag IN ('E','R');
SELECT COUNT(*)
INTO l_error_count
FROM CSI_XNP_MSGS_TEMP xnp
WHERE xnp.source_id IS NOT NULL
AND xnp.source_id = all_txn.mtl_txn_id
AND xnp.source_type = 'MTL_TRANSACTION_ID'
AND nvl(xnp.msg_status, 'READY') <> 'PROCESSED';
INSERT INTO CSI_INV_DISCREPANCY_TEMP
(
discrepancy_id
,inventory_item_id
,serial_number
,inv_revision
,inv_lot_number
,inv_quantity
,inv_organization_id
,inv_subinventory_name
,inv_locator_id
,instance_id
,ii_revision
,ii_lot_number
,ii_quantity
,ii_organization_id
,ii_subinventory_name
,ii_locator_id
,ii_location_type_code
,instance_usage_code
,master_org_trackable_flag
,child_org_trackable_flag
)
VALUES
(
csi_inv_discrepency_temp_s.Nextval
,l_instance_rec.inventory_item_id
,l_instance_rec.serial_number
,l_instance_rec.inventory_revision
,l_instance_rec.lot_number
,l_instance_rec.quantity
,l_instance_rec.inv_organization_id
,l_instance_rec.inv_subinventory_name
,l_instance_rec.inv_locator_id
,l_instance_rec.instance_id
,l_inventory_revision
,l_lot_number
,l_quantity
,l_inv_organization_id
,l_inv_subinventory_name
,l_inv_locator_id
,l_location_type_code
,l_instance_usage_code
,v_nl_trackable
,v_nl_trackable
);
v_err_msg := 'Unable to Insert a record into the CSI_INV_DISCREPENCY_TEMP table'||SUBSTR(sqlerrm,1,1000);
END; -- end of insert into ib_inv_sync table
select STATUS_CODE
into l_comp_stat
from fnd_concurrent_requests
where request_id = to_number(document_id);
Select CSI_WF_ITEM_KEY_NUMBER_S.Nextval
Into l_itemkey_seq
From dual;
SELECT count(*)
INTO l_disc_count
FROM CSI_INV_DISCREPANCY_TEMP;