API to Assign Item to an Organization in Oracle Inventory


EGO_ITEM_PUB package provides functionality for maintaining items, item revisions, etc. We can use ASSIGN_ITEM_TO_ORG procedure to assign one item to an organization.

The procedure definition is:

  PROCEDURE Assign_Item_To_Org(
      p_api_version             IN      NUMBER
     ,p_init_msg_list           IN      VARCHAR2        DEFAULT  G_FALSE
     ,p_commit                  IN      VARCHAR2        DEFAULT  G_FALSE
     ,p_Inventory_Item_Id       IN      NUMBER          DEFAULT  G_MISS_NUM
     ,p_Item_Number             IN      VARCHAR2        DEFAULT  G_MISS_CHAR
     ,p_Organization_Id         IN      NUMBER          DEFAULT  G_MISS_NUM
     ,p_Organization_Code       IN      VARCHAR2        DEFAULT  G_MISS_CHAR
     ,p_Primary_Uom_Code        IN      VARCHAR2        DEFAULT  G_MISS_CHAR
     ,x_return_status           OUT NOCOPY  VARCHAR2
     ,x_msg_count               OUT NOCOPY  NUMBER);

The parameters are:

  • P_API_VERSION – A decimal number indicating major and minor revisions to the API. Pass 1.0 unless otherwise indicated in the API parameter list.
  • P_INIT_MSG_LIST – A one-character flag indicating whether to initialize the FND_MSG_PUB package’s message stack at the beginning of API processing (and thus remove any messages that may exist on the stack from prior processing in the same session). Valid values are FND_API.G_TRUE and FND_API.G_FALSE.
  • P_COMMIT – A one-character flag indicating whether to commit work at the end of API processing. Valid values are FND_API.G_TRUE and FND_API.G_FALSE.
  • P_INVENTORY_ITEM_ID – Inventory Item Id of the Item
  • P_ITEM_NUMBER – Segment1 of the Item
  • P_ORGANIZATION_ID – Organization Id of the Organization to whom Item must be assigned
  • P_ORGANIZATION_CODE – 3 character Organization Code of the Organization to whom Item must be assigned
  • P_PRIMARY_UOM_CODE – Primary Unit of Measure of the item.
  • X_RETURN_STATUS – A one-character code indicating whether any errors occurred during processing (in which case error messages will be present on the FND_MSG_PUB package’s message stack). Valid values are FND_API.G_RET_STS_SUCCESS, FND_API.G_RET_STS_ERROR, and FND_API.G_RET_STS_UNEXP_ERROR.
  • X_MSG_COUNT – An integer indicating the number of messages on the FND_MSG_PUB package’s message stack at the end of API processing.

Sample Code: (Tested in R12.1.3)

DECLARE
        g_user_id             fnd_user.user_id%TYPE :=NULL;
        l_appl_id             fnd_application.application_id%TYPE;
        l_resp_id             fnd_responsibility_tl.responsibility_id%TYPE;
        l_api_version		  NUMBER := 1.0;
        l_init_msg_list       VARCHAR2(2) := fnd_api.g_false;
        l_commit		      VARCHAR2(2) := FND_API.G_FALSE;
        x_message_list        error_handler.error_tbl_type;
        x_return_status		  VARCHAR2(2);
        x_msg_count		      NUMBER := 0;
BEGIN
        SELECT fa.application_id
          INTO l_appl_id
          FROM fnd_application fa
         WHERE fa.application_short_name = 'INV';

        SELECT fr.responsibility_id
          INTO l_resp_id
          FROM fnd_application fa, fnd_responsibility_tl fr
         WHERE fa.application_short_name = 'INV'
           AND fa.application_id = fr.application_id
           AND UPPER (fr.responsibility_name) = 'INVENTORY';

        fnd_global.apps_initialize (g_user_id, l_resp_id, l_appl_id);

        EGO_ITEM_PUB.ASSIGN_ITEM_TO_ORG(
                   P_API_VERSION          => l_api_version
                ,  P_INIT_MSG_LIST        => l_init_msg_list
                ,  P_COMMIT               => l_commit
                ,  P_INVENTORY_ITEM_ID    => 1003
                ,  p_item_number          => 000000000001035
                ,  p_organization_id      => 11047
                ,  P_ORGANIZATION_CODE    => 'DXN'
                ,  P_PRIMARY_UOM_CODE     => 'EA'
                ,  X_RETURN_STATUS        => x_return_status
                ,  X_MSG_COUNT            => x_msg_count
            );
        DBMS_OUTPUT.PUT_LINE('Status: '||x_return_status);
        IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
          DBMS_OUTPUT.PUT_LINE('Error Messages :');
          Error_Handler.GET_MESSAGE_LIST(x_message_list=>x_message_list);
            FOR j IN 1..x_message_list.COUNT LOOP
              DBMS_OUTPUT.PUT_LINE(x_message_list(j).message_text);
            END LOOP;
        END IF;
EXCEPTION
        WHEN OTHERS THEN
          dbms_output.put_line('Exception Occured :');
          DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
END;

5 Responses to API to Assign Item to an Organization in Oracle Inventory

  1. Khan. says:

    NICE, I WAS LOOKING FOR THIS, IS IT POSSIBLE TO ASSIGN MULTIPLE ITEMS( PARAMETER AS AN EXCEL FILE WITH 100’S OF ITEMS IN THAT FILE). PLEASE IF YOU HAVE THE CODE PLEASE DO FORWARD TO MY MAILID.

  2. Ian says:

    How would you return x_message_list in a ref cursor using “sys_refcursor” and “select message_text from TABLE(CAST(”

    I am trying to pass the results back to a vb.net app. I can get the basic code to work but I am struggling with the syntax to get the errors back to the client using reference cursors.

    The choice of sys_refcursor is because I do not know a defined cursor for error_handler.error_tbl_type.

    something like:-

    IF (x_return_status FND_API.G_RET_STS_SUCCESS) THEN
    Error_Handler.GET_MESSAGE_LIST(x_message_list=>x_message_list);
    OPEN v_cursor FOR SELECT message_text
    from TABLE(CAST(x_message_list as ????????));
    io_cursor := v_cursor;
    END IF;

  3. Ian says:

    Answering my own question: It cannot be done without the creation of a TABLE prior to the PL/SQL (e.g. GLOBAL TEP TABLE) call and it would also appear that in order to pass back via a cursor you would require a pre-defined package to pass the ref cursor via an input/output parameter.

    It is all related to how the database can support the structure and return values/support a cursor.

    <>

    Without the necessary license to create objects on the database and restricted to PLSQL runtime blocks I found the best way was to make use of Exceptions and raise the messages to the client via that route.

    A straight forward example raising the first error message from a fail is as follows:-

    e.g. PLSQL

    DECLARE
    CREATION_ERROR EXCEPTION;
    verror_text VARCHAR2(2000);
    l_item_table EGO_Item_PUB.Item_Tbl_Type;
    x_item_table EGO_Item_PUB.Item_Tbl_Type;
    x_Inventory_Item_Id mtl_system_items_b.inventory_item_id%TYPE;
    x_Organization_Id mtl_system_items_b.organization_id%TYPE;
    x_return_status VARCHAR2(1);
    x_msg_count NUMBER(10);
    x_msg_data VARCHAR2(1000);
    x_message_list Error_Handler.Error_Tbl_Type;
    BEGIN

    l_item_table(1).Transaction_Type := ‘CREATE’;
    l_item_table(1).Segment1 := ‘PARTNUMBER’;
    l_item_table(1).Description := ‘xxxxxx’;
    ……….

    EGO_ITEM_PUB.Process_Items(p_api_version => 1.0 ,
    p_init_msg_list => FND_API.g_TRUE ,
    p_commit => FND_API.g_TRUE ,
    p_Item_Tbl => l_item_table ,
    x_Item_Tbl => x_item_table ,
    x_return_status => x_return_status ,
    x_msg_count => x_msg_count);

    IF (x_return_status FND_API.G_RET_STS_SUCCESS) THEN
    Error_Handler.GET_MESSAGE_LIST(x_message_list=>x_message_list);
    FOR i IN 1..x_message_list.COUNT LOOP
    verror_text :=x_message_list(i).message_text;
    RAISE CREATION_ERROR;
    END LOOP;
    END IF;

    COMMIT;

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    ROLLBACK;
    raise_application_error(-20999, SQLERRM);
    WHEN DUP_VAL_ON_INDEX THEN
    ROLLBACK;
    raise_application_error(-20999, SQLERRM);
    WHEN CREATION_ERROR THEN
    ROLLBACK;
    raise_application_error(-20999, verror_text);
    WHEN OTHERS THEN
    ROLLBACK;
    raise_application_error(-20999, SQLERRM);
    END;

    Within the vb.net client you would return the error like:-

    vb.net example
    ‘ Assuming you have
    ‘Imports Oracle.DataAccess.Client ‘ ODP.NET Oracle managed provider
    ‘ Dim OraConnxn As OracleConnection
    ‘ Dim OleCmd As OracleCommand

    OleCmd = New OracleCommand(sSQL, OraConnxn)
    Try
    lResult = OleCmd.ExecuteNonQuery()
    Catch myex As Exception
    MsgBox(myex.Message)
    End Try

    You can improve on all this by adding more to the error or interrogating it more thoroughly on either side of the call i.e. Oracle PL?SQL better construction or vb.net better decipher

    I hope this helps

  4. Akil Pariyani says:

    Hello,
    We required to copy item attribute from on OU to another OU,
    Is there any API that will helpfull for the same?

  5. Akil Pariyani says:

    Hello,
    We required to copy item attribute from on OU to another OU,
    Is there any API that will helpfull for the same?

    Thanks and regards,
    Akil.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: