[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 1
into count_assigned
from dual
where exists (select 1
from mtl_secondary_inventories
where status_id = p_status_id);
select 1
into count_assigned
from dual
where exists (select 1
from mtl_item_locations
where status_id = p_status_id);
select 1
into count_assigned
from dual
where exists (select 1
from mtl_lot_numbers
where status_id = p_status_id);
select 1
into count_assigned
from dual
where exists (select /*+ first_rows(1) */ 1
from mtl_serial_numbers
where status_id = p_status_id);
select 1
into count_assigned
from dual
where exists (select 1
from mtl_onhand_quantities_detail moqd, mtl_parameters mp
where moqd.organization_id = mp.organization_id
and mp.default_status_id is not null
and nvl(moqd.status_id, -9999) = p_status_id
and rownum = 1); -- Do we need to add rownum as the query is inside 'exists'.
SELECT default_loc_status_id
INTO l_status_id
FROM MTL_SECONDARY_INVENTORIES
WHERE organization_id = p_organization_id
AND secondary_inventory_name = p_sub_code;
IN OUT NOCOPY INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type )
IS
BEGIN
if px_status_rec.organization_id = fnd_api.g_miss_num THEN
px_status_rec.organization_id := NULL;
if px_status_rec.update_method = fnd_api.g_miss_num then
px_status_rec.update_method := NULL;
if px_status_rec.last_updated_by = fnd_api.g_miss_num then
px_status_rec.last_updated_by := FND_GLOBAL.USER_ID;
px_status_rec.last_update_date := SYSDATE;
if px_status_rec.last_update_login = fnd_api.g_miss_num then
px_status_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
if px_status_rec.update_reason_id = fnd_api.g_miss_num then
px_status_rec.update_reason_id := NULL;
PROCEDURE Insert_status_history(p_status_rec
IN INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type )
IS
l_status_rec INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type;
SELECT serial_number
FROM MTL_SERIAL_NUMBERS
WHERE current_organization_id = p_status_rec.organization_id
AND inventory_item_id = p_status_rec.inventory_item_id
AND serial_number > p_status_rec.serial_number
AND serial_number <= p_status_rec.to_serial_number;
l_status_update_id NUMBER := NULL; -- SCHANDRU INVERES
Select MTL_MATERIAL_STATUS_HISTORY_S.nextval
Into l_status_update_id
From dual;
Select mtl_onhand_status_group_s.NEXTVAL
Into l_status_group_id FROM dual;
INSERT INTO MTL_MATERIAL_STATUS_HISTORY
(
STATUS_UPDATE_ID
,ORGANIZATION_ID
,INVENTORY_ITEM_ID
,LOT_NUMBER
,SERIAL_NUMBER
,UPDATE_METHOD
,STATUS_ID
,ZONE_CODE
,LOCATOR_ID
,LPN_ID ---- Added for # 6633612
,CREATION_DATE
,CREATED_BY
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,UPDATE_REASON_ID
,INITIAL_STATUS_FLAG
,FROM_MOBILE_APPS_FLAG
-- NSRIVAST, INVCONV , Start
,GRADE_CODE
,PRIMARY_ONHAND
,SECONDARY_ONHAND
-- NSRIVAST, INVCONV , End
,GROUP_ID --Bug#11826279
,pending_status --ERES Deferred
)
VALUES (
-- BEGIN SCHANDRU INVERES
--MTL_MATERIAL_STATUS_HISTORY_S.nextval
l_status_update_id, -- Add this local variable so that it can be used to be stored in the temp table.
-- END SCHANDRU INVERES
l_status_rec.ORGANIZATION_ID
,l_status_rec.INVENTORY_ITEM_ID
,l_status_rec.LOT_NUMBER
,l_status_rec.SERIAL_NUMBER
,l_status_rec.UPDATE_METHOD
,l_status_rec.STATUS_ID
,l_status_rec.ZONE_CODE
,l_status_rec.LOCATOR_ID
,l_status_rec.LPN_ID ---- Added for # 6633612
,l_status_rec.CREATION_DATE
,l_status_rec.CREATED_BY
,l_status_rec.LAST_UPDATED_BY
,l_status_rec.LAST_UPDATE_DATE
,l_status_rec.LAST_UPDATE_LOGIN
,l_status_rec.PROGRAM_APPLICATION_ID
,l_status_rec.PROGRAM_ID
,l_status_rec.ATTRIBUTE_CATEGORY
,l_status_rec.ATTRIBUTE1
,l_status_rec.ATTRIBUTE2
,l_status_rec.ATTRIBUTE3
,l_status_rec.ATTRIBUTE4
,l_status_rec.ATTRIBUTE5
,l_status_rec.ATTRIBUTE6
,l_status_rec.ATTRIBUTE7
,l_status_rec.ATTRIBUTE8
,l_status_rec.ATTRIBUTE9
,l_status_rec.ATTRIBUTE10
,l_status_rec.ATTRIBUTE11
,l_status_rec.ATTRIBUTE12
,l_status_rec.ATTRIBUTE13
,l_status_rec.ATTRIBUTE14
,l_status_rec.ATTRIBUTE15
,l_status_rec.UPDATE_REASON_ID
,l_status_rec.INITIAL_STATUS_FLAG
,l_status_rec.FROM_MOBILE_APPS_FLAG
-- NSRIVAST, INVCONV , Start
,l_status_rec.GRADE_CODE
,l_status_rec.PRIMARY_ONHAND
,l_status_rec.SECONDARY_ONHAND
-- NSRIVAST, INVCONV , End
,l_status_rec.GROUP_ID --Bug#11826279
,l_status_rec.pending_status --ERES Deferred
);
Insert into MTL_GRADE_STATUS_ERES_GTMP(status_update_id,
grade_update_id) values (l_status_update_id, NULL);
Insert into MTL_GRADE_STATUS_ERES_GTMP(status_update_id,
grade_update_id) values (l_status_rec.group_id, NULL);
Select MTL_MATERIAL_STATUS_HISTORY_S.nextval
Into l_status_update_id
From dual;
INSERT INTO MTL_MATERIAL_STATUS_HISTORY
(
STATUS_UPDATE_ID
,ORGANIZATION_ID
,INVENTORY_ITEM_ID
,LOT_NUMBER
,SERIAL_NUMBER
,UPDATE_METHOD
,STATUS_ID
,ZONE_CODE
,LOCATOR_ID
,LPN_ID ---- Added for # 6633612
,CREATION_DATE
,CREATED_BY
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,UPDATE_REASON_ID
,INITIAL_STATUS_FLAG
,FROM_MOBILE_APPS_FLAG
-- NSRIVAST, INVCONV , Start
,GRADE_CODE
,PRIMARY_ONHAND
,SECONDARY_ONHAND
-- NSRIVAST, INVCONV , End
)
VALUES (
--BEGIN SCHANDRU INVERES
--MTL_MATERIAL_STATUS_HISTORY_S.nextval
l_status_update_id, -- Add this local variable so that it can be used to be stored in the temp table
-- END SCHANDRU INVERES
l_status_rec.ORGANIZATION_ID
,l_status_rec.INVENTORY_ITEM_ID
,l_status_rec.LOT_NUMBER
,cc.serial_number
,l_status_rec.UPDATE_METHOD
,l_status_rec.STATUS_ID
,l_status_rec.ZONE_CODE
,l_status_rec.LOCATOR_ID
,l_status_rec.LPN_ID -- Added for # 6633612
,l_status_rec.CREATION_DATE
,l_status_rec.CREATED_BY
,l_status_rec.LAST_UPDATED_BY
,l_status_rec.LAST_UPDATE_DATE
,l_status_rec.LAST_UPDATE_LOGIN
,l_status_rec.PROGRAM_APPLICATION_ID
,l_status_rec.PROGRAM_ID
,l_status_rec.ATTRIBUTE_CATEGORY
,l_status_rec.ATTRIBUTE1
,l_status_rec.ATTRIBUTE2
,l_status_rec.ATTRIBUTE3
,l_status_rec.ATTRIBUTE4
,l_status_rec.ATTRIBUTE5
,l_status_rec.ATTRIBUTE6
,l_status_rec.ATTRIBUTE7
,l_status_rec.ATTRIBUTE8
,l_status_rec.ATTRIBUTE9
,l_status_rec.ATTRIBUTE10
,l_status_rec.ATTRIBUTE11
,l_status_rec.ATTRIBUTE12
,l_status_rec.ATTRIBUTE13
,l_status_rec.ATTRIBUTE14
,l_status_rec.ATTRIBUTE15
,l_status_rec.UPDATE_REASON_ID
,l_status_rec.INITIAL_STATUS_FLAG
,l_status_rec.FROM_MOBILE_APPS_FLAG
-- NSRIVAST, INVCONV , Start
,l_status_rec.GRADE_CODE
,l_status_rec.PRIMARY_ONHAND
,l_status_rec.SECONDARY_ONHAND
-- NSRIVAST, INVCONV , End
);
Insert into MTL_GRADE_STATUS_ERES_GTMP(status_update_id,
grade_update_id) values (l_status_update_id, NULL);
, 'Insert_Status_history'
);
END Insert_status_history;
SELECT 1 FROM mtl_material_statuses mts1,
mtl_material_statuses mts2
WHERE cp_old_status_id <> cp_new_status_id
AND mts1.status_id = cp_old_status_id
AND mts1.reservable_type = 1
AND mts2.status_id = cp_new_status_id
AND mts2.reservable_type <> mts1.reservable_type;
SELECT 1 FROM mtl_onhand_quantities_detail moq
WHERE organization_id = cp_organization_id
AND subinventory_code = cp_subinventory_code
AND EXISTS
(SELECT 1
FROM mtl_reservations mr
WHERE mr.inventory_item_id = moq.inventory_item_id
AND mr.organization_id = moq.organization_id
/* Bug 8674685
AND ( (mr.inventory_item_id = cp_inventory_item_id )
OR cp_inventory_item_id IS NULL
)
*/
AND ( (mr.subinventory_code = cp_subinventory_code )
OR mr.subinventory_code IS NULL
)
)
AND ROWNUM = 1;
SELECT 1 FROM mtl_onhand_quantities_detail moq
WHERE organization_id = cp_organization_id
AND locator_id = cp_locator_id
AND EXISTS
(SELECT 1
FROM mtl_reservations mr
WHERE mr.inventory_item_id = moq.inventory_item_id
AND mr.organization_id = moq.organization_id
/* Bug 8674685
AND ( (mr.inventory_item_id = cp_inventory_item_id )
OR cp_inventory_item_id IS NULL
)
*/
AND ( (mr.locator_id = cp_locator_id )
OR ( (mr.locator_id IS NULL
AND mr.subinventory_code = moq.subinventory_code
)
OR mr.subinventory_code IS NULL
)
)
)
AND ROWNUM = 1;
SELECT 1 FROM mtl_onhand_quantities_detail moq
WHERE organization_id = cp_organization_id
AND (inventory_item_id = cp_inventory_item_id OR cp_inventory_item_id IS NULL)
AND lot_number = cp_lot_number
AND EXISTS
(SELECT 1
FROM mtl_reservations mr
WHERE mr.inventory_item_id = moq.inventory_item_id
AND mr.organization_id = moq.organization_id
AND ( (mr.inventory_item_id = cp_inventory_item_id )
OR cp_inventory_item_id IS NULL
)
AND ( (mr.lot_number = cp_lot_number )
OR mr.lot_number IS NULL
)
)
AND ROWNUM = 1;
SELECT 1 FROM mtl_reservations
WHERE inventory_item_id = cp_inventory_item_id
AND organization_id = cp_organization_id
AND ROWNUM = 1;
SELECT 1 FROM mtl_onhand_quantities_detail moq
WHERE organization_id = cp_organization_id
AND (inventory_item_id = cp_inventory_item_id OR cp_inventory_item_id IS NULL)
AND (subinventory_code = cp_subinventory_code OR cp_subinventory_code IS NULL)
AND (locator_id = cp_locator_id OR cp_locator_id IS NULL)
AND (lot_number = cp_lot_number or cp_lot_number is null)
AND (lpn_id = cp_lpn_id OR cp_lpn_id IS NULL) -- Bug 14240066
AND EXISTS
(SELECT 1
FROM mtl_reservations mr
WHERE mr.inventory_item_id = moq.inventory_item_id
AND mr.organization_id = moq.organization_id
AND ( (mr.inventory_item_id = cp_inventory_item_id )
OR cp_inventory_item_id IS NULL
)
AND ( ( ( mr.subinventory_code = cp_subinventory_code ) OR
mr.subinventory_code IS NULL ) OR cp_subinventory_code IS NULL
)
AND ( ( ( mr.locator_id = cp_locator_id ) OR
mr.locator_id IS NULL ) OR cp_locator_id IS NULL
)
AND ( ( ( mr.lot_number = cp_lot_number ) OR
mr.lot_number IS NULL ) OR cp_lot_number IS NULL
)
AND ( ( ( mr.lpn_id = cp_lpn_id ) OR -- Bug 14240066
mr.lpn_id IS NULL ) OR cp_lpn_id IS NULL
)
)
AND ROWNUM = 1;
SELECT default_status_id INTO l_default_status_id
FROM mtl_parameters
WHERE organization_id = p_organization_id;
CURSOR cur_ms IS SELECT inventory_atp_code
,reservable_type
,availability_type FROM mtl_material_statuses
WHERE status_id = p_status_id;
UPDATE mtl_lot_numbers SET inventory_atp_code =rec_ms.inventory_atp_code,
availability_type =rec_ms.reservable_type,
reservable_type =rec_ms.availability_type
WHERE organization_id = p_org_id
AND lot_number = p_lot_number
AND inventory_item_id = p_inventory_item_id;
mtl_serial_numbers. Hence commenting the UPDATE statement */
/*
UPDATE mtl_serial_numbers SET inventory_atp_code =rec_ms.inventory_atp_code,
availability_type =rec_ms.reservable_type,
reservable_type =rec_ms.availability_type
WHERE current_organization_id = p_org_id
AND serial_number = p_serial_number
AND inventory_item_id = p_inventory_item_id;
UPDATE MTL_ITEM_LOCATIONS SET inventory_atp_code =rec_ms.inventory_atp_code,
availability_type =rec_ms.reservable_type,
reservable_type =rec_ms.availability_type
WHERE organization_id = p_org_id
AND inventory_location_id = p_inventory_location_id;
UPDATE mtl_secondary_inventories SET inventory_atp_code =rec_ms.inventory_atp_code,
availability_type =rec_ms.reservable_type,
reservable_type =rec_ms.availability_type
WHERE organization_id = p_org_id
AND secondary_inventory_name =p_secondary_inventory_name;