[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select to_char(sysdate,'MM/DD/YYYY HH:MM:SS') INTO l_ts from dual;
select mms.status_code
into x_status_code
from mtl_material_statuses mms ,
MTL_SECONDARY_INVENTORIES msi
where mms.status_id = msi.status_id
and msi.SECONDARY_INVENTORY_NAME = p_sub_inv
and msi.organization_id = p_org_id;
select mms.status_code
into x_status_code
from mtl_material_statuses mms ,
MTL_ITEM_LOCATIONS_KFV mil
where mms.status_id = mil.status_id
and mil.INVENTORY_LOCATION_ID = p_locator_id
and mil.organization_id = p_org_id;
select mms.status_code
into x_status_code
from mtl_material_statuses mms ,
MTL_SERIAL_NUMBERS msn
where mms.status_id = msn.status_id
and msn.SERIAL_NUMBER = p_serial
and msn.current_organization_id = p_org_id
and msn.inventory_item_id = p_item_id;
select mms.status_code
into x_status_code
from mtl_material_statuses mms ,
MTL_LOT_NUMBERS mln
where mms.status_id = mln.status_id
and mln.LOT_NUMBER = p_lot
and mln.organization_id = p_org_id
and mln.inventory_item_id = p_item_id;
SELECT SERIAL_NUMBER_CONTROL_CODE
INTO l_item_serial_check
FROM mtl_system_items
WHERE organization_id = p_org_id
AND inventory_item_id = p_item_id;
select mms.status_code
into x_status_code
from mtl_material_statuses mms ,
mtl_onhand_quantities_detail moqd
where mms.status_id = moqd.status_id
and moqd.organization_id = p_org_id
and moqd.inventory_item_id = p_item_id
AND moqd.subinventory_code = p_sub_inv
AND Decode(p_locator_id,NULL,-999,moqd.locator_id) = Nvl(p_locator_id,-999)
AND Decode(p_lot,NULL,'@@@',moqd.lot_number) = Nvl(p_lot,'@@@')
AND Nvl(moqd.lpn_id,-999) = Decode(p_lpn_id,NULL,-999,p_lpn_id) --Bug12621577
AND rownum < 2;
SELECT status_id
FROM MTL_LOT_NUMBERS
WHERE organization_id = p_org_id
AND inventory_item_id = p_item_id
AND lot_number BETWEEN p_from_lot AND p_to_lot;
SELECT status_code
INTO x_status_code
FROM MTL_MATERIAL_STATUSES_VL
WHERE status_id = lot_status_id;
SELECT status_id
FROM MTL_SERIAL_NUMBERS
WHERE current_organization_id = p_org_id
AND inventory_item_id = p_item_id
--AND current_status in (1, 3, 5)
AND current_status in (1, 3, 5, 7)
AND serial_number BETWEEN p_from_serial AND p_to_serial;
SELECT status_code
INTO x_status_code
FROM MTL_MATERIAL_STATUSES_VL
WHERE status_id = serial_status_id;
select moqd.rowid FROM
mtl_onhand_quantities_detail moqd,
mtl_material_status_history mmsh
where moqd.inventory_item_id = mmsh.inventory_item_id
and moqd.organization_id = mmsh.organization_id
and moqd.subinventory_code = mmsh.zone_code
and nvl(moqd.locator_id, -9999) = nvl(mmsh.locator_id, Nvl(moqd.locator_id, -9999))
and nvl(moqd.lpn_id, -9999) = nvl(mmsh.lpn_id , -9999)
and nvl(moqd.lot_number, '@@@@') BETWEEN nvl (mmsh.lot_number, Nvl(moqd.lot_number, '@@@@'))
and nvl (mmsh.lot_number, Nvl(moqd.lot_number, '@@@@'))
and exists
(select 1 from mtl_system_items_b msi
where moqd.inventory_item_id = msi.inventory_item_id
AND moqd.organization_id = msi.organization_id
AND msi.serial_number_control_code in (1,6)
)
AND mmsh.group_id = p_transaction_id
FOR UPDATE NOWAIT;
select msn.rowid FROM mtl_serial_numbers msn,
mtl_material_status_history mmsh
where msn.inventory_item_id = mmsh.inventory_item_id
and msn.current_organization_id = mmsh.organization_id
and msn.current_subinventory_code = mmsh.zone_code
and nvl(msn.current_locator_id, -9999) = nvl(mmsh.locator_id, Nvl(msn.current_locator_id, -9999))
and nvl(msn.lpn_id, -9999) = nvl(mmsh.lpn_id, -9999)
and nvl(msn.lot_number, '@@@@') BETWEEN nvl (mmsh.lot_number, Nvl(msn.lot_number, '@@@@'))
and nvl (mmsh.lot_number, Nvl(msn.lot_number, '@@@@'))
and current_status = 3
and exists
(select 1 from mtl_system_items_b msi
where msn.inventory_item_id = msi.inventory_item_id
AND msn.current_organization_id = msi.organization_id
AND nvl(msi.serial_status_enabled, 'N') = 'Y'
)
AND mmsh.group_id = p_transaction_id
FOR UPDATE NOWAIT;
select ms.rowid FROM mtl_secondary_inventories ms,
mtl_material_status_history mmsh
where ms.organization_id = mmsh.organization_id
and ms.secondary_inventory_name = mmsh.zone_code
AND mmsh.status_update_id = p_transaction_id
FOR UPDATE NOWAIT;
select mil.rowid FROM mtl_item_locations mil,
mtl_material_status_history mmsh
where mil.organization_id = mmsh.organization_id
and mil.inventory_location_id = mmsh.locator_id
AND mmsh.status_update_id = p_transaction_id
FOR UPDATE NOWAIT;
select mln.rowid FROM mtl_lot_numbers mln,
mtl_material_status_history mmsh
where mln.organization_id = mmsh.organization_id
and mln.inventory_item_id = mmsh.inventory_item_id
AND mln.lot_number = mmsh.lot_number
AND mmsh.status_update_id = p_transaction_id
FOR UPDATE NOWAIT;
select msn.rowid FROM mtl_serial_numbers msn,
mtl_material_status_history mmsh
where msn.current_organization_id = mmsh.organization_id
and msn.inventory_item_id = mmsh.inventory_item_id
AND msn.serial_number = mmsh.serial_number
AND mmsh.status_update_id = p_transaction_id
FOR UPDATE NOWAIT;
UPDATE mtl_material_status_history
SET pending_status = 0
WHERE pending_status = 1
AND group_id = p_transaction_id;
update mtl_onhand_quantities_detail
set status_id = p_status_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
where ROWID = rowid_list(j);
mdebug(' update executed in sql: Onhand');
SELECT serial_control into l_serial_status_control
from mtl_material_statuses
WHERE status_id = p_status_id;
update mtl_serial_numbers
set status_id = p_status_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
where ROWID = rowid_list1(j);
UPDATE mtl_material_status_history
SET pending_status = 0
WHERE pending_status = 1
AND status_update_id = p_transaction_id;
update mtl_secondary_inventories
set status_id = p_status_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
where ROWID = rowid_list(j);
mdebug(' update executed in sql: Onhand');
UPDATE mtl_material_status_history
SET pending_status = 0
WHERE pending_status = 1
AND status_update_id = p_transaction_id;
update mtl_item_locations
set status_id = p_status_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
where ROWID = rowid_list(j);
mdebug(' update executed in sql: Onhand');
UPDATE mtl_material_status_history
SET pending_status = 0
WHERE pending_status = 1
AND status_update_id = p_transaction_id;
update mtl_lot_numbers
set status_id = p_status_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
where ROWID = rowid_list(j);
mdebug(' update executed in sql: Onhand');
UPDATE mtl_material_status_history
SET pending_status = 0
WHERE pending_status = 1
AND status_update_id = p_transaction_id;
update mtl_serial_numbers
set status_id = p_status_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
where ROWID = rowid_list(j);
mdebug(' update executed in sql: Onhand');
DELETE FROM mtl_material_status_history
WHERE pending_status = 1
AND (group_id = p_transaction_id OR status_update_id = p_transaction_id);
PROCEDURE update_status(
p_update_method IN NUMBER
, p_organization_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_sub_code IN VARCHAR2
, p_sub_status_id IN NUMBER
, p_sub_reason_id IN NUMBER
, p_locator_id IN NUMBER
, p_loc_status_id IN NUMBER
, p_loc_reason_id IN NUMBER
, p_from_lot_number IN VARCHAR2
, p_to_lot_number IN VARCHAR2
, p_lot_status_id IN NUMBER
, p_lot_reason_id IN NUMBER
, p_from_SN IN VARCHAR2
, p_to_SN IN VARCHAR2
, p_serial_status_id IN NUMBER
, p_serial_reason_id IN NUMBER
, x_Status OUT nocopy VARCHAR2
, x_Message OUT nocopy VARCHAR2
, p_update_from_mobile IN VARCHAR2 DEFAULT 'Y'
-- NSRIVAST, INVCONV , Start
, p_grade_code IN VARCHAR2 DEFAULT NULL
, p_primary_onhand IN NUMBER DEFAULT NULL
, p_secondary_onhand IN NUMBER DEFAULT NULL
, p_onhand_status_id IN NUMBER DEFAULT NULL -- Added for # 6633612
, p_onhand_reason_id IN NUMBER DEFAULT NULL -- Added for # 6633612
, p_lpn_id IN NUMBER DEFAULT NULL -- Added for # 6633612
-- NSRIVAST, INVCONV , End
, p_lpn_indicator IN NUMBER DEFAULT 1 --bug12621577
, p_group_id IN NUMBER DEFAULT -999 --ERES Deferred
, p_pending_status IN NUMBER DEFAULT 0 --ERES Deferred
)
IS
l_status_rec INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type;
SELECT lot_number
FROM MTL_LOT_NUMBERS
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND lot_number between p_from_lot_number and p_to_lot_number;
select moqd.rowid FROM mtl_onhand_quantities_detail moqd
where inventory_item_id = Nvl(p_inventory_item_id, inventory_item_id)
and organization_id = p_organization_id
and subinventory_code = Nvl(p_sub_code, subinventory_code)
and nvl(locator_id, -9999) = nvl(p_locator_id, Nvl(locator_id, -9999))
and (((nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)) AND (p_lpn_indicator=1))
OR (nvl(lpn_id, -9999) = nvl(p_lpn_id, Nvl(lpn_id, -9999)) AND (p_lpn_indicator=0))) --bug12621577 modifying changes done in Bug 7012984
and nvl(lot_number, '@@@@') BETWEEN nvl (p_from_lot_number, Nvl(lot_number, '@@@@'))
and nvl (p_to_lot_number, Nvl(lot_number, '@@@@'))
and exists
(select 1 from mtl_system_items_b msi
where moqd.inventory_item_id = msi.inventory_item_id
AND moqd.organization_id = msi.organization_id
AND msi.serial_number_control_code in (1,6)
)
FOR UPDATE NOWAIT;
select moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.locator_id,moqd.lot_number,moqd.lpn_id,
Sum(moqd.primary_transaction_quantity) primary_onhand
FROM mtl_onhand_quantities_detail moqd
where inventory_item_id = Nvl(p_inventory_item_id, inventory_item_id)
and organization_id = p_organization_id
and subinventory_code = Nvl(p_sub_code, subinventory_code)
and nvl(locator_id, -9999) = nvl(p_locator_id, Nvl(locator_id, -9999))
and (((nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)) AND (p_lpn_indicator=1)) --bug12621577
OR (nvl(lpn_id, -9999) = nvl(p_lpn_id, Nvl(lpn_id, -9999)) AND (p_lpn_indicator=0)))
and nvl(lot_number, '@@@@') BETWEEN nvl (p_from_lot_number, Nvl(lot_number, '@@@@'))
and nvl (p_to_lot_number, Nvl(lot_number, '@@@@'))
GROUP BY moqd.organization_id,moqd.inventory_item_id,moqd.subinventory_code,moqd.locator_id,moqd.lot_number,moqd.lpn_id;
select msn.rowid FROM mtl_serial_numbers msn
where inventory_item_id = Nvl(p_inventory_item_id, inventory_item_id)
and current_organization_id = p_organization_id
and current_subinventory_code = Nvl(p_sub_code, current_subinventory_code)
and nvl(current_locator_id, -9999) = nvl(p_locator_id, Nvl(current_locator_id, -9999))
and (((nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)) AND (p_lpn_indicator=1))
OR (nvl(lpn_id, -9999) = nvl(p_lpn_id, Nvl(lpn_id, -9999)) AND (p_lpn_indicator=0))) --bug12621577 modifying changes done in Bug 7012984
and nvl(lot_number, '@@@@') BETWEEN nvl (p_from_lot_number, Nvl(lot_number, '@@@@'))
and nvl (p_to_lot_number, Nvl(lot_number, '@@@@'))
and current_status = 3
and exists
(select 1 from mtl_system_items_b msi
where msn.inventory_item_id = msi.inventory_item_id
AND msn.current_organization_id = msi.organization_id
AND nvl(msi.serial_status_enabled, 'N') = 'Y'
)
FOR UPDATE NOWAIT;
SELECT *
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id IN
(SELECT lpn_id
FROM wms_license_plate_numbers plpn
start with lpn_id = p_lpn_id
connect by parent_lpn_id = prior lpn_id
)
order by serial_summary_entry
FOR UPDATE NOWAIT;
IF p_update_from_mobile = 'Y' THEN
SAVEPOINT INV_UPDATE_STATUS;
mdebug('in update status');
SELECT Count(1) INTO l_pending_eres_chk
FROM mtl_material_status_history
WHERE inventory_item_id = NVL(p_inventory_item_id, inventory_item_id)
AND organization_id = p_organization_id
AND zone_code = NVL(p_sub_code,zone_code)
AND Decode(p_locator_id,NULL,-999,locator_id) = Nvl(p_locator_id,-999)
AND Decode(p_from_lot_number,NULL,'@@@',lot_number) = Nvl(p_from_lot_number,'@@@')
AND Decode(p_lpn_id,NULL,-999,lpn_id) = Nvl(p_lpn_id,-999)
AND pending_status = 1
AND ROWNUM = 1 ;
RAISE e_invalid_update;
EXCEPTION WHEN e_invalid_update THEN
mdebug(' IN e_invalid_update EXCEPTION ');
l_status_rec.update_method := INV_MATERIAL_STATUS_PUB.g_update_method_manual;
SELECT Count(1) INTO l_pending_eres_chk
FROM mtl_material_status_history
WHERE organization_id = p_organization_id
AND zone_code = p_sub_code
AND locator_id is null
AND inventory_item_id is null
AND lot_number is null AND serial_number is null
AND pending_status = 1
AND ROWNUM = 1 ;
mdebug(' IN e_invalid_update EXCEPTION ');
update mtl_secondary_inventories
set status_id = p_sub_status_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
where organization_id = p_organization_id
and secondary_inventory_name = p_sub_code;
l_status_rec.update_reason_id := p_sub_reason_id;
l_status_rec.from_mobile_apps_flag := p_update_from_mobile;
INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
SELECT Count(1) INTO l_pending_eres_chk
FROM mtl_material_status_history
WHERE organization_id = p_organization_id
AND locator_id = p_locator_id
AND inventory_item_id is null
AND lot_number is null AND serial_number is null
AND pending_status = 1
AND ROWNUM = 1 ;
mdebug(' IN e_invalid_update EXCEPTION ');
update mtl_item_locations
set status_id = p_loc_status_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
where organization_id = p_organization_id
and inventory_location_id = p_locator_id;
l_status_rec.update_reason_id := p_loc_reason_id;
l_status_rec.from_mobile_apps_flag := p_update_from_mobile;
INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
SELECT Count(1) INTO l_pending_eres_chk
FROM mtl_material_status_history
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND lot_number BETWEEN p_from_lot_number and p_to_lot_number
AND zone_code is null
AND locator_id is null
AND serial_number is null
AND pending_status = 1
AND ROWNUM = 1 ;
mdebug(' IN e_invalid_update EXCEPTION ');
update mtl_lot_numbers
set status_id = p_lot_status_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
where organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id
and lot_number BETWEEN p_from_lot_number and p_to_lot_number ;
l_status_rec.update_reason_id := p_lot_reason_id;
l_status_rec.from_mobile_apps_flag := p_update_from_mobile;
INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
SELECT Count(1) INTO l_pending_eres_chk
FROM mtl_material_status_history
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND serial_number BETWEEN p_from_SN AND p_to_SN
AND zone_code is null
AND locator_id is null
AND pending_status = 1
AND ROWNUM = 1 ;
mdebug(' IN e_invalid_update EXCEPTION ');
update mtl_serial_numbers
set status_id = p_serial_status_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
where current_organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id
and serial_number BETWEEN p_from_SN AND p_to_SN;
l_status_rec.update_reason_id := p_serial_reason_id;
l_status_rec.from_mobile_apps_flag := p_update_from_mobile;
INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
SELECT serial_control into l_serial_status_control
from mtl_material_statuses
WHERE status_id = p_onhand_status_id;
SELECT outermost_lpn_id into l_outermost_lpn_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id ;
mdebug('in mass update of lpn '||p_onhand_status_id);
FND_MESSAGE.SET_NAME('WMS','WMS_STATUS_UPDATE_FAILED');
UPDATE mtl_onhand_quantities_detail
SET status_id = p_onhand_status_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
WHERE inventory_item_id = l_wlc_cur.inventory_item_id
AND organization_id = p_organization_id
AND subinventory_code = Nvl(p_sub_code,'@@@@')
AND locator_id = Nvl(p_locator_id ,-9999)
AND Nvl(lot_number,'@@@@') = Nvl(l_wlc_cur.lot_number,'@@@@')
AND lpn_id = l_wlc_cur.parent_lpn_id;
UPDATE mtl_serial_numbers
set status_id = p_onhand_status_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
WHERE inventory_item_id = l_wlc_cur.inventory_item_id
AND current_organization_id = p_organization_id
AND current_subinventory_code = Nvl(p_sub_code,'@@@@')
AND current_locator_id = Nvl(p_locator_id ,-9999)
AND lpn_id = l_wlc_cur.parent_lpn_id;
SELECT outermost_lpn_id into l_outermost_lpn_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id ;
update mtl_serial_numbers
set status_id = p_onhand_status_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
where lpn_id = p_lpn_id
AND current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND serial_number BETWEEN p_from_SN AND p_to_SN
AND Nvl(lot_number,'@@@@') = Nvl(p_from_lot_number,'@@@@');
SELECT outermost_lpn_id into l_outermost_lpn_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
SELECT Count(1) INTO l_pending_eres_chk
FROM mtl_material_status_history
WHERE inventory_item_id = NVL(p_inventory_item_id,inventory_item_id)
AND organization_id = p_organization_id
AND zone_code = NVL(p_sub_code,zone_code)
AND nvl(locator_id, -9999) = nvl(p_locator_id, Nvl(locator_id, -9999))
AND nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)
and nvl(lot_number, '@@@@') BETWEEN nvl (p_from_lot_number, Nvl(lot_number, '@@@@'))
and nvl (p_to_lot_number, Nvl(lot_number, '@@@@'))
AND pending_status = 1
AND ROWNUM = 1 ;
mdebug(' IN e_invalid_update EXCEPTION ');
/*Bug 10359438. Moved the below code and added select query to retrieve old status id of the record. */
/* Bug 6917621 */
if (p_inventory_item_id is not null) then
l_status_rec.inventory_item_id := p_inventory_item_id;
l_status_rec.update_reason_id := p_onhand_reason_id;
l_status_rec.from_mobile_apps_flag := p_update_from_mobile;
FOR cc IN cur_lot_number LOOP -- To update all the lots in a given sub, locator combination..
l_status_rec.lot_number := cc.lot_number;
l_status_rec.from_mobile_apps_flag := p_update_from_mobile;
select status_id into l_old_status_id
from mtl_onhand_quantities_detail moqd
where inventory_item_id = Nvl(p_inventory_item_id, inventory_item_id)
and organization_id = p_organization_id
and subinventory_code = Nvl(p_sub_code, subinventory_code)
and nvl(locator_id, -9999) = nvl(p_locator_id, Nvl(locator_id, -9999))
and nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)
and nvl(lot_number, '@@@@') = l_status_rec.lot_number ;
/*Select mtl_onhand_status_group_s.nextval
Into l_status_group_id
From dual;
INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
/*Select mtl_onhand_status_group_s.nextval
Into l_status_group_id
From dual;
INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
update mtl_onhand_quantities_detail
set status_id = p_onhand_status_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
where ROWID = rowid_list(j);
-- Modified the where clause in the above update as it is not required for the bug # 6633612
--COMMIT; Bug#11826279, MTL_GRADE_STATUS_ERES_GTM getting deleted in the same session.
mdebug(' update executed in sql: Onhand');
SELECT serial_control into l_serial_status_control
from mtl_material_statuses
WHERE status_id = p_onhand_status_id;
update mtl_serial_numbers
set status_id = p_onhand_status_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
where ROWID = rowid_list1(j);
mdebug(' update executed in sql: Onhand Serial');
end if; --added for full lpn update case
p_update_method
, p_organization_id
, p_inventory_item_id
, p_sub_code
, p_sub_status_id
, p_sub_reason_id
, p_locator_id
, p_loc_status_id
, p_loc_reason_id
, p_from_lot_number
, p_to_lot_number
, p_lot_status_id
, p_lot_reason_id
, p_from_SN
, p_to_SN
, p_serial_status_id
, p_serial_reason_id
, p_onhand_status_id -- Added for # 6633612
, p_onhand_reason_id -- Added for # 6633612
, p_lpn_id -- Added for # 6633612
, x_Status
, x_Message
);
IF p_update_from_mobile = 'Y' THEN
ROLLBACK TO INV_UPDATE_STATUS;
--ROLLBACK TO INV_UPDATE_STATUS;
IF p_update_from_mobile = 'Y' THEN
ROLLBACK TO INV_UPDATE_STATUS;
FND_MESSAGE.SET_NAME('WMS','WMS_STATUS_UPDATE_FAILED');
END update_status;
p_update_method IN NUMBER
, p_organization_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_sub_code IN VARCHAR2
, p_sub_status_id IN NUMBER
, p_sub_reason_id IN NUMBER
, p_locator_id IN NUMBER
, p_loc_status_id IN NUMBER
, p_loc_reason_id IN NUMBER
, p_from_lot_number IN VARCHAR2
, p_to_lot_number IN VARCHAR2
, p_lot_status_id IN NUMBER
, p_lot_reason_id IN NUMBER
, p_from_SN IN VARCHAR2
, p_to_SN IN VARCHAR2
, p_serial_status_id IN NUMBER
, p_serial_reason_id IN NUMBER
, p_onhand_status_id IN NUMBER DEFAULT NULL -- Added for # 6633612
, p_onhand_reason_id IN NUMBER DEFAULT NULL -- Added for # 6633612
, p_lpn_id IN NUMBER DEFAULT NULL -- Added for # 6633612
, x_Status OUT nocopy VARCHAR2
, x_Message OUT nocopy VARCHAR2)
IS
l_workflow_name varchar2(250);
l_update_method varchar2(80);
l_calling_program_name := 'Update Status';
SELECT meaning
INTO l_update_method
FROM MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'MTL_STATUS_UPDATE_METHOD'
AND LOOKUP_CODE = p_update_method;
SELECT WORKFLOW_NAME
INTO l_workflow_name
FROM MTL_TRANSACTION_REASONS
WHERE REASON_ID = p_sub_reason_id;
SELECT REASON_NAME
INTO l_reason_name
FROM MTL_TRANSACTION_REASONS
WHERE REASON_ID = p_sub_reason_id;
SELECT status_code
INTO l_status_code
FROM MTL_MATERIAL_STATUSES_VL
WHERE status_id = p_sub_status_id;
,P_UPDATE_STATUS_METHOD => l_update_method
,X_RETURN_STATUS => lX_RETURN_STATUS
,X_MSG_DATA => lX_MSG_DATA
,X_MSG_COUNT => lX_MSG_COUNT
,X_ORGANIZATION_ID => lX_ORGANIZATION_ID
,X_SUBINVENTORY => lX_SUBINVENTORY
,X_SUBINVENTORY_STATUS => lX_SUBINVENTORY_STATUS
,X_LOCATOR => lX_LOCATOR
,X_LOCATOR_STATUS => lX_LOCATOR_STATUS
,X_LPN_ID => lX_LPN_ID
,X_LPN_STATUS => lX_LPN_STATUS
,X_INVENTORY_ITEM_ID => lX_INVENTORY_ITEM_ID
,X_REVISION => lX_REVISION
,X_LOT_NUMBER => lX_LOT_NUMBER
,X_LOT_STATUS => lX_LOT_STATUS
,X_QUANTITY => lX_QUANTITY
,X_UOM_CODE => lX_UOM_CODE
,X_PRIMARY_QUANTITY => lX_PRIMARY_QUANTITY
,X_TRANSACTION_QUANTITY => lX_TRANSACTION_QUANTITY
,X_RESERVATION_ID => lX_RESERVATION_ID
);
SELECT WORKFLOW_NAME
INTO l_workflow_name
FROM MTL_TRANSACTION_REASONS
WHERE REASON_ID = p_loc_reason_id;
SELECT REASON_NAME
INTO l_reason_name
FROM MTL_TRANSACTION_REASONS
WHERE REASON_ID = p_loc_reason_id;
SELECT status_code
INTO l_status_code
FROM MTL_MATERIAL_STATUSES_VL
WHERE status_id = p_loc_status_id;
,P_UPDATE_STATUS_METHOD => l_update_method
,X_RETURN_STATUS => lX_RETURN_STATUS
,X_MSG_DATA => lX_MSG_DATA
,X_MSG_COUNT => lX_MSG_COUNT
,X_ORGANIZATION_ID => lX_ORGANIZATION_ID
,X_SUBINVENTORY => lX_SUBINVENTORY
,X_SUBINVENTORY_STATUS => lX_SUBINVENTORY_STATUS
,X_LOCATOR => lX_LOCATOR
,X_LOCATOR_STATUS => lX_LOCATOR_STATUS
,X_LPN_ID => lX_LPN_ID
,X_LPN_STATUS => lX_LPN_STATUS
,X_INVENTORY_ITEM_ID => lX_INVENTORY_ITEM_ID
,X_REVISION => lX_REVISION
,X_LOT_NUMBER => lX_LOT_NUMBER
,X_LOT_STATUS => lX_LOT_STATUS
,X_QUANTITY => lX_QUANTITY
,X_UOM_CODE => lX_UOM_CODE
,X_PRIMARY_QUANTITY => lX_PRIMARY_QUANTITY
,X_TRANSACTION_QUANTITY => lX_TRANSACTION_QUANTITY
,X_RESERVATION_ID => lX_RESERVATION_ID
);
SELECT WORKFLOW_NAME
INTO l_workflow_name
FROM MTL_TRANSACTION_REASONS
WHERE REASON_ID = p_lot_reason_id;
SELECT REASON_NAME
INTO l_reason_name
FROM MTL_TRANSACTION_REASONS
WHERE REASON_ID = p_lot_reason_id;
SELECT status_code
INTO l_status_code
FROM MTL_MATERIAL_STATUSES_VL
WHERE status_id = p_lot_status_id;
,P_UPDATE_STATUS_METHOD => l_update_method
,X_RETURN_STATUS => lX_RETURN_STATUS
,X_MSG_DATA => lX_MSG_DATA
,X_MSG_COUNT => lX_MSG_COUNT
,X_ORGANIZATION_ID => lX_ORGANIZATION_ID
,X_SUBINVENTORY => lX_SUBINVENTORY
,X_SUBINVENTORY_STATUS => lX_SUBINVENTORY_STATUS
,X_LOCATOR => lX_LOCATOR
,X_LOCATOR_STATUS => lX_LOCATOR_STATUS
,X_LPN_ID => lX_LPN_ID
,X_LPN_STATUS => lX_LPN_STATUS
,X_INVENTORY_ITEM_ID => lX_INVENTORY_ITEM_ID
,X_REVISION => lX_REVISION
,X_LOT_NUMBER => lX_LOT_NUMBER
,X_LOT_STATUS => lX_LOT_STATUS
,X_QUANTITY => lX_QUANTITY
,X_UOM_CODE => lX_UOM_CODE
,X_PRIMARY_QUANTITY => lX_PRIMARY_QUANTITY
,X_TRANSACTION_QUANTITY => lX_TRANSACTION_QUANTITY
,X_RESERVATION_ID => lX_RESERVATION_ID
);
SELECT WORKFLOW_NAME
INTO l_workflow_name
FROM MTL_TRANSACTION_REASONS
WHERE REASON_ID = p_serial_reason_id;
SELECT REASON_NAME
INTO l_reason_name
FROM MTL_TRANSACTION_REASONS
WHERE REASON_ID = p_serial_reason_id;
SELECT status_code
INTO l_status_code
FROM MTL_MATERIAL_STATUSES_VL
WHERE status_id = p_serial_status_id;
,P_UPDATE_STATUS_METHOD => l_update_method
,X_RETURN_STATUS => lX_RETURN_STATUS
,X_MSG_DATA => lX_MSG_DATA
,X_MSG_COUNT => lX_MSG_COUNT
,X_ORGANIZATION_ID => lX_ORGANIZATION_ID
,X_SUBINVENTORY => lX_SUBINVENTORY
,X_SUBINVENTORY_STATUS => lX_SUBINVENTORY_STATUS
,X_LOCATOR => lX_LOCATOR
,X_LOCATOR_STATUS => lX_LOCATOR_STATUS
,X_LPN_ID => lX_LPN_ID
,X_LPN_STATUS => lX_LPN_STATUS
,X_INVENTORY_ITEM_ID => lX_INVENTORY_ITEM_ID
,X_REVISION => lX_REVISION
,X_LOT_NUMBER => lX_LOT_NUMBER
,X_LOT_STATUS => lX_LOT_STATUS
,X_QUANTITY => lX_QUANTITY
,X_UOM_CODE => lX_UOM_CODE
,X_PRIMARY_QUANTITY => lX_PRIMARY_QUANTITY
,X_TRANSACTION_QUANTITY => lX_TRANSACTION_QUANTITY
,X_RESERVATION_ID => lX_RESERVATION_ID
);
SELECT WORKFLOW_NAME
INTO l_workflow_name
FROM MTL_TRANSACTION_REASONS
WHERE REASON_ID = p_onhand_reason_id;
SELECT REASON_NAME
INTO l_reason_name
FROM MTL_TRANSACTION_REASONS
WHERE REASON_ID = p_onhand_reason_id;
SELECT status_code
INTO l_status_code
FROM MTL_MATERIAL_STATUSES_VL
WHERE status_id = p_onhand_status_id;
,P_UPDATE_STATUS_METHOD => l_update_method
,X_RETURN_STATUS => lX_RETURN_STATUS
,X_MSG_DATA => lX_MSG_DATA
,X_MSG_COUNT => lX_MSG_COUNT
,X_ORGANIZATION_ID => lX_ORGANIZATION_ID
,X_SUBINVENTORY => lX_SUBINVENTORY
,X_SUBINVENTORY_STATUS => lX_SUBINVENTORY_STATUS
,X_LOCATOR => lX_LOCATOR
,X_LOCATOR_STATUS => lX_LOCATOR_STATUS
,X_LPN_ID => lX_LPN_ID
,X_LPN_STATUS => lX_LPN_STATUS
,X_INVENTORY_ITEM_ID => lX_INVENTORY_ITEM_ID
,X_REVISION => lX_REVISION
,X_LOT_NUMBER => lX_LOT_NUMBER
,X_LOT_STATUS => lX_LOT_STATUS
,X_QUANTITY => lX_QUANTITY
,X_UOM_CODE => lX_UOM_CODE
,X_PRIMARY_QUANTITY => lX_PRIMARY_QUANTITY
,X_TRANSACTION_QUANTITY => lX_TRANSACTION_QUANTITY
,X_RESERVATION_ID => lX_RESERVATION_ID
);
SELECT tracking_quantity_ind INTO p_tracking_qty_ind
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id;
SELECT *
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id IN
(SELECT lpn_id
FROM wms_license_plate_numbers plpn
start with lpn_id = p_outermost_lpn_id
connect by parent_lpn_id = prior lpn_id
)
and wlc.parent_lpn_id not in
(SELECT lpn_id
FROM wms_license_plate_numbers plpn
start with lpn_id = p_lpn_id
connect by parent_lpn_id = prior lpn_id
);
SELECT *
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id IN
(SELECT lpn_id
FROM wms_license_plate_numbers plpn
start with lpn_id = p_outermost_lpn_id
connect by parent_lpn_id = prior lpn_id
);
select status_id
FROM mtl_serial_numbers
where inventory_item_id = l_inventory_item_id
AND lpn_id = l_lpn_id;
select DISTINCT status_id INTO l_default_status_id
from mtl_onhand_quantities_detail
WHERE lpn_id = l_wlc_cur.parent_lpn_id
AND inventory_item_id = l_wlc_cur.inventory_item_id
AND NVL(lot_number,'@@@@') = NVL(l_wlc_cur.lot_number,'@@@@')
AND organization_id = p_organization_id;
select DISTINCT status_id into l_default_status_id
from mtl_onhand_quantities_detail
where lpn_id = l_wlc_item_cur.parent_lpn_id
AND inventory_item_id = l_wlc_item_cur.inventory_item_id
AND NVL(lot_number,'@@@@') = NVL(l_wlc_item_cur.lot_number,'@@@@')
AND organization_id = p_organization_id;
SELECT *
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id IN
(SELECT lpn_id
FROM wms_license_plate_numbers plpn
start with lpn_id = p_outermost_lpn_id
connect by parent_lpn_id = prior lpn_id
);
select status_id
FROM mtl_serial_numbers msn
where inventory_item_id = l_inventory_item_id
AND lpn_id = l_lpn_id
AND nvl(msn.lot_number , '@@@@') = NVL(p_lot_number,'@@@@')
AND msn.serial_number NOT IN (select serial_number
from mtl_serial_numbers
where serial_number between p_fm_sn AND p_to_sn);
select DISTINCT status_id into l_default_status_id
from mtl_onhand_quantities_detail
where lpn_id = l_wlc_item_cur.parent_lpn_id
AND inventory_item_id = l_wlc_item_cur.inventory_item_id
AND NVL(lot_number,'@@@@') = NVL(l_wlc_item_cur.lot_number,'@@@@')
AND organization_id = p_organization_id;