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.
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    => 1004
                ,  p_item_number          => TEST1010
                ,  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;
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply