How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Michael Hannigan Your Own Question

Michael Hannigan
Michael Hannigan, Consultant
Category: Programming
Satisfied Customers: 11421
Experience:  25+ Years Experience in Field. Multi Platform Developer.
23511658
Type Your Programming Question Here...
Michael Hannigan is online now
A new question is answered every 9 seconds

I'm programming a small interface to enter and read input

Customer Question

I'm programming a small interface to enter and read input and output from an API in the Oracle EBS R12.
To be specific, I need help with the procedures in the WSH_DELIVERIES, WSH_PICK_LIST and WSH_DELIVERIES_DETAILS Packages.
In short, I've already made a PL/SQL procedure that inputs and reads information from these procedures and were working fine up to 2 weeks ago.
As far as I can tell there has been no change in the main Database or Database Server but right now it seems not to work.
I get a SUCCESS response but when I check the document, there are no changes.
Thanks.
Submitted: 1 year ago.
Category: Programming
Expert:  Michael Hannigan replied 1 year ago.

Hello. My name is***** can help you with your question.

In order to use the auto create, there is a specific process that will need to follow. Please take a look at the following page which has an example along with detailed explanation:

http://bitli.es/hpoIE0

Customer: replied 1 year ago.

Hi,

I've tried this and used the information in the procedures but it is not working.

However, the Launch Pick-Release procedure and Ship/Confirm are working using the Shipping Transaction Interface.

Below is the code if you can take a look, if there's something wrong:

===============

create or replace PROCEDURE "isar_GPFPickSO" ( p_header_id IN NUMBER,
p_msg_data OUT VARCHAR2)
IS

x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
p_api_version_number NUMBER := 1.0;
init_msg_list VARCHAR2 (200);
x_msg_details VARCHAR2 (3000);
x_msg_data VARCHAR2 (3000);
x_msg_summary VARCHAR2 (3000);
p_line_rows wsh_util_core.id_tab_type;
x_del_rows wsh_util_core.id_tab_type;
l_ship_method_code VARCHAR2 (100);
l_commit VARCHAR2 (30);
p_delivery_id NUMBER;
p_delivery_name VARCHAR2 (30);
x_trip_id VARCHAR2 (30);
x_trip_name VARCHAR2 (30);
exep_api EXCEPTION;
l_picked_flag VARCHAR2 (10);
l_return_status VARCHAR2 (1000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
v_order_number VARCHAR2(20);
v_count NUMBER:=0;
CURSOR c_ord_details
IS
SELECT oha.order_number sales_order,
oha.org_id,
ola.line_number,
ola.shipment_number,
ola.flow_status_code,
wdd.delivery_detail_id,
wdd.inv_interfaced_flag,
wdd.oe_interfaced_flag,
wdd.released_status,
ola.ordered_item
FROM apps.oe_order_headers_all oha,
apps.oe_order_lines_all ola,
apps.wsh_delivery_details wdd
WHERE oha.header_id = ola.header_id
AND oha.org_id = ola.org_id
AND oha.header_id = wdd.source_header_id
AND ola.line_id = wdd.source_line_id
AND oha.booked_flag = 'Y'
AND NVL (ola.cancelled_flag, 'N') <> 'Y'
AND wdd.released_status IN ('R', 'B')
AND oha.attribute17 Is Not NUll
AND ola.flow_status_code = 'AWAITING_SHIPPING'
AND oha.header_id = p_header_id ;

BEGIN

-- Initializing the Applications

SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = 'SG.GPF';

SELECT responsibility_id, application_id
INTO l_resp_id, l_appl_id
FROM fnd_responsibility_vl
--WHERE responsibility_name = 'Order Management Super User';
--WHERE responsibility_name = 'Sales Order Management Manager';
WHERE responsibility_name = 'Salesperson Services (Full Access)';

fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

-------------================================================

x_return_status := wsh_util_core.g_ret_sts_success;
--i := 0;
v_count := 0 ;
FOR i IN c_ord_details
LOOP
v_count := v_count + 1 ;
v_order_number := i.sales_order ;
mo_global.set_policy_context ('S', i.org_id);
mo_global.init ('ONT');

p_line_rows (v_count) := i.delivery_detail_id;
END LOOP;
-- API Call for Auto Create Deliveries

DBMS_OUTPUT.put_line ( 'Calling WSH_DELIVERY_DETAILS_PUB to Perform AutoCreate Delivery');
DBMS_OUTPUT.put_line ( '====================================================');

wsh_delivery_details_pub.autocreate_deliveries (
p_api_version_number => 1.0,
p_init_msg_list => apps.fnd_api.g_true,
p_commit => l_commit,
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);

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

IF (x_return_status <> wsh_util_core.g_ret_sts_success)
THEN
DBMS_OUTPUT.put_line ( 'Failed to Auto create delivery for Sales Order');
p_msg_data := p_msg_data ||'
Failed to Auto create delivery for Sales Order'|| Chr(13);
RAISE exep_api;
ELSE
DBMS_OUTPUT.put_line ( '
Auto Create Delivery Action has successfully completed for Order : '||v_order_number);
p_msg_data := p_msg_data || 'Auto Create Delivery Action has successfully completed for Order : '||v_order_number|| Chr(13);
p_msg_data := p_msg_data ||'
'|| Chr(13);
p_msg_data := p_msg_data ||' ';
END IF;

-- Pick release.
p_delivery_id := x_del_rows (1);
p_delivery_name := TO_CHAR (x_del_rows (1));

DBMS_OUTPUT.put_line ('Calling WSH_DELIVERIS_PUB to Perform Pick Release of SO');
DBMS_OUTPUT.put_line ('=============================================');
-- API Call for Pick Release

wsh_deliveries_pub.delivery_action (
p_api_version_number => 1.0,
p_init_msg_list => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_action_code => 'PICK-RELEASE',
p_delivery_id => p_delivery_id,
p_delivery_name => p_delivery_name,
p_asg_trip_id => NULL,
p_asg_trip_name => NULL,
p_asg_pickup_stop_id => NULL,
p_asg_pickup_loc_id => NULL,
p_asg_pickup_stop_seq => NULL,
p_asg_pickup_loc_code => NULL,
p_asg_pickup_arr_date => NULL,
p_asg_pickup_dep_date => NULL,
p_asg_dropoff_stop_id => NULL,
p_asg_dropoff_loc_id => NULL,
p_asg_dropoff_stop_seq => NULL,
p_asg_dropoff_loc_code => NULL,
p_asg_dropoff_arr_date => NULL,
p_asg_dropoff_dep_date => NULL,
p_sc_action_flag => 'S',
p_sc_intransit_flag => 'N',
p_sc_close_trip_flag => 'N',
p_sc_create_bol_flag => 'N',
p_sc_stage_del_flag => 'Y',
p_sc_trip_ship_method => NULL,
p_sc_actual_dep_date => NULL,
p_sc_report_set_id => NULL,
p_sc_report_set_name => NULL,
p_sc_defer_interface_flag => 'Y',
p_sc_send_945_flag => NULL,
p_sc_rule_id => NULL,
p_sc_rule_name => NULL,
p_wv_override_flag => 'N',
x_trip_id => x_trip_id,
x_trip_name => x_trip_name);

DBMS_OUTPUT.put_line (x_return_status);
DBMS_OUTPUT.put_line (x_msg_count);
DBMS_OUTPUT.put_line (x_msg_data);

IF (x_return_status <> wsh_util_core.g_ret_sts_success)
THEN
DBMS_OUTPUT.put_line ('Failed to Pick Release the sales order');
p_msg_data := p_msg_data || '
Failed to Pick Release the sales Order : '||v_order_number|| Chr(13);

RAISE exep_api;
ELSE
DBMS_OUTPUT.put_line ('Sales Order has successfully Pick Released');
DBMS_OUTPUT.put_line ('==============================');
p_msg_data := p_msg_data || '
Sales Order has successfully Pick Released For Order : '||v_order_number|| Chr(13);

END IF;

--for pick confirm
COMMIT;

EXCEPTION
WHEN exep_api
THEN
DBMS_OUTPUT.put_line ('==============');
DBMS_OUTPUT.put_line ('Error Details If Any');
DBMS_OUTPUT.put_line ('==============');

wsh_util_core.get_messages ('Y',
x_msg_summary,
x_msg_details,
x_msg_count);

IF x_msg_count > 1
THEN
x_msg_data := x_msg_summary || x_msg_details;
p_msg_data := p_msg_data || '============== Error ============='|| Chr(13);
p_msg_data := p_msg_data || x_msg_data || Chr(13);
DBMS_OUTPUT.put_line (' x_msg_data exp ' || x_msg_data);
ELSE
x_msg_data := x_msg_summary || x_msg_details;
p_msg_data := p_msg_data || '============== Error ============='|| Chr(13);
p_msg_data := p_msg_data || x_msg_data || Chr(13);
DBMS_OUTPUT.put_line (' x_msg_summary exp '|| x_msg_data);
END IF;

END "isar_GPFPickSO";

================================

Expert:  Michael Hannigan replied 1 year ago.

Should p_commit be

p_commit => apps.fnd_api.g_true,

Expert:  Michael Hannigan replied 1 year ago.

What is the last message you get? For example, it should tell you as it completes each step like "Auto Create Delivery action has successfully..."

Customer: replied 1 year ago.

Hi,

I've tried to change the p_commit as you suggested, either way I get:

"Failed to Auto create delivery for Sales Order ============ Error ==============="

No error code, not even the order No.

Expert:  Michael Hannigan replied 1 year ago.

What is output as x_return_status?

Customer: replied 1 year ago.

"Failed to Auto create delivery for Sales Order ============ Error ==============="

is the output of p_msg_data;


As you can see: p_msg_data := p_msg_data || x_msg_data || Chr(13);

So, this is the complete output...
Expert:  Michael Hannigan replied 1 year ago.

Is there something missing in here?

p_msg_data := p_msg_data ||'
Failed to Auto create delivery for Sales Order'|| Chr(13);
RAISE exep_api;

It looks like something may have been deleted.

Expert:  Michael Hannigan replied 1 year ago.

In any case, the problem would have to originate here:

IF (x_return_status <> wsh_util_core.g_ret_sts_success)

Correct? Because we're assuming success or not success... so it is either really failing, or this statement isn't correct.

Customer: replied 1 year ago.

Hi,

I've considered that the test condition is failing.

But it is failing because the condition is correct.

The WSH_UTIL_CORE.G_RET_STS_SUCCESS is the correct variable upon which we would check the success of failure of the API execution.

Any other possibilities as per your experience as why would the Autocreate API would not function? Better yet, why would it simply drop without any error messages either?

Thanks.

Expert:  Michael Hannigan replied 1 year ago.

What I was thinking was that there wasn't actually an error, which would explain that part of it.

So what does WSH_UTIL_CORE.G_RET_STS_SUCCESS get set to. I believe it should be "S" if it is successful.

Customer: replied 1 year ago.

Hi,

I found out the issue.

The Autocreate procedure in the package is set up so that the Delivery Note of the Sales Order has to be printed so that Delivery process can begin.

It is ironic because in the description of Autocreate procedure in the package it is clearly mentioned "no prerequisites".