XI/PI: jdbc receiver using stored procedure with arrays

[yulimar_monasterio2]

The company needs an interface to search for header data and detail to a legacy system.

This interface from ERP to legacy system is synchronous and uses stored procedure.

The definition of the stored procedure is as follows:

PROCEDURE

Generar_Detalle_Vtas_Pagadas

(p_cvendedor IN bdc_vendedores.cvendedor%TYPE,

p_fdesde IN DATE,

p_fhasta IN DATE,

v_encabezado_ct OUT encabezado_ct,

v_detalle_vtas_pagadas_ct OUT detalle_vtas_pagadas_ct,

err_num OUT NUMBER

Data types used in stored procedure:

TYPE encabezado_ct AS OBJECT

( CREGION NUMBER(22),

XREGION VARCHAR2(50),

CMERCADO NUMBER(22),

XMERCADO VARCHAR2(50),

CTVENDEDOR VARCHAR2(5),

XCTVENDEDOR VARCHAR2(50),

XDENOMINACION VARCHAR2(15))

TYPE detalle_vtas_pagadas_ct AS OBJECT

( CITEM VARCHAR2(10),

XIDENTIFICADOR VARCHAR2(15),

XCONTRATO VARCHAR2(15),

XVALOR VARCHAR2(30),

CCUENTA VARCHAR2(15),

FACTIVACION DATE,

XDOMINIO VARCHAR2(30),

CCED VARCHAR2(20),

XCLIENTE VARCHAR2(161),

CCPO VARCHAR2(60) )

As shown, this has three input parameters varchar and date respectively and has three output parameters, two of which are defined with a data

type such as table and the other number.

The problem is to define the signature of the stored procedure in XI when defining the data type request, the message type and mapping system

legacy because the data type is not supported (tables: detalle_vtas_pagadas_ct,encabezado_ct )

[yulimar_monasterio2]

How do I define the stored procedure data type in the request?

[Former Member]

HI ,

please refer to http://help.sap.com/saphelp_nwpi711/helpdata/en/43/9519abb1146353e10000000a11466f/frameset.htm。;

[yulimar_monasterio2]

thanks for your reply, but I defined my request data type of these forms and the problem arises in defining the output parameters such as table arrangements.

My question is how I can define an output parameter that contains a data type such as table?

FORM 1:

I defined v_detalle_vtas_pagadas_ct and v_encabezado_ct varchar type, because but these parameters are type arrays:

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_VentasPagadasVYC xmlns:ns0="urn:VentasPagadas:VE_ALTASDEDCALIDAD_SD_VYC">

<VtasPagadas><Generar_Detalle_Vtas_Pagadas action="EXECUTE">

<table>vyc.Vyc_Pack_Reportes_Sap.Generar_Detalle_Vtas_Pagadas</table>

<p_cvendedor type="VARCHAR"></p_cvendedor>

<p_fdesde type="DATE"></p_fdesde>

<p_fhasta type="DATE"></p_fhasta>

<v_encabezado_ct isOutput="1" type="varchar">1</v_encabezado_ct>

<v_detalle_vtas_pagadas_ct isOutput="1" type="varchar">X</v_detalle_vtas_pagadas_ct>

</Generar_Detalle_Vtas_Pagadas>

</VtasPagadas></ns0:MT_VentasPagadasVYC>

thanks for your help.

Edited by: ymonasterio on Mar 31, 2010 4:46 PM

[yulimar_monasterio2]

FORM 2:

defined as an array, but the error tells me that we need to define the attribute either input or output

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_VtasPagadas_VYC xmlns:ns0="urn:VentasPagadas:VE_ALTASDEDCALIDAD_SD_VYC">

<VtasPagadasRequest><Generar_Detalle_Vtas_Pagadas action="EXECUTE">

<table>vyc.Vyc_Pack_Reportes_Sap.Generar_Detalle_Vtas_Pagadas</table>

<p_cvendedor type="VARCHAR"></p_cvendedor>

<p_fdesde type="DATE"></p_fdesde>

<p_fhasta type="DATE"></p_fhasta>

<v_encabezado_ct>

<CREGION isOutput="1" type="NUMERIC">X</CREGION>

<XREGION isOutput="1" type="VARCHAR">X</XREGION>

<CMERCADO isOutput="1" type="NUMERIC">X</CMERCADO>

<XMERCADO isOutput="1" type="VARCHAR">X</XMERCADO>

<CTVENDEDOR isOutput="1" type="VARCHAR">X</CTVENDEDOR>

<XCTVENDEDOR isOutput="1" type="VARCHAR">X</XCTVENDEDOR>

<XDENOMINACION isOutput="1" type="VARCHAR">X</XDENOMINACION>

</v_encabezado_ct>

<v_detalle_vtas_pagadas_ct>

<CITEM isOutput="1" type="VARCHAR">X</CITEM>

<XIDENTIFICADOR isOutput="1" type="VARCHAR">X</XIDENTIFICADOR>

<XCONTRATO isOutput="1" type="VARCHAR">X</XCONTRATO>

<XVALOR type="VARCHAR">X</XVALOR>

<CCUENTA isOutput="1" type="VARCHAR">X</CCUENTA>

<FACTIVACION isOutput="1" type="DATE">X</FACTIVACION>

<ICTA isOutput="1" type="VARCHAR">X</ICTA>

<CCED isOutput="1" type="VARCHAR">X</CCED>

<XCLIENTE isOutput="1" type="VARCHAR">X</XCLIENTE>

<CCPO isOutput="1" type="VARCHAR">X</CCPO>

</v_detalle_vtas_pagadas_ct>

<err_num isOutput="1" type="NUMERIC">X</err_num>

</Generar_Detalle_Vtas_Pagadas>

</VtasPagadasRequest></ns0:MT_VtasPagadas_VYC>

Edited by: ymonasterio on Mar 31, 2010 4:48 PM

[yulimar_monasterio2]

so how I can define in the request data type v_detalle_vtas_pagadas_ct and v_encabezado_ct parameters?

En SQL estan definidos asi:

v_encabezado_ct OUT encabezado_ct,

v_detalle_vtas_pagadas_ct OUT detalle_vtas_pagadas_ct,

TYPE encabezado_ct AS OBJECT

( CREGION NUMBER(22),

XREGION VARCHAR2(50),

CMERCADO NUMBER(22),

XMERCADO VARCHAR2(50),

CTVENDEDOR VARCHAR2(5),

XCTVENDEDOR VARCHAR2(50),

XDENOMINACION VARCHAR2(15))

TYPE detalle_vtas_pagadas_ct AS OBJECT

( CITEM VARCHAR2(10),

XIDENTIFICADOR VARCHAR2(15),

XCONTRATO VARCHAR2(15),

XVALOR VARCHAR2(30),

CCUENTA VARCHAR2(15),

FACTIVACION DATE,

XDOMINIO VARCHAR2(30),

CCED VARCHAR2(20),

XCLIENTE VARCHAR2(161),

CCPO VARCHAR2(60) )

[Former Member]

Hi Mona,

Did you able to call the stored procedure with input array from JDBC receiver/sender adapter ?

I have the same requirement. Please respond.

Is this possible ?

Thanks

[Former Member]

Hi ymonasterio,

I also have the same requirement to send data as arrays to stored procedure.

Please let me know if i create the datatype in the same way as you created will it work?Are you able to call the stored procedure succesfully?

Please help me.

Thanks,

Hemanth.

[Former Member]

Define the data type as mentioned below:-

<StatementName> -

---

Make occurance to 0 to unbounded and map it to Root node of Source structure.

<storedProcedureName action=u201D EXECUTEu201D>

<table>realStoredProcedureeName</table>

<param1 [isInput=u201Dtrueu201D] [isOutput=true] type=SQLDatatype>val1</param1>

</storedProcedureName >

</StatementName>

This will accept multiple values

chirag

[yulimar_monasterio2]

How can I define the data type of arrays?