Update Physical Count in Inventory

Update Physical Count in Inventory

Step 1- Login to Oracle http://erptest.local:8001 /with your respective credentials.



Enter the user name and password. Click on login.

Step 2- Go to the responsibility as WASAC Inventory User. Step 3 - Find the organization. 

Change Organization-MRP



Search organization GHP.



Click on ok.

Figure 1-Change Organization

Step 4 -Define Physical Inventory 

Navigation:  Counting->Physical Inventory->Physical Inventories

Figure 2-Physical Inventory



Click on new.

Figure 3-Snapshot

 

Click on snapshot. It will show concurrent request for inventory snapshot submitted.

Step 5 - Run physical inventory    

Then go to view. Click on request. find a request. Check the status of freeze physical inventory

Figure 4-Find Request

Step 6-Check the Physical inventory summary. 

Navigation:  Counting->Physical Inventory->Physical Inventories

Figure 5-Physical Inventory Summary



Click on open. It will show this screen.

Figure 6-Define Physical Inventory



Click on tags.

Figure 7-Generate Physical Inventory Tags 

Click on generate. Then it will show this output.

Step 7 - Check the request.

Figure 8-Concurrent Request

Step 8 -Check the physical count tag in physical inventory.

Figure 9- Physical Inventory Tags  

Click on no. Run the following program and it will automatically fill the entries in physical inventory tag counts(GHP). SET SERVEROUTPUT ON; DECLARE L_TAG_RECORD_TBL INV_PHY_INV_PUB.PI_TAG_RECORDS; L_TAG_RECORD INV_PHY_INV_PUB.PI_TAG_RECORD; L_RETURN_STATUS VARCHAR2(100); L_RETURN_MSG VARCHAR(2000); L_DEBUG NUMBER; L_ORG_ID NUMBER; L_USER_ID NUMBER := -1; L_RESP_ID NUMBER := -1; L_APPLICATION_ID NUMBER := -1; L_USER_NAME VARCHAR2(30) := 'HYBERIT'; -- user name l_resp_name VARCHAR2(30) := 'WASAC Inventory User'; -- responsibility name L_INVENTORY_ITEM_ID NUMBER; L_UOM VARCHAR2(20); L_TAG_ID NUMBER; L_TAG_NUMBER NUMBER; L_SUBINVENTORY_CODE VARCHAR2(20); l_Count number; l_physical_inventory_name varchar2(200); l_physical_inventory_id number; CURSOR ITEM_DETAIL IS SELECT * FROM XX_ITEM_dETAIL where organization_id=85; BEGIN

-- Get the USER_ID from FND_USER Table SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = l_user_name; -- Get the application_id and responsibility_id SELECT application_id, responsibility_id INTO l_application_id, l_resp_id FROM fnd_responsibility_vl WHERE responsibility_name = l_resp_name; -- intializing the link to the application FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_application_id); dbms_output.put_line('Initialized applications context: '|| l_user_id || ' '|| l_resp_id ||' '|| l_application_id ); INV_LOG_UTIL.TRACE ('Come into update_tags1 ','INV_PHY_INV_PUB',11);

FOR I IN ITEM_dETAIL LOOP l_count :=0; BEGIN SELECT INVENTORY_ITEM_ID, PRIMARY_UOM_CODE INTO L_INVENTORY_ITEM_ID,L_UOM FROM MTL_sYSTEM_ITEMS_B WHERE SEGMENT1=I.SEGMENT1 AND SEGMENT2=I.SEGMENT2 AND ORGANIZATION_ID=I.ORGANIZATION_ID; EXCEPTION when no_data_found then dbms_output.put_line('this item does not exit'); l_count :=l_count+1; end;  begin SELECT PHYSICAL_INVENTORY_ID, physical_inventory_name INTO l_PHYSICAL_INVENTORY_ID,l_physical_inventory_name FROM MTL_PHYSICAL_INVENTORIES WHERE ORGANIZATION_ID=I.ORGANIZATION_ID; EXCEPTION when no_data_found then dbms_output.put_line('this physical inventory does not exit'); l_count :=l_count+1; end;  begin SELECT TAG_ID, TAG_NUMBER, SUBINVENTORY INTO L_TAG_ID, L_TAG_NUMBER, L_SUBINVENTORY_CODE FROM MTL_PHYSICAL_INVENTORY_TAGS WHERE ORGANIZATION_ID=I.ORGANIZATION_ID AND INVENTORY_ITEM_ID=I.INVENTORY_ITEM_ID; EXCEPTION when no_data_found then dbms_output.put_line('this tag does not exit'); l_count :=l_count+1; end; if l_count =0 then

--L_TAG_RECORD.PII_INTERFACE_ID := 12345; -- this field id not used L_TAG_RECORD.TAG_ID := l_tag_id; l_tag_record.TAG_NUMBER := l_tag_number; L_TAG_RECORD.PHYSICAL_INVENTORY_ID := l_physical_inventory_id; L_TAG_RECORD.PHYSICAL_INVENTORY_NAME := l_physical_inventory_name; L_TAG_RECORD.ORGANIZATION_ID := i.organization_id; l_tag_record.subinventory := L_SUBINVENTORY_CODE; L_TAG_RECORD.INVENTORY_ITEM_ID := l_inventory_item_id; l_tag_record.tag_uom :=l_uom; L_TAG_RECORD.COUNT_QUANTITY := i.physical_count; --L_TAG_RECORD.APPROVAL_STATUS := ''; L_TAG_RECORD.VOID_FLAG := 2; l_tag_record.ACTION := 'U'; l_tag_record_tbl(1) := l_tag_record; INV_PHY_INV_PUB.UPDATE_TAGS(P_TAG_LINE_REC_TBL => L_TAG_RECORD_TBL, P_COMMIT =>FND_API.G_TRUE, X_RETURN_STATUS => L_RETURN_STATUS, X_MSG_DATA => L_RETURN_MSG, P_USERID => L_USER_ID , P_API_VERSION_NUMBER => 1.0 , P_INIT_MSG_LIST => FND_API.G_TRUE); COMMIT; DBMS_OUTPUT.PUT_LINE ( 'Status :' || L_RETURN_STATUS); DBMS_OUTPUT.put_line ('Mesage data' || L_RETURN_MSG); end if; end loop; END; 

Check the entries in physical inventory tag counts.

Figure 10 Physical Inventory Tag counts

Step 9 -Check  the approve adjustment.

Figure 11-Approve Adjustment 

Go to the Approve adjustments.