4 Replies Latest reply on May 19, 2017 9:27 AM by sahar.zd

    FDPSTP failed due to ORA-06550

    sahar.zd

      Dear All,

      I have a problem, I have created a stored procedure that run successfully from PL/SQL

      but when I define it in the application (executable / define / add it in the requested responsibility) and run it; it gives me error

       

      Cause: FDPSTP failed due to ORA-06550: line 1, column 7:

      PLS-00306: wrong number or types of arguments in call to 'XX_PROC_TRANSFER_IFS_SOB'

      ORA-06550: line 1, column 7:

      PL/SQL: Statement ignored

       

       

      my procedure :

       

       

      CREATE OR REPLACE PROCEDURE APPS.XX_PROC_TRANSFER_IFS_SOB  AS

      T_ITEM varchar2(100);

      T_QUANTITY varchar2(100) ;

      T_SUBINVENTORY_CODE varchar2(100);

      T_TRANSFER_SUBINVENTORY varchar2(100);

      T_TRANSACTION_DATE varchar2(100);

      T_TRANSFER_ORGANIZATION_NAME varchar2(100);

      k number(10):=0;

      l_return_status VARCHAR2(100);

      l_msg_count NUMBER;

      l_msg_data  VARCHAR2(500);

      l_trans_count   VARCHAR2(100);

      retval number;

      l_exit_error varchar2(500);

      r_exit_organization_id   hr_organization_units.organization_id %type;

      r_exit_inventory_item_id   MTL_SYSTEM_ITEMS_B.inventory_item_id%type;

      r_Description MTL_SYSTEM_ITEMS_B.Description%type;

      r_Segment1 MTL_SYSTEM_ITEMS_B.Segment1%type;

      r_exit_primary_uom_code MTL_SYSTEM_ITEMS_B.Primary_unit_of_measure%type;

      r_ORGANIZATION_Name  ORG_ORGANIZATION_DEFINITIONS.ORGANIZATION_Name%type;

      r_ORGANIZATION_Code   ORG_ORGANIZATION_DEFINITIONS.ORGANIZATION_CODE%type;

      r_organization_id   ORG_ORGANIZATION_DEFINITIONS.organization_id%type;

      l_transaction_interface_id_ex  mtl_transactions_interface.transaction_interface_id%type;

      l_transaction_header_id_ex     mtl_transactions_interface.transaction_header_id%type;

      l_exit_transaction_type_id mtl_transaction_types.transaction_type_id%type;

      l_Category  varchar2(100);

      r_exit_quantity varchar2(100);

      CURSOR C_INV is

      select

      ITEM ,

      QUANTITY ,

      SUBINVENTORY_CODE,

      TRANSFER_SUBINVENTORY ,

      TRANSACTION_DATE ,

      TRANSFER_ORGANIZATION_NAME

      from XX_TBL_TRANSFER_IFS_SOB

      --where ROWNUM = 1

      ;

      BEGIN

      OPEN C_INV;

      LOOP

      FETCH C_INV  into  T_ITEM , T_QUANTITY ,T_SUBINVENTORY_CODE,T_TRANSFER_SUBINVENTORY ,T_TRANSACTION_DATE ,T_TRANSFER_ORGANIZATION_NAME ;

      EXIT WHEN C_INV %notfound;

      k := k+1;

      select organization_id  into r_exit_organization_id  from hr_organization_units where Name = 'In-Flight Services';

      select B.inventory_item_id, B.Description, B.Segment1,B.Primary_UOM_CODE

      into r_exit_inventory_item_id ,r_Description, r_Segment1, r_exit_primary_uom_code

      from MTL_SYSTEM_ITEMS_B B where B.Segment1 like T_ITEM and organization_id = r_exit_organization_id ;

      select organization_id, ORGANIZATION_Name, ORGANIZATION_Code

      into r_organization_id, r_ORGANIZATION_Name , r_ORGANIZATION_Code

      from ORG_ORGANIZATION_DEFINITIONS  where ORGANIZATION_Code  = T_TRANSFER_ORGANIZATION_NAME ;

      r_exit_quantity := -1 * T_QUANTITY;

      l_transaction_interface_id_ex  := mtl_material_transactions_s.NEXTVAL;

      SELECT mtl_material_transactions_s.NEXTVAL

      INTO l_transaction_header_id_ex

      FROM DUAL;

       

      dbms_output.put_line(r_exit_organization_id || ' , ' || T_ITEM  || ' , ' || T_QUANTITY || ',' || T_SUBINVENTORY_CODE || ',' || T_TRANSFER_SUBINVENTORY|| ',' ||TO_DATE(T_TRANSACTION_DATE,'DD-MM-YYYY HH24:MI:SS') || ',' ||  T_TRANSFER_ORGANIZATION_NAME || ' , ' ||  r_exit_inventory_item_id || ',' || r_Description || ',' || r_Segment1 || ',' || r_exit_primary_uom_code || ',' || r_ORGANIZATION_Name || ',' || r_ORGANIZATION_Code || ',' || r_exit_quantity);

      l_Category := 'Subinventory Transfer';--'Manual';

      SELECT transaction_type_id

      INTO l_exit_transaction_type_id

      FROM mtl_transaction_types

      WHERE transaction_type_name = 'Subinventory Transfer';

      dbms_output.put_line('Interface starts');

      /*--------- Insert into Interface -----------*/

      INSERT INTO mtl_transactions_interface (

      transaction_interface_id,

      transaction_header_id,

      source_code,

      source_line_id,

      source_header_id,

      process_flag,

      transaction_mode,

      lock_flag,

      last_update_date,

      last_updated_by,

      creation_date,

      created_by,

      inventory_item_id,

      organization_id,

      transaction_quantity,

      transaction_uom,

      transaction_date,

      subinventory_code,

      transaction_type_id,

      TRANSFER_SUBINVENTORY,

      TRANSFER_ORGANIZATION

      --transfer_locator

      )

      VALUES (

      l_transaction_interface_id_ex,    --transaction_interface_id which is equal to mtl_material_transactions_s.NEXTVAL

      l_transaction_header_id_ex,

      l_Category,                       --source_code this source code helps us to categorize transactions

      -111,                                 --source_line_id,   this Id is definition ID for your source which is read only

      11,                                     --source_header_id   ,this ID is not read only. Can be used to identify record.

      1,                                       -- process_flag,(yes)      , 1 means process it

      2,                                  

      2,                                       -- lock_flag

      TO_DATE('2017/04/30','YYYY-MM-DD'),  --SYSDATE,                         -- last_update_date,

      0,--fnd_global.user_id,      -- last_updated_by

      TO_DATE('2017/04/30','YYYY-MM-DD') ,  --SYSDATE,                         -- creation_date,

      0,--fnd_global.user_id,      -- created_by,

      r_exit_inventory_item_id,    -- inventory_item_id inventory_item_id of item to be transacted

      r_exit_organization_id,        -- organization_id        source organization_id of item

      T_QUANTITY,     -- transaction_quantity        This is an exit transaction. So my transaction quantity must be multiplied by -1

      r_exit_primary_uom_code,       -- transaction_uom primary_uom_code of item to be transacted

      TO_DATE(T_TRANSACTION_DATE,'DD-MM-YYYY HH24:MI:SS'),      -- transaction_date

      T_SUBINVENTORY_CODE,    -- subinventory_code subinventory_code that the item to be exited

      l_exit_transaction_type_id ,                   --exit this is an important column. This determines which action to be transacted.

      --NULL

      T_TRANSFER_SUBINVENTORY,

      r_organization_id

      );

      dbms_output.put_line('Interface end');

      dbms_output.put_line('mtl_serial_numbers_interface start');

      INSERT INTO mtl_serial_numbers_interface (

      transaction_interface_id,

      source_code,

      source_line_id,

      --fm_serial_number,

      --to_serial_number,

      process_flag,

      created_by,

      creation_date,

      last_updated_by,

      last_update_date)

      VALUES (l_transaction_interface_id_ex,

      l_Category,       -- source_code,

      -111,                               -- source_line_id,

      --r_exit_onhand.old_serial_number,

      --r_exit_onhand.old_serial_number,

      1,                               -- process_flag,(yes)

      0,--fnd_global.user_id,

      TO_DATE('2017/04/30','YYYY-MM-DD'),--SYSDATE,

      0,--fnd_global.user_id,

      TO_DATE('2017/04/30','YYYY-MM-DD')

      --SYSDATE

      );

      dbms_output.put_line('mtl_serial_numbers_interface end');

      dbms_output.put_line('mtl_transaction_lots_interface start');

      INSERT INTO mtl_transaction_lots_interface

                                 (transaction_interface_id,

                                  lot_number,

                                  source_line_id,

                                  lot_expiration_date,

                                  transaction_quantity,

                                  last_update_date,

                                 last_updated_by,

                                  creation_date,

                                 created_by ,

              source_code,

                                  process_flag

                                 )

                          VALUES (l_transaction_interface_id_ex,

                                   k,--  rec_lot_num.lot_number,

                                 -111,

                                  SYSDATE + 10000,               --Lot expiration date

                                  T_QUANTITY,

                                  TO_DATE('2017/04/30','YYYY-MM-DD'),---SYSDATE,                        --last update date

                                0, -- fnd_global.user_id,

                                 TO_DATE('2017/04/30','YYYY-MM-DD'), -- SYSDATE,                           --creation date

                              0, --  fnd_global.user_id,

               l_Category,

                                 1

                                     );   

      dbms_output.put_line('mtl_transaction_lots_interface end');

      COMMIT;

      retval :=

      INV_TXN_MANAGER_PUB.process_transactions (

      p_api_version     => 1.0,

      p_init_msg_list   => fnd_api.g_true,

      --p_init_msg_list         => 'T',

      p_commit                => 'T',

      x_return_status   => l_return_status,

      x_msg_count       => l_msg_count,

      x_msg_data        => l_msg_data,

      x_trans_count     => l_trans_count,

      p_header_id       => l_transaction_header_id_ex );--transaction_header_id that we determined before

      dbms_output.put_line('l_msg_data:'||l_msg_data);

      dbms_output.put_line('l_return_status:'||l_return_status);

      dbms_output.put_line('v_header_id:'||l_transaction_header_id_ex);

      dbms_output.put_line('retval:'||l_transaction_header_id_ex);

      END LOOP;

      CLOSE C_INV;

      dbms_output.put_line(k);

      SELECT error_explanation

      INTO l_exit_error

      FROM mtl_transactions_interface

      WHERE transaction_interface_id = l_transaction_interface_id_ex

      --AND transaction_header_id = l_transaction_header_id_ex

      AND source_code = 'Subinventory Transfer';

      EXCEPTION

      WHEN NO_DATA_FOUND THEN

      l_exit_error := NULL;

      dbms_output.put_line(l_exit_error );

      END;

      /

       

       

      "please advice..

       

      thank you in advance

        • 1. Re: FDPSTP failed due to ORA-06550
          mdtaylor

          Add the mandatory errbuf and retcode parameters to your procedure and retest.

           

          CREATE OR REPLACE PROCEDURE k_req_test_submit(errbuf OUT VARCHAR2, retcode OUT NUMBER) IS

          1 person found this helpful
          • 2. Re: FDPSTP failed due to ORA-06550
            sahar.zd

            thank you so much it worked perfectly

            • 3. Re: FDPSTP failed due to ORA-06550
              mdtaylor

              Awesome.  Glad it worked and thanks for letting me know.

              • 4. Re: FDPSTP failed due to ORA-06550
                sahar.zd

                Dear

                i tried to run the same procedure today after adding the parameters :

                 

                ORACLE error 6550 in FDPSTP

                 

                Cause: FDPSTP failed due to ORA-06550: line 1, column 7:

                PLS-00306: wrong number or types of arguments in call to 'XX_PROC_TRANSFER_IFS_SOB'

                ORA-06550: line 1, column 7:

                PL/SQL: Statement ignored

                 

                 

                 

                 

                CREATE OR REPLACE PROCEDURE APPS.XX_PROC_TRANSFER_IFS_SOB(errbuf OUT VARCHAR2, retcode OUT NUMBER)  AS

                T_ITEM varchar2(100);

                T_QUANTITY varchar2(100) ;

                T_SUBINVENTORY_CODE varchar2(100);

                T_TRANSFER_SUBINVENTORY varchar2(100);

                T_TRANSACTION_DATE varchar2(100);

                T_TRANSFER_ORGANIZATION_NAME varchar2(100);

                k number(10):=0;

                l_return_status VARCHAR2(100);

                l_msg_count NUMBER;

                l_msg_data  VARCHAR2(500);

                l_trans_count   VARCHAR2(100);

                retval number;

                l_exit_error varchar2(500);

                r_exit_organization_id   hr_organization_units.organization_id %type;

                r_exit_inventory_item_id   MTL_SYSTEM_ITEMS_B.inventory_item_id%type;

                r_Description MTL_SYSTEM_ITEMS_B.Description%type;

                r_Segment1 MTL_SYSTEM_ITEMS_B.Segment1%type;

                r_exit_primary_uom_code MTL_SYSTEM_ITEMS_B.Primary_unit_of_measure%type;

                r_ORGANIZATION_Name  ORG_ORGANIZATION_DEFINITIONS.ORGANIZATION_Name%type;

                r_ORGANIZATION_Code   ORG_ORGANIZATION_DEFINITIONS.ORGANIZATION_CODE%type;

                r_organization_id   ORG_ORGANIZATION_DEFINITIONS.organization_id%type;

                l_transaction_interface_id_ex  mtl_transactions_interface.transaction_interface_id%type;

                l_transaction_header_id_ex     mtl_transactions_interface.transaction_header_id%type;

                l_exit_transaction_type_id mtl_transaction_types.transaction_type_id%type;

                l_Category  varchar2(100);

                r_exit_quantity varchar2(100);

                CURSOR C_INV is

                select

                ITEM ,

                QUANTITY ,

                SUBINVENTORY_CODE,

                TRANSFER_SUBINVENTORY ,

                TRANSACTION_DATE ,

                TRANSFER_ORGANIZATION_NAME

                from XX_TBL_TRANSFER_IFS_SOB

                --where ROWNUM = 1

                ;

                BEGIN

                OPEN C_INV;

                LOOP

                FETCH C_INV  into  T_ITEM , T_QUANTITY ,T_SUBINVENTORY_CODE,T_TRANSFER_SUBINVENTORY ,T_TRANSACTION_DATE ,T_TRANSFER_ORGANIZATION_NAME ;

                EXIT WHEN C_INV %notfound;

                k := k+1;

                select organization_id  into r_exit_organization_id  from hr_organization_units where Name = 'In-Flight Services';

                select B.inventory_item_id, B.Description, B.Segment1,B.Primary_UOM_CODE

                into r_exit_inventory_item_id ,r_Description, r_Segment1, r_exit_primary_uom_code

                from MTL_SYSTEM_ITEMS_B B where B.Segment1 like T_ITEM and organization_id = r_exit_organization_id ;

                select organization_id, ORGANIZATION_Name, ORGANIZATION_Code

                into r_organization_id, r_ORGANIZATION_Name , r_ORGANIZATION_Code

                from ORG_ORGANIZATION_DEFINITIONS  where ORGANIZATION_Code  = T_TRANSFER_ORGANIZATION_NAME ;

                r_exit_quantity := -1 * T_QUANTITY;

                l_transaction_interface_id_ex  := mtl_material_transactions_s.NEXTVAL;

                SELECT mtl_material_transactions_s.NEXTVAL

                INTO l_transaction_header_id_ex

                FROM DUAL;

                --l_transaction_header_id_ex :=  xxtg_transaction_header_id_s.NEXTVAL;

                dbms_output.put_line(r_exit_organization_id || ' , ' || T_ITEM  || ' , ' || T_QUANTITY || ',' || T_SUBINVENTORY_CODE || ',' || T_TRANSFER_SUBINVENTORY|| ',' ||TO_DATE(T_TRANSACTION_DATE,'DD-MM-YYYY HH24:MI:SS') || ',' ||  T_TRANSFER_ORGANIZATION_NAME || ' , ' ||  r_exit_inventory_item_id || ',' || r_Description || ',' || r_Segment1 || ',' || r_exit_primary_uom_code || ',' || r_ORGANIZATION_Name || ',' || r_ORGANIZATION_Code || ',' || r_exit_quantity);

                l_Category := 'Subinventory Transfer';--'Manual';

                SELECT transaction_type_id

                INTO l_exit_transaction_type_id 

                FROM mtl_transaction_types

                WHERE transaction_type_name = 'Subinventory Transfer';

                dbms_output.put_line('Interface starts');

                /*--------- Insert into Interface -----------*/

                INSERT INTO mtl_transactions_interface (

                transaction_interface_id,

                transaction_header_id,

                source_code,

                source_line_id,

                source_header_id,

                process_flag,

                transaction_mode,

                lock_flag,

                last_update_date,

                last_updated_by,

                creation_date,

                created_by,

                inventory_item_id,

                organization_id,

                transaction_quantity,

                transaction_uom,

                transaction_date,

                subinventory_code,

                transaction_type_id,

                TRANSFER_SUBINVENTORY,

                TRANSFER_ORGANIZATION

                --transfer_locator

                )

                VALUES (

                l_transaction_interface_id_ex,    --transaction_interface_id which is equal to mtl_material_transactions_s.NEXTVAL

                l_transaction_header_id_ex,

                l_Category,                       --source_code this source code helps us to categorize transactions

                -111,                                 --source_line_id,   this Id is definition ID for your source which is read only

                11,                                     --source_header_id   ,this ID is not read only. Can be used to identify record.

                1,                                       -- process_flag,(yes)      , 1 means process it

                2,                                   

                2,                                       -- lock_flag

                --TO_DATE('2017/04/30','YYYY-MM-DD'), 

                SYSDATE,                         -- last_update_date,

                0,--fnd_global.user_id,      -- last_updated_by

                --TO_DATE('2017/04/30','YYYY-MM-DD') , 

                SYSDATE,                         -- creation_date,

                0,--fnd_global.user_id,      -- created_by,

                r_exit_inventory_item_id,    -- inventory_item_id inventory_item_id of item to be transacted

                r_exit_organization_id,        -- organization_id        source organization_id of item

                T_QUANTITY,     -- transaction_quantity        This is an exit transaction. So my transaction quantity must be multiplied by -1

                r_exit_primary_uom_code,       -- transaction_uom primary_uom_code of item to be transacted

                TO_DATE(T_TRANSACTION_DATE,'DD-MM-YYYY HH24:MI:SS'),      -- transaction_date

                T_SUBINVENTORY_CODE,    -- subinventory_code subinventory_code that the item to be exited

                l_exit_transaction_type_id ,                   --exit this is an important column. This determines which action to be transacted.

                --NULL

                T_TRANSFER_SUBINVENTORY,

                r_organization_id

                );

                dbms_output.put_line('Interface end');

                dbms_output.put_line('mtl_serial_numbers_interface start');

                INSERT INTO mtl_serial_numbers_interface (

                transaction_interface_id,

                source_code,

                source_line_id,

                --fm_serial_number,

                --to_serial_number,

                process_flag,

                created_by,

                creation_date,

                last_updated_by,

                last_update_date)

                VALUES (l_transaction_interface_id_ex,

                l_Category,       -- source_code,

                -111,                               -- source_line_id,

                --r_exit_onhand.old_serial_number,

                --r_exit_onhand.old_serial_number,

                1,                               -- process_flag,(yes)

                0,--fnd_global.user_id,

                --TO_DATE('2017/04/30','YYYY-MM-DD'),

                SYSDATE,

                0,--fnd_global.user_id,

                --TO_DATE('2017/04/30','YYYY-MM-DD')

                SYSDATE

                );

                dbms_output.put_line('mtl_serial_numbers_interface end');

                dbms_output.put_line('mtl_transaction_lots_interface start');

                INSERT INTO mtl_transaction_lots_interface

                                           (transaction_interface_id,

                                            lot_number,

                                            source_line_id,

                                            lot_expiration_date,

                                            transaction_quantity,

                                            last_update_date,

                                           last_updated_by,

                                            creation_date,

                                           created_by ,

                           source_code,

                                            process_flag

                                           )

                                    VALUES (l_transaction_interface_id_ex,

                                             k,--  rec_lot_num.lot_number,

                                           -111,

                                            SYSDATE + 10000,               --Lot expiration date

                                            T_QUANTITY,

                                          -- TO_DATE('2017/04/30','YYYY-MM-DD'),

                           SYSDATE,                        --last update date

                                          0, -- fnd_global.user_id,

                                          -- TO_DATE('2017/04/30','YYYY-MM-DD'),

                          SYSDATE,                           --creation date

                                           0, --  fnd_global.user_id,

                            l_Category,

                                           1

                                               );    

                dbms_output.put_line('mtl_transaction_lots_interface end');

                COMMIT;

                retval :=

                INV_TXN_MANAGER_PUB.process_transactions (

                p_api_version     => 1.0,

                p_init_msg_list   => fnd_api.g_true,

                --p_init_msg_list         => 'T',

                p_commit                => 'T',

                x_return_status   => l_return_status,

                x_msg_count       => l_msg_count,

                x_msg_data        => l_msg_data,

                x_trans_count     => l_trans_count,

                p_header_id       => l_transaction_header_id_ex );--transaction_header_id that we determined before

                dbms_output.put_line('l_msg_data:'||l_msg_data);

                dbms_output.put_line('l_return_status:'||l_return_status);

                dbms_output.put_line('v_header_id:'||l_transaction_header_id_ex);

                dbms_output.put_line('retval:'||l_transaction_header_id_ex);

                END LOOP;

                CLOSE C_INV;

                delete from XX_TBL_TRANSFER_IFS_SOB;

                commit;

                dbms_output.put_line(k);

                SELECT error_explanation

                INTO l_exit_error

                FROM mtl_transactions_interface

                WHERE transaction_interface_id = l_transaction_interface_id_ex

                --AND transaction_header_id = l_transaction_header_id_ex

                AND source_code = 'Subinventory Transfer';

                EXCEPTION

                WHEN NO_DATA_FOUND THEN

                l_exit_error := NULL;

                dbms_output.put_line(l_exit_error );

                 

                END;

                 

                can you please re-check

                 

                thanks..