Autocreate Deliveries Through API WSH_DELIVERY_DETAILS_PUB.AUTOCREATE_DELIVER

Below script will help you to Autocreate Delivery and assign them to Delivery Details using the API WSH_DELIVERY_DETAILS_PUB.AUTOCREATE_DELIVERIES. This generally create a new delivery in WSH_NEW_DELIVERIES table and assign it to the delivery details in WSH_DELIVERY_ASSIGNMENTS Table. 

We have tested this script in Oracle Apps R12.1.1 instance.

SCRIPT:

set serveroutput on;

Declare

x_return_status VARCHAR2 (2);

x_msg_count     NUMBER;

x_msg_data      VARCHAR2 (2000);

p_line_rows     WSH_UTIL_CORE.ID_TAB_TYPE;

x_del_rows      WSH_UTIL_CORE.ID_TAB_TYPE;

p_count         NUMBER;

v_context       VARCHAR2(100);

FUNCTION set_context( i_user_name    IN  VARCHAR2

                     ,i_resp_name    IN  VARCHAR2

                     ,i_org_id       IN  NUMBER)

RETURN VARCHAR2

IS

v_user_id      NUMBER;

v_resp_id      NUMBER;

v_resp_appl_id NUMBER;

v_lang         VARCHAR2(100);

v_session_lang VARCHAR2(100):=fnd_global.current_language;

v_return       VARCHAR2(10):='T';

v_nls_lang     VARCHAR2(100);

v_org_id       NUMBER:=i_org_id;

/* Cursor to get the user id information based on the input user name */

CURSOR cur_user

IS

    SELECT     user_id

    FROM       fnd_user

    WHERE      user_name  =  i_user_name;

/* Cursor to get the responsibility information */

CURSOR cur_resp

IS

    SELECT     responsibility_id

              ,application_id

              ,language

    FROM       fnd_responsibility_tl

    WHERE      responsibility_name  =  i_resp_name;

/* Cursor to get the nls language information for setting the language context */

CURSOR cur_lang(p_lang_code VARCHAR2)

IS

    SELECT    nls_language

    FROM      fnd_languages

    WHERE     language_code  = p_lang_code;

BEGIN

    /* To get the user id details */

    OPEN cur_user;

    FETCH cur_user INTO v_user_id;

    IF cur_user%NOTFOUND

    THEN

        v_return:='F';

       

    END IF;

    CLOSE cur_user;

    /* To get the responsibility and responsibility application id */

    OPEN cur_resp;

    FETCH cur_resp INTO v_resp_id, v_resp_appl_id,v_lang;

    IF cur_resp%NOTFOUND

    THEN

        v_return:='F';

       

    END IF;

    CLOSE cur_resp;

   

    DBMS_OUTPUT.PUT_LINE (v_user_id||' ' ||v_resp_id|| ' ' ||v_resp_appl_id);

    /* Setting the oracle applications context for the particular session */

    fnd_global.apps_initialize ( user_id      => v_user_id

                                ,resp_id      => v_resp_id

                                ,resp_appl_id => v_resp_appl_id);

    /* Setting the org context for the particular session */

    mo_global.set_policy_context('S',v_org_id);

    /* setting the nls context for the particular session */

    IF v_session_lang != v_lang

    THEN

        OPEN cur_lang(v_lang);

        FETCH cur_lang INTO v_nls_lang;

        CLOSE cur_lang;

        fnd_global.set_nls_context(v_nls_lang);

    END IF; --IF v_session_lang != v_lang

    RETURN v_return;

EXCEPTION

WHEN OTHERS THEN

    RETURN 'F';

END set_context;

BEGIN

DBMS_OUTPUT.PUT_LINE('1');

--1. Set applications context if not already set.

v_context := set_context('&USERID','&RESPONSIBILITY_Id',93);

IF v_context = 'F'

    THEN

        DBMS_OUTPUT.PUT_LINE('Error while setting the context');       

    END IF;

DBMS_OUTPUT.PUT_LINE('2');

mo_global.init ('ONT');

p_line_rows (1) :=218027 ; -- Delivery_detail_id from WSH_DELIVERY_DETAILS

-- API Call for Auto Create Deliveries

WSH_DELIVERY_DETAILS_PUB.AUTOCREATE_DELIVERIES

        (

              p_api_version_number => 1.0,

              p_init_msg_list      => apps.fnd_api.g_true,

              p_commit             => apps.fnd_api.g_true,

              x_return_status      => x_return_status,

              x_msg_count          => x_msg_count,

              x_msg_data           => x_msg_data,

              p_line_rows          => p_line_rows,

              x_del_rows           => x_del_rows

            );

IF x_return_status = 'S' THEN

   DBMS_OUTPUT.put_line('Delivery Got Sucessfully created and assigned to delivery details '||x_del_rows(1));

ELSE

   DBMS_OUTPUT.put_line('Message count ' || x_msg_count);

   IF x_msg_count = 1 THEN

      DBMS_OUTPUT.put_line('x_msg_data '||x_msg_data);

   ELSIF x_msg_count > 1 THEN

   LOOP

      p_count := p_count+1;

      x_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);

      IF x_msg_data IS NULL THEN

      EXIT;

      END IF;

      DBMS_OUTPUT.put_line('Message' || p_count ||'---'||x_msg_data);

      END LOOP;

      END IF;

END IF;

END;