Friday, November 16, 2012

Oracle API For Assign the Item to catalog



SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE AssignItmToCat(p_segment1 IN VARCHAR2, p_category_set_name IN VARCHAR2, p_category_name IN VARCHAR2)
AS
        l_api_version    NUMBER := 1.0;
        l_init_msg_list     VARCHAR2(2) := FND_API.G_TRUE;
        l_commit     VARCHAR2(2) := FND_API.G_FALSE;
       
        l_category_id            NUMBER;
        l_category_set_id        NUMBER;
        l_transaction_type       VARCHAR2(20) := EGO_ITEM_PUB.G_TTYPE_CREATE;     
   
        x_message_list           Error_Handler.Error_Tbl_Type;
        x_return_status     VARCHAR2(2);
        x_msg_count      NUMBER := 0;
        x_msg_data               VARCHAR2(255);
        x_error_code             NUMBER;
   
    l_user_id     NUMBER := -1;
    l_resp_id     NUMBER := -1;
    l_application_id  NUMBER := -1;
        l_rowcnt     NUMBER := 1;
        l_user_name      VARCHAR2(30) := 'MGRPLM';
        l_resp_name      VARCHAR2(30) := 'EGO_DEVELOPMENT_MANAGER';   

        CURSOR csr_org_items IS
        SELECT inventory_item_id, organization_id
        FROM mtl_system_items_b
        WHERE segment1 like p_segment1;

BEGIN

    -- Get the user_id
    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
    WHERE responsibility_key = l_resp_name;

    FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_application_id);  -- MGRPLM / Development Manager / EGO
    dbms_output.put_line('Initialized applications context: '|| l_user_id || ' '|| l_resp_id ||' '|| l_application_id );
 
        SELECT category_set_id INTO l_category_set_id FROM mtl_category_sets
        WHERE category_set_name = p_category_set_name;  -- 'Product Family'
       
        SELECT category_id INTO l_category_id FROM mtl_categories_b
        WHERE segment1 = p_category_name; -- 'Consumer Goods'     
       
        -- call API to load Items
       DBMS_OUTPUT.PUT_LINE('====================================================');
       DBMS_OUTPUT.PUT_LINE('Calling EGO_ITEM_PUB.Process_Item_Cat_Assignment API');       
 
      FOR itm IN csr_org_items
      LOOP
              EGO_ITEM_PUB.Process_Item_Cat_Assignment
             (
                    l_api_version     
                  , l_init_msg_list   
                  , l_commit          
                  , l_category_id     
                  , l_category_set_id 
                  , itm.inventory_item_id
                  , itm.organization_id 
                  , l_transaction_type
                  , x_return_status   
                  , x_error_code       
                  , x_msg_count       
                  , x_msg_data        
             );
    
      END LOOP;
     
       DBMS_OUTPUT.PUT_LINE('==================================================');
       DBMS_OUTPUT.PUT_LINE('Return Status: '||x_return_status);

       IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
          DBMS_OUTPUT.PUT_LINE('Error Message Count :'||x_msg_count);
          DBMS_OUTPUT.PUT_LINE('Error Message :'||x_msg_data);
       END IF;
       DBMS_OUTPUT.PUT_LINE('=========================================');      
       
EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('Exception Occured :');
          DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
          DBMS_OUTPUT.PUT_LINE('========================================');
END;


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect