Increase onhand quantity interface using miscellaneous receipt

On Hand Quantity Interface in Oracle apps-R12

Interface tables:

MTL_TRANSACTIONS_INTERFACE

Concurrent Program:

Launch the Transaction Manager through Interface Manager or explicitly call the API – INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS () to launch a dedicated transaction worker to process them.

The Transaction Manager picks up the rows to process based on the LOCK_FLAG, TRANSACTION_MODE, and PROCESS_FLAG. Only records with TRANSACTION_MODE of 3, LOCK_FLAG of ‘2’, and PROCESS_FLAG of ‘1’ will be picked up by the Transaction Manager and assigned to a Transaction Worker. If a record fails to process completely, then PROCESS_FLAG will be set to ‘3’ and ERROR_CODE and ERROR_EXPLANATION will be populated with the cause for the error.

Base Tables:

mtl_onhand_quantities

Validations:

Validate organization_id

Check if item is assigned to organization

Validate organization_id, organization_code.

Validate inventory item id.

Transaction period must be open.

Some important columns that need to be populated in the interface tables:

MTL_TRANSACTIONS_INTERFACE:

TRANSACTION_SOURCE_NAME (ANY USER DEFINED VALUE),

TRANSACTION_HEADER_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL)

TRANSACTION_INTERFACE_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL ---If item is lot or serial controlled, use this field to link to mtl_transactions_interface otherwise leave it as NULL),

TRANSACTION_DATE,

TRANSACTION_TYPE_ID,

PROCESS_FLAG (1 = Yet to be processed, 2 = Processed, 3= Error)

TRANSACTION_MODE (2 = Concurrent – to launch a dedicated transaction worker

to explicitly process a set of transactions.

3 = Background – will be picked up by transaction manager

polling process and assigned to transaction

worker. These will not be picked up until the

transaction manager is running)

SOURCE_CODE,

SOURCE_HEADER_ID,

SOURCE_LINE_ID (Details about the source like Order Entry etc for tracking purposes)

TRANSACTION_SOURCE_ID

Source Type	Foreign Key Reference
Account	GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
Account Alias	MTL_GENERIC_DISPOSITIONS.DISPOSITION_ID
Job or schedule	WIP_ENTITIES.WIP_ENTITY_ID
Sales Order	MTL_SALES_ORDERS.SALES_ORDER_ID

ITEM_SEGMENT1 TO 20,

TRANSACTION_QTY,

TRANSACTION_UOM,

SUBINVENTORY_CODE,

ORGANIZATION_ID,

---

Note:

our stage table should have all the required mandatory columns and also should have who columns,and error_code,error_message to know whether that record is valid or not

.so that we can update our stage table if it is valid it is 'V',if it is invalid,it is 'E'.

stage table columns:

  ORGANIZATION_CODE   

 ITEM_NUMBER         

 SUBINVENTORY        

 TRANSACTION_DATE    

 TRANSACTION_TYPE    

 UOM_CODE            

 PRIMARY_QUANTITY    

 TRANSACTION_QUANTITY

 ERROR_CODE          

 ERROR_MESSAGE       

 PROCESS_FLAG        

Staging table :-

CREATE TABLE APPS.XX_INV_ONHAND_QTY_STG

(

 ORGANIZATION_CODE     VARCHAR2(100 BYTE),

 ITEM_NUMBER           VARCHAR2(100 BYTE),

 SUBINVENTORY          VARCHAR2(100 BYTE),

 TRANSACTION_DATE      DATE,

 TRANSACTION_TYPE      VARCHAR2(100 BYTE),

 UOM_CODE              VARCHAR2(3 BYTE),

 PRIMARY_QUANTITY      NUMBER,

 TRANSACTION_QUANTITY  NUMBER,

 ERROR_CODE            VARCHAR2(100 BYTE),

 ERROR_MESSAGE         VARCHAR2(100 BYTE),

 PROCESS_FLAG          VARCHAR2(1 BYTE)

)

Procedure :-

CREATE OR REPLACE PROCEDURE APPS.XX_INV_PROC_FOR_VALIDATION

IS

X_ORGANIZATION_ID                   NUMBER;

X_INV_ITEM_ID                       NUMBER;

X_PRIMARY_UOM_CODE                  VARCHAR2(3);

X_TXN                               VARCHAR2(1);

X_ERR_MSG                           VARCHAR2(100);

X_SUBINVENTORY                        VARCHAR2(100);

X_TRANSACTION_TYPE_ID                NUMBER;

X_TXN_SOURCE_TYPE_ID                NUMBER;

X_COUNT                                NUMBER;

X_err_code                            VARCHAR2(100);

X_PROCESS_FLAG                        VARCHAR2(1);

x_TRANSACTION_INTERFACE_ID            NUMBER;

X_CODE_COMBINATION_ID                 NUMBER;

CURSOR C1 IS SELECT ORGANIZATION_CODE,

      ITEM_NUMBER,

      SUBINVENTORY,

      TRANSACTION_DATE,

      TRANSACTION_TYPE,

      UOM_CODE,

      PRIMARY_QUANTITY,

      TRANSACTION_QUANTITY

      FROM

      XX_INV_ONHAND_QTY_STG;

      

CURSOR C2 IS SELECT ORGANIZATION_CODE,

      ITEM_NUMBER,

      SUBINVENTORY,

      TRANSACTION_DATE,

      TRANSACTION_TYPE,

      UOM_CODE,

      PRIMARY_QUANTITY,

      TRANSACTION_QUANTITY

      FROM

      XX_INV_ONHAND_QTY_STG

      WHERE PROCESS_FLAG = 'V';

BEGIN

  X_err_code       := NULL;

  X_COUNT          :=0;

 FOR ONHAND_LOOP IN C1

 LOOP

 X_COUNT        :=    X_COUNT+1;

 ---Validations:

       ---------------------------------------------------

     

                    --ORGANIZATION VALIDATION

       ----------------------------------------------------

       

     BEGIN

     SELECT  MP.ORGANIZATION_ID,'V'

       INTO    X_ORGANIZATION_ID,

       X_PROCESS_FLAG

       FROM    MTL_PARAMETERS  MP

       WHERE   MP.ORGANIZATION_CODE = ONHAND_LOOP.ORGANIZATION_CODE;

          DBMS_OUTPUT.PUT_LINE('ORGANIZATION IS VALID');

        EXCEPTION

            WHEN OTHERS THEN

                 -- X_err_code := SQLCODE;

            X_ERR_MSG := SUBSTR(SQLERRM, 1, 200);

            UPDATE XX_INV_ONHAND_QTY_STG SET ERROR_CODE='E',ERROR_MESSAGE = X_ERR_MSG;

           COMMIT;

                      DBMS_OUTPUT.PUT_LINE('ORGANIZATION IS NOT VALID');

     END;

     

     

     -----------------------------------------------------------

                   ---ITEM AND UOM VALIDATION

     -----------------------------------------------------------

     

     BEGIN

     SELECT  MSI.INVENTORY_ITEM_ID

            ,MSI.PRIMARY_UOM_CODE

            ,'V'

            ,MSI.MTL_TRANSACTIONS_ENABLED_FLAG

      INTO    X_INV_ITEM_ID

           ,X_PRIMARY_UOM_CODE

           ,X_PROCESS_FLAG

           ,X_TXN

       FROM    MTL_SYSTEM_ITEMS        MSI

       WHERE   MSI.ORGANIZATION_ID     =       X_ORGANIZATION_ID

               AND MSI.SEGMENT1            =   ONHAND_LOOP.ITEM_NUMBER;

                          DBMS_OUTPUT.PUT_LINE('ITEM IS VALID');

        EXCEPTION

        WHEN OTHERS THEN

       -- X_err_code := SQLCODE;

        X_ERR_MSG := SUBSTR(SQLERRM, 1, 200);

          UPDATE XX_INV_ONHAND_QTY_STG SET ERROR_CODE='E',ERROR_MESSAGE = X_ERR_MSG;

           COMMIT;

                                      DBMS_OUTPUT.PUT_LINE('ITEM IS NOT VALID');

   END;

   

        ------------------------------------------------------

                       ---SUB INVENTORY VALIDATION

        ------------------------------------------------------

        

        

        BEGIN

        SELECT MSI.SECONDARY_INVENTORY_NAME,'V'

               INTO   X_SUBINVENTORY,X_PROCESS_FLAG

               FROM   MTL_SECONDARY_INVENTORIES MSI

               WHERE  MSI.ORGANIZATION_ID = X_ORGANIZATION_ID--(GET FROM ORG_CODE)

                      AND    MSI.SECONDARY_INVENTORY_NAME  = ONHAND_LOOP.SUBINVENTORY;

                               DBMS_OUTPUT.PUT_LINE('SUBINVENTORY IS VALID');

         EXCEPTION

        WHEN OTHERS THEN

       -- X_err_code := SQLCODE;

        X_ERR_MSG := SUBSTR(SQLERRM, 1, 200);

          UPDATE XX_INV_ONHAND_QTY_STG SET ERROR_CODE='E',ERROR_MESSAGE = X_ERR_MSG;

           COMMIT;

                                      DBMS_OUTPUT.PUT_LINE('SUBINVENTORY IS NOT VALID');

   END;

   

          --------------------------------------------------------

                       ---TRANSACTION TYPE VALIDATION

          --------------------------------------------------------

          

   BEGIN

   SELECT  MTT.TRANSACTION_TYPE_ID

           ,MTT.TRANSACTION_SOURCE_TYPE_ID

           ,     'V'

       INTO    X_TRANSACTION_TYPE_ID

           ,       X_TXN_SOURCE_TYPE_ID

           ,       X_PROCESS_FLAG

       FROM    MTL_TRANSACTION_TYPES   MTT

       WHERE   UPPER ( MTT.TRANSACTION_TYPE_NAME ) = UPPER (ONHAND_LOOP.TRANSACTION_TYPE )

               AND  NVL ( MTT.DISABLE_DATE, SYSDATE +1 ) > SYSDATE ;

                       DBMS_OUTPUT.PUT_LINE('TRANSACTION TYPE IS VALID');

        EXCEPTION

        WHEN OTHERS THEN

        ---X_err_code := SQLCODE;

        X_ERR_MSG := SUBSTR(SQLERRM, 1, 200);

         UPDATE XX_INV_ONHAND_QTY_STG SET ERROR_CODE='E',ERROR_MESSAGE = X_ERR_MSG;

         COMMIT;

         DBMS_OUTPUT.PUT_LINE('TRANSACTION TYPE IS NOT VALID');

   

   END;

   

  

 END LOOP;

 

  BEGIN

   IF  X_PROCESS_FLAG='V' THEN

   X_err_code := NULL;

   X_ERR_MSG  := NULL;

   UPDATE XX_INV_ONHAND_QTY_STG

    SET PROCESS_FLAG = X_PROCESS_FLAG,ERROR_CODE = X_err_code,ERROR_MESSAGE =X_ERR_MSG ;

   COMMIT;

   DBMS_OUTPUT.PUT_LINE('NUMBER OF RECORDS VALIDATED : '||X_COUNT);

   ELSE

   DBMS_OUTPUT.PUT_LINE('NUMBER OF RECORDS VALIDATED : '||X_COUNT);

   END IF;

   END;

   

   

   

   -------------------------------------------------------------------------------------------

               ---FOR INSERTING RECORDS INTO INTERFACE TABLE

   --------------------------------------------------------------------------------------------

   

   

   BEGIN

   

   FND_GLOBAL.Apps_Initialize(FND_GLOBAL.USER_ID,

   FND_GLOBAL.RESP_ID,

   FND_GLOBAL.RESP_APPL_ID);

   

     IF  X_PROCESS_FLAG='V' THEN

 

   

X_ORGANIZATION_ID                   :=NULL;

X_INV_ITEM_ID                       :=NULL;

X_PRIMARY_UOM_CODE                  :=NULL;

X_TXN                               :=NULL;

X_ERR_MSG                           :=NULL;

X_SUBINVENTORY                      :=NULL;

X_TRANSACTION_TYPE_ID               :=NULL;

X_TXN_SOURCE_TYPE_ID                :=NULL;

X_COUNT                             :=NULL;

X_err_code                          :=NULL;

X_PROCESS_FLAG                      :=NULL;

    FOR X IN C2 LOOP

             X_COUNT        :=    X_COUNT+1;

           BEGIN

               SELECT  MP.ORGANIZATION_ID,'S'

       INTO    X_ORGANIZATION_ID,

       X_PROCESS_FLAG

       FROM    MTL_PARAMETERS  MP

       WHERE   MP.ORGANIZATION_CODE = X.ORGANIZATION_CODE;

       UPDATE  XX_INV_ONHAND_QTY_STG SET PROCESS_FLAG = X_PROCESS_FLAG;

                   COMMIT;

        EXCEPTION

            WHEN OTHERS THEN

       UPDATE  XX_INV_ONHAND_QTY_STG SET PROCESS_FLAG = 'U';

                       COMMIT;

           END;

           

           

           

           

           ---------------------------------------------

                               --INVENTORY--

           ---------------------------------------------

           

           BEGIN

     SELECT  MSI.INVENTORY_ITEM_ID

            ,MSI.PRIMARY_UOM_CODE

            ,'S'

            ,MSI.MTL_TRANSACTIONS_ENABLED_FLAG

      INTO    X_INV_ITEM_ID

           ,X_PRIMARY_UOM_CODE

           ,X_PROCESS_FLAG

           ,X_TXN

       FROM    MTL_SYSTEM_ITEMS        MSI

       WHERE   MSI.ORGANIZATION_ID     =       X_ORGANIZATION_ID

               AND MSI.SEGMENT1            =   X.ITEM_NUMBER;

       UPDATE  XX_INV_ONHAND_QTY_STG SET PROCESS_FLAG = X_PROCESS_FLAG;

           COMMIT;

        EXCEPTION

        WHEN OTHERS THEN

            UPDATE  XX_INV_ONHAND_QTY_STG SET PROCESS_FLAG = 'U';

           COMMIT;

   END;

   

   

   -----------------------------------------------------------------------

                       --TRANSACTION TYPE

   -----------------------------------------------------------------------

   

    BEGIN

   SELECT  MTT.TRANSACTION_TYPE_ID

           ,MTT.TRANSACTION_SOURCE_TYPE_ID

           ,     'S'

       INTO    X_TRANSACTION_TYPE_ID

           ,       X_TXN_SOURCE_TYPE_ID

           ,       X_PROCESS_FLAG

       FROM    MTL_TRANSACTION_TYPES   MTT

       WHERE   UPPER ( MTT.TRANSACTION_TYPE_NAME ) = UPPER (X.TRANSACTION_TYPE )

               AND  NVL ( MTT.DISABLE_DATE, SYSDATE +1 ) > SYSDATE ;

       UPDATE  XX_INV_ONHAND_QTY_STG SET PROCESS_FLAG = X_PROCESS_FLAG;

       COMMIT;

        EXCEPTION

        WHEN OTHERS THEN

            UPDATE  XX_INV_ONHAND_QTY_STG SET PROCESS_FLAG = 'U';

           COMMIT;

   

   END;

  

       BEGIN

        SELECT CODE_COMBINATION_ID

        INTO X_CODE_COMBINATION_ID

        FROM

        GL_CODE_COMBINATIONS_KFV     GCC

        WHERE CODE_COMBINATION_ID = 17021;

       

       

       END;

     

   

       SELECT mtl_material_transactions_s.nextval

       INTO X_TRANSACTION_INTERFACE_ID

       FROM DUAL;

       

   --------------------------------------------------------------

   

   ----------------------------------------------------------------

   

       BEGIN

     INSERT INTO MTL_TRANSACTIONS_INTERFACE

             (   SOURCE_HEADER_ID

             ,   SOURCE_LINE_ID

             ,   TRANSACTION_MODE

             ,   SOURCE_CODE

             ,   PROCESS_FLAG

             ,   TRANSACTION_INTERFACE_ID

             ,   TRANSACTION_DATE

             ,   INVENTORY_ITEM_ID

             ,   TRANSACTION_UOM

             ,   ORGANIZATION_ID

             ,   SUBINVENTORY_CODE

             ,   TRANSACTION_QUANTITY

             ,   TRANSACTION_TYPE_ID

             ,   TRANSACTION_SOURCE_TYPE_ID

             ,   DISTRIBUTION_ACCOUNT_ID

             ,   CREATED_BY

             ,   CREATION_DATE

             ,   LAST_UPDATED_BY

             ,   LAST_UPDATE_DATE

             ,   LAST_UPDATE_LOGIN

             )

     VALUES

             (   10

             ,   -10

             ,   3

             ,   'On HAND Qty Load'

             ,   1

             ,   X_TRANSACTION_INTERFACE_ID

             ,   x.TRANSACTION_DATE

             ,   X_INV_ITEM_ID

             ,   X_PRIMARY_UOM_CODE

             ,   X_ORGANIZATION_ID

             ,   x.SUBINVENTORY

             ,   x.TRANSACTION_QUANTITY

             ,   X_TRANSACTION_TYPE_ID

             ,   X_TXN_SOURCE_TYPE_ID

             ,   X_CODE_COMBINATION_ID

             ,   NVL(FND_GLOBAL.USER_ID,-1)

             ,   SYSDATE

             ,   NVL(FND_GLOBAL.USER_ID,-1)

             ,   SYSDATE

             ,   NVL(FND_GLOBAL.LOGIN_ID,-1)

             );

         UPDATE XX_INV_ONHAND_QTY_STG SET PROCESS_FLAG='P';

       DBMS_OUTPUT.PUT_LINE('NUMBER OF RECORDS INSERTED : '||X_COUNT);

       COMMIT;

          EXCEPTION

        WHEN OTHERS THEN

          DBMS_OUTPUT.PUT_LINE('RECORDS Insertion into MTL_TRANSACTION_INTERFACE failed');

         DELETE

          FROM    MTL_TRANSACTIONS_INTERFACE

          WHERE   TRANSACTION_INTERFACE_ID  = X_TRANSACTION_INTERFACE_ID;

        UPDATE XX_INV_ONHAND_QTY_STG SET PROCESS_FLAG='U';

        COMMIT;

        END;

  

   

    

      END LOOP;

    END IF;

   END;

END;

/

EXECUTE XX_INV_PROC_FOR_VALIDATION

---

---BELOW TO INSERT DATA FROM INTERFACE TO BASE TABLES---

---------------------------------------------------    

Go to front-end,

Run Concurrent program : -

inventory responsibility->SETUP->transactions->Interface Manager,now screen opens,in that u will have tools,press tools and click launch manager,now it will start hitting base tables

Note:

If error is occurring go to Transactions ->  Transaction Open Interface -> Error -> See error

After correcting the error apply the below code

/* update mtl_transactions_interface

set process_flag = 1,

lock_flag = 2,

transaction_mode = 3,

validation_required = null,

error_code = null,

error_explanation = null

where process_flag in (1,3); */    

and run procedure,and once again launch manager

once it completed,please check corresponding base tables i.e

select *from mtl_onhand_quantities where trunc(creation_date)=trunc(sysdate);