2 Replies Latest reply on Jan 18, 2017 9:30 PM by mpkk

    PL/SQL REST Webservice not able to use RECORD type

    mpkk

      Hello Friends

       

      While I am invoking a custom PL/SQL REST Webservice with RECORD Type/Table Type along with other other data type parameters, only RECORD TYPE/TABLE Type paramter values are not getting parsed into procedure but other parameters are getting passed and insereted into custom table, where as RECORD TYPE/Table Type parameter values are getting inserted as NULL values, please help me whether I am missing any step.

       

      1. Created table( HDR )  -- Success
      2. Created Package Spec and Body  -- Success
      3. Deployed the service – Success
      4. Invoke Services          -- Success
      5.    After Invoking service verified the records in table  --- Null values  for record type/Table Type variable value columns

      CREATE TABLE flk_order_hdr

        (

      p_api_version_number        NUMBER,

      p_init_msg_list             VARCHAR2(10),

      p_return_values             VARCHAR2(10),

      p_action_commit        VARCHAR2(10),   

      operation VARCHAR2(100),

      order_type_id NUMBER,

      org_id NUMBER,

      sold_to_org_id NUMBER,

      ship_to_org_id NUMBER,

      invoice_to_org_id NUMBER,

      order_source_id NUMBER,

      price_list_id NUMBER,

      cust_po_number VARCHAR2(100),

      attribute1 VARCHAR2(10),

      request_date DATE,

      freight_terms_code VARCHAR2(100),

      shipping_method_code VARCHAR2(100)

          );

       

      CREATE TABLE flk_order_line

         (  line_number NUMBER,

      INVENTORY_ITEM_ID NUMBER,

      LINE_TYPE_ID    NUMBER,

      ORDERED_QUANTITY    NUMBER,

      OPERATION        VARCHAR2(100)

        );

       

       

      CREATE OR REPLACE PACKAGE BODY flkont_order_creation_pkg IS

          PROCEDURE flkont_order_creation_prc (

      p_api_version_number                              IN  NUMBER

            , p_init_msg_list                                   IN  VARCHAR2

            , p_return_values                                   IN  VARCHAR2

            , p_action_commit                      IN  VARCHAR2

            , p_header_rec                      IN  oe_order_pub.header_rec_type

      ) IS

      lr_order                oe_order_pub.header_rec_type;

      BEGIN

      lr_order    := p_header_rec;

        INSERT INTO  flk_order_hdr

          ( operation        ,

      order_type_id    ,

      org_id        ,

            sold_to_org_id    ,

      ship_to_org_id    ,

      invoice_to_org_id    ,

      order_source_id    ,

      price_list_id    ,

      cust_po_number    ,

      attribute1    ,   

      request_date    ,

      freight_terms_code, 

            shipping_method_code,

      p_api_version_number,

      p_init_msg_list,

      p_return_values,

      p_action_commit

           

          )

          VALUES

           (lr_order.operation,

            lr_order.order_type_id,

            lr_order.org_id,

            lr_order.sold_to_org_id,

            lr_order.ship_to_org_id,

            lr_order.invoice_to_org_id,

            lr_order.order_source_id,

            lr_order.price_list_id,

            lr_order.cust_po_number,

            lr_order.attribute1,

            lr_order.request_date,

            lr_order.freight_terms_code,

            lr_order.shipping_method_code,

      p_api_version_number,

      p_init_msg_list,

      p_return_values,

      p_action_commit

           );

         

        COMMIT;

         

       

      END flkont_order_creation_prc;

       

       

      END flkont_order_creation_pkg;

      /

       

      Payload

          <SALES_ORDER_INPUT>

              <RESTHeader>

                  <Responsibility>FUS_FEL_ONT_SUPERUSER</Responsibility>

                  <RespApplication>ONT</RespApplication>

                  <SecurityGroup>STANDARD</SecurityGroup>

                  <NLSLanguage>AMERICAN</NLSLanguage>

                  <Org_Id>122</Org_Id>

              </RESTHeader>

              <InputParameters>

                  <P_API_VERSION_NUMBER>1.0</P_API_VERSION_NUMBER>

                  <P_INIT_MSG_LIST>T</P_INIT_MSG_LIST>

                  <P_RETURN_VALUES>T</P_RETURN_VALUES>

                  <P_ACTION_COMMIT>T</P_ACTION_COMMIT>

                  <P_HEADER_REC>

      <operation>CREATE</operation>

      <order_type_id>5788</order_type_id>

      <org_id>122</org_id>

      <sold_to_org_id>111341633</sold_to_org_id>

      <ship_to_org_id>104242094</ship_to_org_id>

                      <invoice_to_org_id>104242093</invoice_to_org_id>

      <order_source_id>1081</order_source_id>

      <price_list_id>9142</price_list_id>

      <cust_po_number>TEST3D1</cust_po_number>

      <attribute1>US</attribute1>

      <request_date>17-JAN-2017</request_date>

      <freight_terms_code>UPSG.STD</freight_terms_code>

      <shipping_method_code>000001_UPSG.STD_G_GENERIC</shipping_method_code>

                  </P_HEADER_REC>

              </InputParameters>

          </SALES_ORDER_INPUT>

       

       

      After invoking service I am seeing values in 4 columns which passed from procedure as in patermers, rest of the column in table passed as RECORD type, but these columns never got inserted, need your inputs to resolve this issue.