[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT inventory_item_id
INTO l_Parent_inventory_item_id
FROM CSI_ITEM_INSTANCES
WHERE instance_id=p_parent_instance_id ;
DELETE FROM EAM_ASSET_MOVE_TEMP;
SELECT APPS.mtl_material_transactions_s.NEXTVAL
INTO l_header_id FROM sys.dual;
'Selecting the intrasit type from mtl_shipping_network ');
SELECT intransit_type INTO l_intransit_type
FROM MTL_SHIPPING_NETWORK_VIEW WHERE
FROM_organization_id = p_curr_org_id
AND to_organization_id = p_dest_org_id;
SELECT Count(*) INTO l_eam_asset_move_count
FROM EAM_ASSET_MOVE_TEMP;
SELECT Count(*) INTO l_prepare_count
FROM EAM_ASSET_MOVE_TEMP
WHERE PREPARE_STATUS = 'N';
SELECT cii.instance_id ,
cii.serial_number ,
msn.gen_object_id ,
cii.inventory_item_id ,
msn.CURRENT_ORGANIZATION_ID,
cii.INV_SUBINVENTORY_NAME ,
cii.maintainable_flag ,
msi.eam_item_type ,
mp.MAINT_ORGANIZATION_ID
FROM mtl_serial_numbers msn ,
mtl_object_genealogy mog,
mtl_system_items_b msi ,
csi_item_instances cii ,
mtl_parameters mp
WHERE mog.object_id = msn.gen_object_id
AND msn.current_organization_id = msi.organization_id
--AND msn.current_organization_id = p_current_org_id
AND msi.inventory_item_id = msn.inventory_item_id
AND msi.eam_item_type IN (1,3)
AND msn.inventory_item_id = cii.inventory_item_id
AND msn.serial_number = cii.serial_number
AND NVL(cii.active_start_date, sysdate-1) <= sysdate
AND NVL(cii.active_end_date, sysdate +1) >= sysdate
AND msn.current_organization_id = mp.organization_id
AND mp.organization_id = cii.last_vld_organization_id
AND sysdate >= NVL(mog.start_date_active(+), sysdate)
AND sysdate <= NVL(mog.end_date_active(+), sysdate) START
WITH mog.parent_object_id = l_parent_object_id CONNECT BY prior mog.object_id = mog.parent_object_id ;
SELECT msn.GEN_OBJECT_ID
INTO l_parent_object_id
FROM mtl_serial_numbers msn,
csi_item_instances cii
WHERE cii.instance_id =p_parent_instance_id
AND cii.serial_number=msn.serial_number
AND cii.inventory_item_id=msn.inventory_item_id; --Added for 6955393
SELECT 0
INTO l_parent_object_id
FROM dual;
SELECT cii.instance_id ,
cii.serial_number ,
msn.gen_object_id ,
cii.inventory_item_id ,
nvl(cii.inv_organization_id, cii.last_vld_organization_id),
cii.INV_SUBINVENTORY_NAME ,
cii.maintainable_flag ,
msi.eam_item_type ,
mp.MAINT_ORGANIZATION_ID ,
NULL ,
NULL
INTO l_parent_inf_rec
FROM CSI_ITEM_INSTANCES cii,
MTL_PARAMETERS mp ,
MTL_SERIAL_NUMBERS msn,
MTL_SYSTEM_ITEMS_B msi
WHERE cii.instance_id =p_parent_instance_id
AND cii.SERIAL_NUMBER = msn.SERIAL_NUMBER
AND mp.organization_id =cii.last_vld_organization_id
AND msn.current_organization_id = msi.organization_id
AND msi.inventory_item_id = msn.inventory_item_id
AND msn.inventory_item_id =cii.inventory_item_id --6955393
AND msi.eam_item_type IN (1,3);
SELECT *
FROM EAM_ASSET_MOVE_TEMP FOR UPDATE OF PREPARE_STATUS,
PREPARE_MSG;
SELECT INTERMEDIATE_SUBINVENTORY
INTO l_intermediate_subinventory FROM WIP_EAM_PARAMETERS
WHERE ORGANIZATION_ID = p_asset_move_hierarchy_tbl(i).maint_org_id ;
INSERT
INTO EAM_ASSET_MOVE_TEMP
(
INSTANCE_ID ,
SERIAL_NUMBER ,
GEN_OBJECT_ID ,
INVENTORY_ITEM_ID ,
CURRENT_ORG_ID ,
CURRENT_SUBINVENTORY_CODE,
EAM_ITEM_TYPE ,
MAINT_ORG_ID ,
PREPARE_STATUS ,
PREPARE_MSG ,
TRANSACTION_HEADER_ID
)
VALUES
(
p_asset_move_hierarchy_tbl(i).instance_id ,
p_asset_move_hierarchy_tbl(i).serial_number ,
p_asset_move_hierarchy_tbl(i).gen_object_id ,
p_asset_move_hierarchy_tbl(i).inventory_item_id ,
p_asset_move_hierarchy_tbl(i).current_org_id ,
NVL(p_asset_move_hierarchy_tbl(i).current_subinventory_code,l_intermediate_subinventory),
/*for 7370638-AMWB-MR --intermediate_subinventory is the place where the
Asset is recieved if the asset is not present any of the subinventory and
from there asset is transferred to dest_subinv and/or dest-org.*/
NVL(p_asset_move_hierarchy_tbl(i).eam_item_type,1) ,
p_asset_move_hierarchy_tbl(i).maint_org_id ,
p_asset_move_hierarchy_tbl(i).prepare_status ,
p_asset_move_hierarchy_tbl(i).prepare_msg ,
p_header_id
);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'selecting the parent objectId for validating child asset'||child_parent_rec.INSTANCE_ID );
SELECT parent_object_id
INTO l_parent_object_id
FROM mtl_object_genealogy
WHERE object_id =child_parent_rec.gen_object_id
AND START_DATE_ACTIVE<=SYSDATE
AND Nvl(end_DATE_ACTIVE,SYSDATE+1)>=sysdate
AND PARENT_OBJECT_TYPE = 2; --Added for the bug 7721062
--AND parent_object_id IN (SELECT gen_object_id FROM mtl_serial_numbers)
IF (l_slog) then
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'parent objectId for validating the child assest is '||l_parent_object_id);
SELECT PREPARE_STATUS
INTO l_parent_status
FROM EAM_ASSET_MOVE_TEMP
WHERE gen_object_id=l_parent_object_id;
SELECT 'U' INTO l_parent_status FROM dual;
SELECT PREPARE_MSG
INTO l_parent_msg
FROM EAM_ASSET_MOVE_TEMP
WHERE gen_object_id=l_parent_object_id;
SELECT 'Unknown' INTO l_parent_msg FROM dual;
UPDATE EAM_ASSET_MOVE_TEMP
SET PREPARE_STATUS = 'N',
PREPARE_MSG ='EAM_PAR_ASSET_FAIL'
WHERE CURRENT OF child_parent_cur;
SELECT *
FROM EAM_ASSET_MOVE_TEMP
WHERE prepare_status IN ('Y','MR')
AND TRANSACTION_HEADER_ID = p_header_id FOR UPDATE OF TRANSACTION_INTERFACE_ID;
select MAINT_ORGANIZATION_ID into l_maint_organization_id
from MTL_PARAMETERS
where ORGANIZATION_ID = P_CURRENT_ORGANIZATION_ID;
select INTERMEDIATE_SUBINVENTORY into l_intermediate_subinventory
from wip_eam_parameters
where ORGANIZATION_ID = l_maint_organization_id;
select INTERMEDIATE_LOCATOR into l_intermediate_locator
from wip_eam_parameters
where ORGANIZATION_ID = l_maint_organization_id;
SELECT inventory_location_id
INTO l_intermediate_locator_id
FROM mtl_item_locations_kfv
WHERE concatenated_segments = l_intermediate_locator
AND organization_id = l_maint_organization_id;
SELECT COUNT(*) into l_asset_count_for_MR FROM EAM_ASSET_MOVE_TEMP WHERE prepare_status LIKE 'MR';
select INV_LOCATOR_ID INTO l_current_locator_id
from csi_item_instances where INSTANCE_ID=validAssets_rec.instance_id;
SELECT mtl_material_transactions_s.nextval
INTO l_transaction_batch_id
FROM dual;
SELECT primary_uom_code ,
serial_number_control_code,
LOT_CONTROL_CODE
INTO l_Transaction_UOM ,
l_serial_number_control_code,
l_lot_control_code
FROM mtl_system_items_b
WHERE inventory_item_id=p_inventory_item_id
AND organization_id =p_CURRENT_ORGANIZATION_ID;
--Dbms_Output.put_line('ecxeption occured while selecting');
-- SELECT INVENTORY_REVISION INTO l_Item_Revision FROM CSI_ITEM_INSTANCES WHERE INVENTORY_ITEM_ID=p_inventory_item_id ;
SELECT mtl_material_transactions_s.nextval
INTO l_temp_header_id
FROM dual;
SELECT inventory_item_id INTO l_inventory_item_id
FROM csi_item_instances
WHERE instance_id = validAssets_rec.instance_id;
UPDATE EAM_ASSET_MOVE_TEMP
SET TRANSACTION_INTERFACE_ID = l_temp_header_id
WHERE CURRENT OF validAssets_cur;
SELECT mtl_material_transactions_s.nextval
INTO INV_TRANSACTIONS.G_Serial_ID
FROM dual;
-- insert into MTI, MSNI
--dbms_output.put_line('header_id IS' ||INV_TRANSACTIONS.G_Header_ID);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Calling the procedure INV_TRANSACTIONS.LINE_INTERFACE_INSERT' );
SELECT
intransit_type
INTO
l_intransit_type_for_child
FROM MTL_SHIPPING_NETWORK_VIEW
WHERE from_organization_id=l_CURRENT_ORGANIZATION_ID
AND TO_ORGANIZATION_ID=p_CURRENT_ORGANIZATION_ID;
SELECT maint_organization_id INTO l_CURRENT_ORGANIZATION_ID
FROM MTL_PARAMETERS
WHERE organization_id= l_CURRENT_ORGANIZATION_ID;
INV_TRANSACTIONS.LINE_INTERFACE_INSERT(
l_inventory_item_id,
NULL, --revision
l_CURRENT_ORGANIZATION_ID,
NULL, -- l_Transaction_Source_Id,
NULL, -- l_Transaction_action_Id,
l_current_subinventory_code ,
p_transfer_subinventory_code ,
l_current_locator_id,--NULL, --l_From_Locator_Id, can be null and cant be null
p_transfer_locator_id, --l_To_Locator_Id, from EAMMATTB.pls as in eam_mtl_txn_process.PROCESSMTLTXN()
l_transfer_organization_id,
l_transaction_type_id,
NULL, --l_Transaction_Source_Type_Id
l_quantity, --1 (quantity default)
l_Transaction_UOM, --from select query
SYSDATE,
NULL, --l_Reason_Id
FND_GLOBAL.USER_ID,
x_Message,
x_Status
);
UPDATE MTL_TRANSACTIONS_INTERFACE
SET SHIPMENT_NUMBER=p_shipment_number where TRANSACTION_INTERFACE_ID=INV_TRANSACTIONS.G_Interface_ID
AND TRANSACTION_HEADER_ID=INV_TRANSACTIONS.G_Header_ID;
UPDATE MTL_TRANSACTIONS_INTERFACE
SET TRANSACTION_BATCH_ID = l_transaction_batch_id,
TRANSACTION_BATCH_SEQ = 2
WHERE TRANSACTION_INTERFACE_ID=INV_TRANSACTIONS.G_Interface_ID
AND TRANSACTION_HEADER_ID=INV_TRANSACTIONS.G_Header_ID;
INV_TRANSACTIONS.SERIAL_INTERFACE_INSERT(
l_from_ser_number ,
l_to_ser_number ,
FND_GLOBAL.USER_ID ,
l_lot_control_code
);
SELECT MTI.TRANSACTION_HEADER_ID ,
MTI.TRANSACTION_INTERFACE_ID,
MTI.ERROR_CODE ,
MTI.ERROR_EXPLANATION
FROM MTL_TRANSACTIONS_INTERFACE MTI
WHERE MTI.TRANSACTION_HEADER_ID = p_txn_header_id ;
-- Update the Temporary Table with the transaction status
--*******************important part**********************************
-- dbms_output.put_line('updating EAM_ASSET_MOVE_TEMP after');
UPDATE EAM_ASSET_MOVE_TEMP
SET TRANSACTION_STATUS ='Failed',--NVL(Txn_STAT_MTT_REC.ERROR_CODE,'YES'),
TRANSACTION_MSG =Txn_STAT_MTT_REC.ERROR_EXPLANATION
WHERE TRANSACTION_INTERFACE_ID = Txn_STAT_MTT_REC.TRANSACTION_INTERFACE_ID;
SELECT
negative_inv_receipt_code,stock_locator_control_code into
x_neg_flag,x_org_ctrl FROM MTL_PARAMETERS
WHERE
organization_id = p_org;
SELECT
locator_type into x_sub_ctrl
FROM MTL_SECONDARY_INVENTORIES
WHERE
organization_id = p_org and
secondary_inventory_name = p_subinv ;
SELECT
location_control_code,restrict_locators_code into
x_item_ctrl,x_restrict_flag
FROM MTL_SYSTEM_ITEMS
WHERE
inventory_item_id = p_item_id and
organization_id = p_org;
SELECT maint_organization_id INTO l_org_id
FROM MTL_PARAMETERS WHERE
organization_id= p_CURRENT_ORGANIZATION_ID;
SELECT ACCT_PERIOD_ID
INTO l_acc_per_id
FROM ORG_ACCT_PERIODS
WHERE PERIOD_CLOSE_DATE IS NULL
AND ORGANIZATION_ID = l_org_id
AND (SCHEDULE_CLOSE_DATE + 1) > l_sysdate
AND PERIOD_START_DATE <= l_sysdate ;
SELECT APPS.mtl_material_transactions_s.NEXTVAL
INTO l_txn_if_id
FROM sys.dual;
INSERT INTO mtl_transactions_interface
(transaction_header_id,
source_code,
source_line_id,
source_header_id,
process_flag ,
transaction_mode,
lock_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
organization_id,
inventory_item_id,
--distribution_account_id,
subinventory_code,
locator_id,
transaction_quantity,
transaction_uom,
transaction_date,
transaction_type_id,
transaction_action_id,
transaction_source_type_id,
transaction_interface_id,
transaction_batch_id,
TRANSACTION_BATCH_SEQ
)
VALUES
(p_header_id,
1,
-1,
-1,
1,
3,
2,
l_sysdate,
FND_GLOBAL.USER_ID,
l_sysdate,
FND_GLOBAL.USER_ID,
l_org_id,
p_inventory_item_id,
--20594,
p_current_subinventory_code,
p_intermediate_locator_id,
l_qty,
l_uom,
l_sysdate,
42,
27,
13,
l_txn_if_id,
p_batch_transaction_id,
1
);
INSERT INTO mtl_serial_numbers_interface
(transaction_interface_id,
SOURCE_CODE,
SOURCE_LINE_ID,
last_update_date,
last_updated_by,
creation_date,
created_by,
fm_serial_number,
to_serial_number,
ERROR_CODE,
PROCESS_FLAG)
VALUES
(l_txn_if_id, --l_txn_ser_if_id
'HJO', --optional value
1,
l_sysdate,
FND_GLOBAL.USER_ID,
l_sysdate,
FND_GLOBAL.USER_ID,
p_serial_number,
p_serial_number,
NULL,
1
);