3 Replies Latest reply: May 8, 2012 12:27 AM by Sandeep Gandhi, Consultant RSS

    Due to trigger I am not able to create new item in apps please help

    user10683443
      I have created trigger on MTL_SYSTEM_ITEMS table.
      Due to which i am not able to insert new item from apps frontend.

      trigger code

      CREATE OR REPLACE TRIGGER XX_MPC_INVENTORY_TRG
      AFTER INSERT
      ON MTL_SYSTEM_ITEMS
      FOR EACH ROW
      DECLARE
      P_INVENTORY_ITEM_ID NUMBER;
      BEGIN
      XX_MPC_INVENTORY_PKG.XX_MPC_INVENTORY_PRC(P_INVENTORY_ITEM_ID);
      END;

      Can you please suggest some alternative

      Thanks,
      Mihir
        • 1. Re: Due to trigger I am not able to create new item in apps please help
          Sandeep Gandhi, Consultant
          Either disable the trigger or fix the code in XX_MPC_INVENTORY_PKG that is causing the problem.

          Sandeep Gandhi
          • 2. Re: Due to trigger I am not able to create new item in apps please help
            user10683443
            can you please suggest how it can be within the package. please find the pkg code

            CREATE OR REPLACE PACKAGE XX_MPC_INVENTORY_PKG AS
            PROCEDURE XX_MPC_INVENTORY_PRC (P_INVENTORY_ITEM_ID IN NUMBER);
            END XX_MPC_INVENTORY_PKG;


            CREATE OR REPLACE PACKAGE BODY XX_MPC_INVENTORY_PKG AS

            PROCEDURE XX_MPC_INVENTORY_PRC(P_INVENTORY_ITEM_ID IN NUMBER)

            IS
            PRAGMA AUTONOMOUS_TRANSACTION;
            L_EVENT_KEY NUMBER;
            L_EVENT_DATA CLOB;
            L_TEXT VARCHAR2(32000);

            /*--------------------
            L_AWARD_NUMBER VARCHAR2(20);
            L_FUNDING_SOURCE VARCHAR2(200);
            L_FUNDING_AMOUNT NUMBER;
            L_SEGMENT1 VARCHAR2(20);
            L_NAME VARCHAR2(200);
            L_DESCRIPTION VARCHAR2(200);
            L_START_DATE DATE;
            L_COMPLETION_DATE DATE;
            L_PROJECT_CURRENCY_CODE VARCHAR2(10);
            L_PROJECT_TYPE VARCHAR2(20);
            L_PROJECT_STATUS_CODE VARCHAR2(20);
            L_ORGANIZATION_NAME VARCHAR2(50);*/

            CURSOR GET_SEQ IS
            SELECT XXMPC_CREATE_INVENTORY_TEST_S.NEXTVAL FROM DUAL;

            CURSOR XXMPC_XML_CUR IS(

            /* Formatted on 2012/05/01 13:34 (Formatter Plus v4.8.8) */
            SELECT TO_CHAR (SYSDATE, 'yyyymmdd') system_date,
            TO_CHAR (SYSDATE, 'hhmiss') system_time,
            MSI.INVENTORY_ITEM_ID
            FROM MTL_SYSTEM_ITEMS_B MSI
            WHERE
            MSI.INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
            );


            BEGIN

            OPEN GET_SEQ;
            FETCH GET_SEQ
            INTO L_EVENT_KEY;
            CLOSE GET_SEQ;

            FOR XXMPC_XML_REC IN XXMPC_XML_CUR LOOP
            DBMS_LOB.CREATETEMPORARY(L_EVENT_DATA, FALSE, DBMS_LOB.CALL);
            L_TEXT := '<?xml version =''1.0'' encoding =''ASCII''?>';
            DBMS_LOB.WRITEAPPEND(L_EVENT_DATA, LENGTH(L_TEXT), L_TEXT);
            L_TEXT := '<Item>';
            DBMS_LOB.WRITEAPPEND(L_EVENT_DATA, LENGTH(L_TEXT), L_TEXT);
            L_TEXT := '<Header>';
            DBMS_LOB.WRITEAPPEND(L_EVENT_DATA, LENGTH(L_TEXT), L_TEXT);

            L_TEXT := '<SystemDate>';
            L_TEXT := L_TEXT || (XXMPC_XML_REC.system_date);
            L_TEXT := L_TEXT || '</SystemDate>';
            DBMS_LOB.WRITEAPPEND(L_EVENT_DATA, LENGTH(L_TEXT), L_TEXT);

            L_TEXT := '<SystemTime>';
            L_TEXT := L_TEXT || (XXMPC_XML_REC.system_time);
            L_TEXT := L_TEXT || '</SystemTime>';
            DBMS_LOB.WRITEAPPEND(L_EVENT_DATA, LENGTH(L_TEXT), L_TEXT);


            L_TEXT := '<Inventory_Item_Id>';
            L_TEXT := L_TEXT || (XXMPC_XML_REC.Inventory_Item_Id);
            L_TEXT := L_TEXT || '</Inventory_Item_Id>';
            DBMS_LOB.WRITEAPPEND(L_EVENT_DATA, LENGTH(L_TEXT), L_TEXT);



            END LOOP;


            L_TEXT := '</Item>';
            DBMS_LOB.WRITEAPPEND(L_EVENT_DATA, LENGTH(L_TEXT), L_TEXT);

            --dbms_output.put_line(l_text);
            -- raise the event with the event data
            wf_event.raise(p_event_name=>'oracle.apps.mtl.Item.create'
            ,p_event_key=>l_event_key
            ,p_event_data=>l_event_data);
            INSERT INTO XXMPC_CREATE_INVENTORY_TEST
            VALUES
            (L_EVENT_KEY, SYSDATE, L_EVENT_DATA);
            COMMIT;
            --end ;

            END XX_MPC_INVENTORY_PRC;
            END XX_MPC_INVENTORY_PKG;
            • 3. Re: Due to trigger I am not able to create new item in apps please help
              Sandeep Gandhi, Consultant
              First disable the trigger and create an item.
              If it works, you are fairly certain that the problem is in the trigger.
              After that comment out the call to the custom procedure in your trigger and create an item

              If it works, you are fairly certain that the problem is in the procedure.

              After that, add an EXCEPTION code in your procedure and capture the error message. This will help you identify where in the procedure you encounter a problem.

              Sandeep Gandhi