7 Replies Latest reply on Aug 13, 2004 5:33 PM by 137783

    How Use Arrays on 9iAS Integration

    137783
      Hi I'm triying to use Arrays on Interconnect
      But I don't kwon how to use the Functions Generated by iStudio, because when I use it on a trigger all information pass to the hub as null values.

      Can Somebody help me with this??
      I send the structure of the trigger

      DECLARE
      MSG_ID NUMBER;
      AO_ID NUMBER;
      wCrga NUMBER;
      BEGIN
      FOR wrInitco IN (SELECT *
      FROM ERP_KARDEXDET
      WHERE
      compania = :new.compania
      AND sucursal = :new.sucursal
      AND seccion = :new.seccion
      AND tpo_dcmnto = :new.tpo_dcmnto
      AND nmro_srie = :new.nmro_srie
      AND numero = :new.numero
      order by nmro_item)
      LOOP
      --
      wCrga := hub_almacenes.cr_erp_kardexdetalle_ERP_KARDE(
      wrInitco.COMPANIA
      ,wrInitco.SUCURSAL
      ,wrInitco.SECCION
      ,wrInitco.TPO_DCMNTO
      ,wrInitco.NMRO_SRIE
      ,wrInitco.NUMERO
      ,wrInitco.NMRO_ITEM
      ,wrInitco.PROCOM_COMPANIA
      ,wrInitco.PRODUCTO
      ,wrInitco.CNTDAD_PRDCTO
      ,wrInitco.ANO
      ,wrInitco.MES
      ,wrInitco.TTAL_ITEM
      ,wrInitco.TTAL_ITEM_DLRES
      ,wrInitco.VLOR_UNTRIO
      ,wrInitco.DESCUENTO
      ,wrInitco.CSTO_PRMDIO
      ,wrInitco.CSTO_ULTMO
      ,wrInitco.MONEDA
      ,wrInitco.GLOSA
      ,wrInitco.TPO_CMBIO
      ,wrInitco.TPO_MVMNTO_INVNTRIO
      ,MSG_ID
      ,AO_ID);
      END LOOP;
      hub_almacenes.crMsg_registro_documento_OAI_V(MSG_ID,AO_ID);
      --
      hub_almacenes.inv_registro_documento_OAI_V1(MSG_ID,'CHSP');

      end;
        • 1. Re: How Use Arrays on 9iAS Integration
          137783
          Could anybody help me?
          • 2. Re: How Use Arrays on 9iAS Integration
            349404
            Ricardo,

            You're calling the iStudio generated PL/SQL procedures in the wrong order. Try the following:

            DECLARE

                 msg_id NUMBER;
                 ao_id NUMBER;
                 wcrga NUMBER;

                 CURSOR c_get_erp_kardexdet IS
            SELECT *     
            FROM ERP_KARDEXDET
            WHERE     compania = :new.compania
                      AND sucursal = :new.sucursal
                      AND seccion = :new.seccion
                      AND tpo_dcmnto = :new.tpo_dcmnto
                      AND nmro_srie = :new.nmro_srie
                      AND numero = :new.numero
                      ORDER BY nmro_item;     

            BEGIN

                 hub_almacenes.crMsg_registro_documento_OAI_V(
                 msg_id
                 ,ao_id
                 );

                 FOR wrInitco IN c_get_erp_kardexdet     LOOP

                      wcrga := hub_almacenes.cr_erp_kardexdetalle_ERP_KARDE(
                      wrInitco.COMPANIA
                 ,wrInitco.SUCURSAL
                      ,wrInitco.SECCION
                      ,wrInitco.TPO_DCMNTO
                      ,wrInitco.NMRO_SRIE
                      ,wrInitco.NUMERO
                      ,wrInitco.NMRO_ITEM
                      ,wrInitco.PROCOM_COMPANIA
                      ,wrInitco.PRODUCTO
                      ,wrInitco.CNTDAD_PRDCTO
                      ,wrInitco.ANO
                      ,wrInitco.MES
                      ,wrInitco.TTAL_ITEM
                      ,wrInitco.TTAL_ITEM_DLRES
                      ,wrInitco.VLOR_UNTRIO
                      ,wrInitco.DESCUENTO
                      ,wrInitco.CSTO_PRMDIO
                      ,wrInitco.CSTO_ULTMO
                      ,wrInitco.MONEDA
                      ,wrInitco.GLOSA
                      ,wrInitco.TPO_CMBIO
                      ,wrInitco.TPO_MVMNTO_INVNTRIO
                      ,msg_id
                      ,ao_id
                      );

                 END LOOP;

                 hub_almacenes.inv_registro_documento_OAI_V1(
                 msg_id
                 ,'chsp'
                 );

            end;

            Hope this is helpful.

            Thanks,
            Markus

            ____________________________________________

            Markus Spohn
            Principal Product Manager
            OracleAS Integration

            Oracle Corporation
            Worldwide Headquarters
            500 Oracle Parkway m/s 1op627
            Redwood Shores, CA 94065
            U.S.A.

            e-Mail: markus.spohn@oracle.com
            ____________________________________________
            • 3. Re: How Use Arrays on 9iAS Integration
              137783
              Markus
              I tried the following example more simple

              DECLARE
              message number;
              aoID number;
              wRsltdo number;
              CURSOR c_wrDetail IS
              SELECT * FROM CHSPDETAIL WHERE ID = :NEW.ID;
              BEGIN
              TABLETEST.crMsg_CreateData_OAI_V1(message, aoID);
              wRsltdo := TABLETEST.cr_CHSPMaster_DocINPUT(:new.id, :new.name, message, aoid);
              FOR wrDetail IN c_wrDetail LOOP
              wRsltdo := TABLETEST.cr_CHSPDetail_CHSPDetail(
              wrDetail.ID,
              wrDetail.NAME,
              message,
              aoID);
              END LOOP;

              TABLETEST.inv_CreateData_OAI_V1(message,'CHSP');
              --
              --
              END;

              When I executed the publish adapter show me an error like this (this error is in spanish)

              **Error: Se ha producido un error desconocido en db_bridge_reader_1 al crear o e
              nviar el mensaje 23. Aseg·rese de que la aplicaci¾n crea siempre los mensajes co
              rrectamente. El mensaje no se ha enviado y se suprimirß de la base de datos. Rev
              ise los archivos log para obtener informaci¾n exacta sobre el error.
              oracle.oai.agent.common.AgentRuntimeException: El atributo especificado (CHSPDet
              ail) no existe en el tipo de dato (TableTest_CreateData_IN_OAI_V1_R:OAI/V1). Uti
              lice iStudio para comprobar las asignaciones. Puede que tenga asignaciones que u
              tilizan este atributo aunque lo haya suprimido del tipo de dato. TambiÚn puede q
              ue haya cambiado la informaci¾n de iStudio que el adaptador no haya leÝdo todavÝ
              a porque tenga activado el almacenamiento de metadatos en cachÚ. En ese caso, de
              berß transferir los metadatos desde iStudio a este adaptador.
              at oracle.oai.agent.common.AttributeObjectImpl.getAttributeInfo(Attribut
              eObjectImpl.java:76)
              at oracle.oai.agent.adapter.database.DBMessageReader.readDBMessageUsingN
              ewOaiSchema(DBMessageReader.java:581)
              at oracle.oai.agent.adapter.database.DBMessageReader.sendDBMessage(DBMes
              sageReader.java:651)
              at oracle.oai.agent.adapter.database.DBMessageReader.run(DBMessageReader
              .java:245)
              at java.lang.Thread.run(Thread.java:484)



              The error says something like the array does not transfer to the adapter metadata.

              Any idea what is happening.

              My structure is master detail type
              with 3 atributes one of them is array

              Regards

              • 4. Re: How Use Arrays on 9iAS Integration
                349404
                Ricardo,

                Usually this error indicates that the physical message published to InterConnect does not conform to the logical data structure (application view) of the published event that you have modeled in iStudio. How did you model the master/detail relationship in the the event's application view?

                If you modeled both, CHSPMaster and CHSPDetail, as elements of the CreateData event

                CreateData
                ..|
                .(+) CHSPMaster
                .(+) CHSPDetail

                the PL/SQL code you sent along with your note should work.

                If you modeled only CHSPMaster as an element of CreateData and then CHSPDetail as an elements of CHSPMaster

                CreateData
                ..|
                .(+) CHSPMaster
                ....|
                ...(+) CHSPDetail

                your PL/SQL code should look as follows:

                DECLARE

                l_moid NUMBER;
                l_aoid NUMBER;
                l_master_aoid NUMBER;
                l_detail_aoid NUMBER;

                CURSOR c_wrDetail IS
                SELECT *
                FROM chspdetail
                WHERE id = :new.id;

                BEGIN

                TABLETEST.crMsg_CreateData_OAI_V1(l_moid, l_aoid);

                l_master_aoid := TABLETEST.cr_CHSPMaster_DocINPUT(
                :new.id,
                :new.name,
                l_moid,
                l_aoid
                );

                FOR wrDetail IN c_wrDetail LOOP

                l_detail_rsltdid := TABLETEST.cr_CHSPDetail_CHSPDetail(
                wrDetail.ID,
                wrDetail.NAME,
                l_moid,
                l_master_aoid
                );

                END LOOP;

                TABLETEST.inv_CreateData_OAI_V1(l_moid,'CHSP');

                END;

                However, why don't you send me a dump of your metadata repsitory and I will take a look at it. You can export the repository into a dump file using the $ORACLE_HOME/oai/9.0.4/repository/oaiexport utility. Please email the dump to markus.spohn@oracle.com.

                Thanks,
                Markus

                ____________________________________________

                Markus Spohn
                Principal Product Manager
                OracleAS Integration

                Oracle Corporation
                Worldwide Headquarters
                500 Oracle Parkway m/s 1op627
                Redwood Shores, CA 94065
                U.S.A.

                e-Mail: markus.spohn@oracle.com
                ____________________________________________
                • 5. Re: How Use Arrays on 9iAS Integration
                  137783
                  Hi Markus,
                  I really really apreciate your help because Oracle Support is not a good option for Help Iam very disgust with them.
                  By the way I will send you our test case But we actually use 9iAS R2 with Interconnect 9022 with 9i 9.0.1.4 Database.

                  Can I send you the information to your email??

                  Regards

                  And again thank you very much
                  • 6. Re: How Use Arrays on 9iAS Integration
                    349404
                    Hi Ricardo,

                    Sure, no problem. InterConnect 9.0.2.2 is fine as well. Please send everything to markus.spohn@oracle.com and I'll take a look at it.

                    Thanks,
                    Markus
                    ____________________________________________

                    Markus Spohn
                    Principal Product Manager
                    OracleAS Integration

                    Oracle Corporation
                    Worldwide Headquarters
                    500 Oracle Parkway m/s 1op627
                    Redwood Shores, CA 94065
                    U.S.A.

                    e-Mail: markus.spohn@oracle.com
                    ____________________________________________
                    • 7. Re: How Use Arrays on 9iAS Integration
                      137783
                      Markus
                      The Problem with arrays was resolved with your advice
                      Regards