, , , , , , , , , ,

Performing Back Order on a SO Line using Wsh_Delivery_Pub API

DECLARE

x_return_status VARCHAR2 (3000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (3000);
–Standard Parameters.
p_api_version_number NUMBER;
init_msg_list VARCHAR2 (30);
x_msg_details VARCHAR2 (3000);
x_msg_summary VARCHAR2 (3000);
p_validation_level NUMBER;
p_commit VARCHAR2 (30);
–Parameters for WSH_DELIVERIES_PUB
p_action_code VARCHAR2 (15);
p_delivery_id NUMBER;
p_delivery_name VARCHAR2 (30);
p_asg_trip_id NUMBER;
p_asg_trip_name VARCHAR2 (30);
p_asg_pickup_stop_id NUMBER;
p_asg_pickup_loc_id NUMBER;
p_asg_pickup_loc_code VARCHAR2 (30);
p_asg_pickup_arr_date DATE;
p_asg_pickup_dep_date DATE;
p_asg_dropoff_stop_id NUMBER;
p_asg_dropoff_loc_id NUMBER;
p_asg_dropoff_loc_code VARCHAR2 (30);
p_asg_dropoff_arr_date DATE;
p_asg_dropoff_dep_date DATE;
p_sc_action_flag VARCHAR2 (10);
p_sc_close_trip_flag VARCHAR2 (10);
p_sc_create_bol_flag VARCHAR2 (10);
p_sc_stage_del_flag VARCHAR2 (10);
p_sc_trip_ship_method VARCHAR2 (30);
p_sc_actual_dep_date VARCHAR2 (30);
p_sc_report_set_id NUMBER;
p_sc_report_set_name VARCHAR2 (60);
p_asg_pickup_stop_seq NUMBER;
p_asg_dropoff_stop_seq NUMBER;
p_sc_send_945_flag VARCHAR2 (60);
p_sc_rule_id NUMBER;
p_sc_rule_name VARCHAR2 (60);
p_wv_override_flag VARCHAR2 (10);
x_trip_id VARCHAR2 (30);
x_trip_name VARCHAR2 (30);
/*Handle exceptions*/
fail_api EXCEPTION;

l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
l_ship_method_code VARCHAR2 (100);

CURSOR c_ord_details
IS

SELECT DISTINCT det.source_header_number sales_order, det.org_id,
det.source_line_number, det.source_header_id,
det.source_line_id, det.source_header_type_name,
det.inventory_item_id, det.requested_quantity,
(SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = det.inventory_item_id
AND organization_id = det.organization_id) ordered_item,
det.organization_id, det.src_requested_quantity,
det.shipped_quantity, del.delivery_id,
del.status_code delivery_status_code,
det.released_status pick_release_status,
det.oe_interfaced_flag, det.inv_interfaced_flag
FROM wsh_delivery_details det,
wsh_delivery_assignments asn,
wsh_new_deliveries del
WHERE 1 = 1
AND det.delivery_detail_id = asn.delivery_detail_id
AND asn.delivery_id = del.delivery_id(+)
AND det.source_header_number = ‘10001059’
AND det.org_id = 308
AND shipped_quantity IS NULL
AND NVL (del.status_code, ‘OP’) <> ‘CL’
AND det.released_status = ‘Y’;

BEGIN

— Initializing the Applications

SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = ‘A42485’;

SELECT responsibility_id, application_id
INTO l_resp_id, l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = ‘Order Management Super User’;

fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

FOR i IN c_ord_details

LOOP

— Mandatory initialization for R12

mo_global.set_policy_context (‘S’, i.org_id);
mo_global.init (‘ONT’);

BEGIN
SELECT shipping_method_code
INTO l_ship_method_code
FROM oe_order_headers_all
WHERE order_number = i.sales_order AND org_id = i.org_id;
EXCEPTION
WHEN OTHERS
THEN
l_ship_method_code := NULL;
END;

/* Initialize return status*/

x_return_status := wsh_util_core.g_ret_sts_success;

p_action_code := ‘CONFIRM’; — The action code
p_delivery_id := i.delivery_id;– The delivery that needs to be confirmed
p_delivery_name := TO_CHAR (i.delivery_id); — The delivery name,
p_sc_action_flag := ‘C’; — Ship entered quantity
p_sc_stage_del_flag := ‘N’; —
p_sc_trip_ship_method := l_ship_method_code; — The ship method code

DBMS_OUTPUT.put_line (‘Calling WSH_DELIVERIES_PUB to Perform Back Order’);
DBMS_OUTPUT.put_line (‘=============================================’);

wsh_deliveries_pub.delivery_action
(p_api_version_number => 1.0,
p_init_msg_list => init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_action_code => p_action_code,
p_delivery_id => i.delivery_id,
p_delivery_name => p_delivery_name,
p_asg_trip_id => p_asg_trip_id,
p_asg_trip_name => p_asg_trip_name,
p_asg_pickup_stop_id => p_asg_pickup_stop_id,
p_asg_pickup_loc_id => p_asg_pickup_loc_id,
p_asg_pickup_stop_seq => p_asg_pickup_stop_seq,
p_asg_pickup_loc_code => p_asg_pickup_loc_code,
p_asg_pickup_arr_date => p_asg_pickup_arr_date,
p_asg_pickup_dep_date => p_asg_pickup_dep_date,
p_asg_dropoff_stop_id => p_asg_dropoff_stop_id,
p_asg_dropoff_loc_id => p_asg_dropoff_loc_id,
p_asg_dropoff_stop_seq => p_asg_dropoff_stop_seq,
p_asg_dropoff_loc_code => p_asg_dropoff_loc_code,
p_asg_dropoff_arr_date => p_asg_dropoff_arr_date,
p_asg_dropoff_dep_date => p_asg_dropoff_dep_date,
p_sc_action_flag => p_sc_action_flag,
p_sc_intransit_flag => ‘N’,
p_sc_close_trip_flag => ‘N’,
p_sc_create_bol_flag => ‘N’,
p_sc_stage_del_flag => p_sc_stage_del_flag,
p_sc_trip_ship_method => p_sc_trip_ship_method,
p_sc_actual_dep_date => p_sc_actual_dep_date,
p_sc_report_set_id => p_sc_report_set_id,
p_sc_report_set_name => p_sc_report_set_name,
p_sc_defer_interface_flag => ‘Y’,
p_sc_send_945_flag => p_sc_send_945_flag,
p_sc_rule_id => p_sc_rule_id,
p_sc_rule_name => p_sc_rule_name,
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 (‘Back Order has not been Completed For SO’);

ROLLBACK;
RAISE fail_api;

ELSE
DBMS_OUTPUT.put_line (‘Back Order Successfully Completed For SO’);
COMMIT;

END IF;
END LOOP;

EXCEPTION

WHEN fail_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;
DBMS_OUTPUT.put_line (x_msg_data);
ELSE
x_msg_data := x_msg_summary x_msg_details;
DBMS_OUTPUT.put_line (x_msg_data);
END IF;

END;

/by
, , , , , , , , ,

Partial Shipments of a Sales Order Using API

For partial shipment of the sales order, we need to call the WSH_DELIVERY_DETAILS_PUB.Update_Shipping_Attributes API to update the corresponding delivery details to ship all/entered quantity in the delivery details.

— OM – Script to Ship Partial Quantities in a SO using WSH_DELIVERY_PUB API —
–===========================================================

DECLARE

p_sales_order NUMBER := 10014445;
p_line_number NUMBER := 1.1;
p_org_id NUMBER := 308;
l_shipped_quantity NUMBER := 5;
p_api_version_number NUMBER := 1.0;
init_msg_list VARCHAR2 (200);
l_commit VARCHAR2 (30);
x_msg_details VARCHAR2 (3000);
x_msg_summary VARCHAR2 (3000);
x_return_status VARCHAR2 (3);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (3000);
p_validation_level NUMBER;
v_errbuf VARCHAR2 (2000);
v_retcode VARCHAR2 (20);
v_released_status wsh_delivery_details.released_status%TYPE;
v_inv_interfaced_flag wsh_delivery_details.inv_interfaced_flag%TYPE;
v_oe_interfaced_flag wsh_delivery_details.oe_interfaced_flag%TYPE;
v_source_code wsh_delivery_details.source_code%TYPE;
v_pending_interface_flag wsh_trip_stops.pending_interface_flag%TYPE;
l_changed_attributes wsh_delivery_details_pub.changedattributetabtype;
l_source_code VARCHAR2 (30) := ‘OE’;
— Parameters for WSH_DELIVERIES_PUB
p_delivery_name VARCHAR2 (30);
p_action_code VARCHAR2 (15);
p_asg_trip_id NUMBER;
p_asg_trip_name VARCHAR2 (30);
p_asg_pickup_stop_id NUMBER;
p_asg_pickup_loc_id NUMBER;
p_asg_pickup_loc_code VARCHAR2 (30);
p_asg_pickup_arr_date DATE;
p_asg_pickup_dep_date DATE;
p_asg_dropoff_stop_id NUMBER;
p_asg_dropoff_loc_id NUMBER;
p_asg_dropoff_loc_code VARCHAR2 (30);
p_asg_dropoff_arr_date DATE;
p_asg_dropoff_dep_date DATE;
p_sc_action_flag VARCHAR2 (10);
p_sc_intransit_flag VARCHAR2 (10);
p_sc_close_trip_flag VARCHAR2 (10);
p_sc_create_bol_flag VARCHAR2 (10);
p_sc_stage_del_flag VARCHAR2 (10);
p_sc_trip_ship_method VARCHAR2 (30);
p_sc_actual_dep_date VARCHAR2 (30);
p_sc_report_set_id NUMBER;
p_sc_report_set_name VARCHAR2 (60);
p_sc_defer_interface_flag VARCHAR2 (60);
p_sc_send_945_flag VARCHAR2 (60);
p_sc_rule_id NUMBER;
p_sc_rule_name VARCHAR2 (60);
p_wv_override_flag VARCHAR2 (10);
p_asg_pickup_stop_seq NUMBER;
p_asg_dropoff_stop_seq NUMBER;
x_trip_id VARCHAR2 (30);
x_trip_name VARCHAR2 (30);
fail_api EXCEPTION;
x_debug_file VARCHAR2 (100);
l_ship_method_code VARCHAR2 (100);
l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;

CURSOR c_ord_details
IS

SELECT DISTINCT det.source_header_number sales_order, det.org_id,
det.source_line_number, det.source_header_id,
det.source_line_id, det.source_header_type_name,
det.inventory_item_id, det.requested_quantity,
det.delivery_detail_id,
(SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id =
det.inventory_item_id
AND organization_id = det.organization_id)
ordered_item,
det.organization_id, det.src_requested_quantity,
det.shipped_quantity, del.delivery_id,
del.status_code delivery_status_code,
det.released_status pick_release_status,
det.oe_interfaced_flag, det.inv_interfaced_flag
FROM wsh_delivery_details det,
wsh_delivery_assignments asn,
wsh_new_deliveries del
WHERE 1 = 1
AND det.delivery_detail_id = asn.delivery_detail_id
AND asn.delivery_id = del.delivery_id(+)
AND det.source_header_number = p_sales_order
AND det.source_line_number = p_line_number
AND det.org_id = p_org_id
AND shipped_quantity IS NULL
AND NVL (del.status_code, ‘OP’) <> ‘CL’
AND det.released_status = ‘Y’;

BEGIN
— Initializing the Applications

SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = ‘A42485’;

SELECT responsibility_id, application_id
INTO l_resp_id, l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = ‘Order Management Super User’;

fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

FOR i IN c_ord_details
LOOP
DBMS_OUTPUT.put_line
(‘Initializing the Application for Shipping Transactions’);
— Mandatory initialization for R12
mo_global.set_policy_context (‘S’, i.org_id);
mo_global.init (‘ONT’);
— Ship Confirming
p_delivery_name := TO_CHAR (i.delivery_id);

DBMS_OUTPUT.put_line
(‘Before Shipping, Calling WSH_DELIVERY_DETAILS_PUB API to Update Shipping Attributes’
);
DBMS_OUTPUT.put_line (‘=============================================’);
l_changed_attributes (1).delivery_detail_id := i.delivery_detail_id;
l_changed_attributes (1).shipped_quantity := l_shipped_quantity;
wsh_delivery_details_pub.update_shipping_attributes
(p_api_version_number => 1.0,
p_init_msg_list => init_msg_list,
p_commit => l_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_changed_attributes => l_changed_attributes,
p_source_code => l_source_code
);

IF (x_return_status <> wsh_util_core.g_ret_sts_success)
THEN
RAISE fail_api;
DBMS_OUTPUT.put_line (‘Failed to Update the Shipping Attributes’);
ELSE
DBMS_OUTPUT.put_line (‘Successfully Updated the Shipping Attributes’);
END IF;

BEGIN
SELECT shipping_method_code
INTO l_ship_method_code
FROM oe_order_headers_all
WHERE order_number = i.sales_order AND org_id = i.org_id;
EXCEPTION
WHEN OTHERS
THEN
l_ship_method_code := NULL;
END;

p_action_code := ‘CONFIRM’; — The action code for ship confirm
p_sc_action_flag := ‘S’; — Ship entered quantity.
p_sc_intransit_flag := ‘Y’;
–In transit flag is set to ‘Y’ closes the pickup stop and sets the delivery in transit.
p_sc_close_trip_flag := ‘Y’; — Close the trip after ship confirm
p_sc_trip_ship_method := l_ship_method_code; — The ship method code
p_sc_defer_interface_flag := ‘Y’;
p_sc_stage_del_flag := ‘Y’;
p_sc_create_bol_flag := ‘N’;
p_wv_override_flag := ‘N’;

— API Call for Ship Confirmation
DBMS_OUTPUT.put_line
(‘Calling WSH_DELIVERIES_PUB to Perform Ship Confirmation’);
DBMS_OUTPUT.put_line (‘=============================================’);

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

IF (x_return_status <> wsh_util_core.g_ret_sts_success)
THEN
DBMS_OUTPUT.put_line
(‘Ship confirm has not been Completed For SO => ‘);
ROLLBACK;
RAISE fail_api;
ELSE
DBMS_OUTPUT.put_line
(‘Ship confirm Successfully Completed For SO => ‘);
COMMIT;

DBMS_OUTPUT.put_line
(‘Checking the Delivery Status after delivery action API Call’);
DBMS_OUTPUT.put_line (‘==========================================’);

SELECT wdd.source_code, wdd.released_status,
wdd.inv_interfaced_flag, wdd.oe_interfaced_flag,
wts.pending_interface_flag
INTO v_source_code, v_released_status,
v_inv_interfaced_flag, v_oe_interfaced_flag,
v_pending_interface_flag
FROM wsh_trips wtr,
wsh_trip_stops wts,
wsh_delivery_legs wlg,
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
wsh_delivery_details wdd
WHERE wtr.trip_id = wts.trip_id
AND wts.stop_id = wlg.pick_up_stop_id
AND wts.pending_interface_flag = ‘Y’
AND wdd.inv_interfaced_flag <> ‘Y’
AND wlg.delivery_id = wnd.delivery_id
AND wnd.delivery_id = wda.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wnd.delivery_id = p_delivery_name
AND wdd.source_line_id = i.source_line_id;

IF ( v_source_code = ‘OE’
AND v_released_status = ‘C’
AND v_inv_interfaced_flag <> ‘Y’
AND v_oe_interfaced_flag <> ‘Y’
AND v_pending_interface_flag = ‘Y’
)
THEN

DBMS_OUTPUT.put_line
(‘The Delivery has been Shipped & the Next Step is – Run Interface’
);
DBMS_OUTPUT.put_line
(‘===========================================’);
— API Call for Submitting Interface Trip Stop

wsh_ship_confirm_actions.interface_all_wrp
(errbuf => v_errbuf,
retcode => v_retcode,
p_mode => ‘ALL’,
p_stop_id => NULL,
p_delivery_id => p_delivery_name,
p_log_level => 0,
p_batch_id => NULL,
p_trip_type => NULL,
p_organization_id => i.organization_id,
p_num_requests => 1,
p_stops_per_batch => 1
);

ELSE
DBMS_OUTPUT.put_line (‘The Delivery has not Shipped Properly’);
END IF;
END IF;
END LOOP;

EXCEPTION
WHEN fail_api
THEN

DBMS_OUTPUT.put_line (‘==============’);
DBMS_OUTPUT.put_line (‘Error Details If Any’);
DBMS_OUTPUT.put_line (‘==============’);

wsh_util_core.get_messages (p_init_msg_list => ‘Y’,
x_summary => x_msg_summary,
x_details => x_msg_details,
x_count => x_msg_count
);

IF x_msg_count > 1
THEN
x_msg_data := x_msg_summary x_msg_details;
DBMS_OUTPUT.put_line (x_msg_data);
ELSE
x_msg_data := x_msg_summary x_msg_details;
DBMS_OUTPUT.put_line (x_msg_data);

END IF;
END;

/by
, , , , , , ,

O2C Flow SQL Query Link

select ooha.order_number,ooha.org_id,
       hca.account_name,
       hp.party_name “Customer Name”,
       hcasab.orig_system_reference      BILL_TO_ORIG_REF,
       hpsb.status                       BILL_TO_STATUS,
       ‘ADDRESS1 – ‘||bill_loc.address1||’,’||CHR(10)|| ‘ADDRESS2 – ‘||bill_loc.address2||’,’||CHR(10)||
       ‘ADDRESS3 – ‘||bill_loc.address3||’,’||CHR(10)|| ‘CITY     – ‘||bill_loc.city||’,’||CHR(10)||
       ‘POSTAL CD- ‘||bill_loc.postal_code||’,’||CHR(10)|| ‘COUNTRY  – ‘|| bill_loc.country  BILL_TO_ADDRESS,
       hcasas.orig_system_reference      SHIP_TO_ORIG_REF,
       hpss.status SHIP_TO_STATUS,
       ‘ADDRESS1 – ‘||ship_loc.address1||’,’||CHR(10)|| ‘ADDRESS2 – ‘||ship_loc.address2||’,’||CHR(10)||
       ‘ADDRESS3 – ‘||ship_loc.address3||’,’||CHR(10)|| ‘CITY     – ‘||ship_loc.city||’,’||CHR(10)||
       ‘POSTAL CD- ‘||ship_loc.postal_code||’,’||CHR(10)|| ‘COUNTRY  – ‘|| ship_loc.country  SHIP_TO_ADDRESS,
       oola.inventory_item_id,oola.ordered_item,
       msib.description item_description,
       wnd.name delivery_number,
       rct.trx_number “AR Invoice Number”,
       acr.receipt_number “AR Receipt Number”,
       gjh.ledger_id,
       gjh.name
  from oe_order_headers_all ooha,
       oe_order_lines_all oola,
       hz_parties hp,
       hz_cust_accounts hca,
       hz_party_sites hpss,
       hz_party_sites hpsb,
       hz_locations bill_loc,
       hz_locations ship_loc,
       hz_cust_acct_sites_all hcasab,
       hz_cust_acct_sites_all hcasas,
       hz_cust_site_uses_all hzsuab,
       hz_cust_site_uses_all hzsuas,
       mtl_system_items_b msib,
       wsh_delivery_details wdd,
       wsh_new_deliveries wnd,
       wsh_delivery_assignments wda,
       ra_customer_trx_all rct,
       ra_customer_trx_lines_all rctl,
       ra_cust_trx_line_gl_dist_all rctld,        
       ar_cash_receipts_all acr,
       xla.xla_transaction_entities xte,
       xla_events xe,
       xla_ae_headers xah,
       xla_ae_lines xal,
       xla_distribution_links xdl,
       gl_import_references gir,
       gl_je_batches gjb,
       gl_je_headers gjh,
       gl_je_lines gjl
 where ooha.order_number = :SalesOrderNumber –Right click :RequitionNumber from Toad Enable Prompt For Substitution Variables
   and ooha.org_id = 204
   and hca.cust_account_id    = ooha.sold_to_org_id
   and hp.party_id            = hca.party_id
   and hpss.party_id            = hca.party_id
   and hpsb.party_id            = hca.party_id
   and bill_loc.location_id = hpss.location_id
   and ship_loc.location_id = hpsb.location_id
   AND hcasas.cust_account_id  = hca.cust_account_id
   AND hcasab.cust_account_id  = hca.cust_account_id
   AND hcasas.party_site_id    = hpss.party_site_id
   AND hcasab.party_site_id    = hpsb.party_site_id
   and hzsuas.cust_acct_site_id = hcasas.cust_acct_site_id
   and hzsuab.cust_acct_site_id = hcasab.cust_acct_site_id
   and hzsuas.site_use_id = ooha.ship_to_org_id
   and hzsuab.site_use_id = ooha.invoice_to_org_id            
   and wda.delivery_id        = wnd.delivery_id(+)
   and wdd.delivery_detail_id = wda.delivery_detail_id
   and wdd.source_header_id   = ooha.header_id
   and wdd.source_line_id     = oola.line_id
   and wdd.organization_id    = msib.organization_id(+)
   and wdd.inventory_item_id  =msib.inventory_item_id(+)
   and rct.interface_header_attribute1 = to_char(ooha.order_number)
   and rct.org_id = ooha.org_id
   and rctl.customer_trx_id = rct.customer_trx_id
   and rctl.sales_order = to_char(ooha.order_number)
   and rctld.customer_trx_id = rct.customer_trx_id
   and rctld.customer_trx_line_id = rctl.customer_trx_line_id
   and acr.receipt_number = ‘G-1001’
   and acr.pay_from_customer = rct.sold_to_customer_id
   and acr.org_id = ooha.org_id
   and acr.customer_site_use_id = rct.bill_to_site_use_id
   and xte.transaction_number = acr.receipt_number
   and xte.entity_code = ‘RECEIPTS’
   and xe.entity_id = xte.entity_id  
   and xah.event_id = xe.event_id
   and xal.ae_header_id = xah.ae_header_id
   and xal.accounting_class_code = ‘CASH’
   and xdl.ae_header_id = xah.ae_header_id
   and xdl.ae_line_num = xal.ae_line_num
   –and xdl.source_distribution_id_num_1
   and gir.reference_5 = xte.entity_id  — Entity Id
   and gir.reference_6 = to_char(xe.event_id) –Event Id
   and gir.reference_7 = to_char (xah.ae_header_id) — AE Header Id
   and gir.gl_sl_link_id = xal.gl_sl_link_id
   and gir.created_by = 1318
   and gjb.je_batch_id = gir.je_batch_id
   and gjh.je_batch_id=gjb.je_batch_id
   and gjh.je_header_id = gir.je_header_id
   and gjl.je_header_id=gjh.je_header_id
   and gjl.je_line_num= gir.je_line_num
/by
, , , ,

Order Management Tables and its details

Order Management Tables. Entered
oe_order_headers_all 1 record created in header table
oe_order_lines_all Lines for particular records
oe_price_adjustments When discount gets applied
oe_order_price_attribs If line has price attributes then populated
oe_order_holds_all If any hold applied for order like credit check etc.
Booked
oe_order_headers_all Booked_flag=Y Order booked.
wsh_delivery_details Released_status Ready to release
Pick Released
wsh_delivery_details Released_status=Y Released to Warehouse (Line has been released to Inventory for processing)
wsh_picking_batches After batch is created for pick release.
mtl_reservations This is only soft reservations. No physical movement of stock
Full Transaction
mtl_material_transactions No records in mtl_material_transactions
mtl_txn_request_headers
mtl_txn_request_lines
wsh_delivery_details Released to warehouse.
wsh_new_deliveries if Auto-Create is Yes then data populated.
wsh_delivery_assignments deliveries get assigned
Pick Confirmed
wsh_delivery_details Released_status=Y Hard Reservations. Picked the stock. Physical movement of stock
Ship Confirmed
wsh_delivery_details Released_status=C Y To C:Shipped ;Delivery Note get printed Delivery assigned to trip stopquantity will be decreased from staged
mtl_material_transactions On the ship confirm form, check Ship all box
wsh_new_deliveries If Defer Interface is checked I.e its deferred then OM & inventory not updated. If Defer Interface is not checked.: Shipped
oe_order_lines_all Shipped_quantity get populated.
wsh_delivery_legs 1 leg is called as 1 trip.1 Pickup & drop up stop for each trip.
oe_order_headers_all If all the lines get shipped then only flag N
Autoinvoice
wsh_delivery_details Released_status=I Need to run workflow background process.
ra_interface_lines_all Data will be populated after wkfw process.
ra_customer_trx_all After running Autoinvoice Master Program for
ra_customer_trx_lines_all specific batch transaction tables get populated
Price Details
qp_list_headers_b To Get Item Price Details.
qp_list_lines
Items On Hand Qty
mtl_onhand_quantities TO check On Hand Qty Items.

Payment Terms
ra_terms Payment terms

AutoMatic Numbering System
ar_system_parametes_all you can chk Automactic Numbering is enabled/disabled.
Customer Information
hz_parties Get Customer information include name,contacts,Address and Phone
hz_party_sites
hz_locations
hz_cust_accounts
hz_cust_account_sites_all
hz_cust_site_uses_all
ra_customers
Document Sequence
fnd_document_sequences Document Sequence Numbers
fnd_doc_sequence_categories
fnd_doc_sequence_assignments
Default rules for Price List
oe_def_attr_def_rules Price List Default Rules
oe_def_attr_condns
ak_object_attributes
End User Details
csi_t_party_details To capture End user Details

Sales Credit Sales Credit Information(How much credit can get)
oe_sales_credits

Attaching Documents
fnd_attached_documents Attched Documents and Text information
fnd_documents_tl
fnd_documents_short_text

Blanket Sales Order
oe_blanket_headers_all Blanket Sales Order Information.
oe_blanket_lines_all

Processing Constraints
oe_pc_assignments Sales order Shipment schedule Processing Constratins
oe_pc_exclusions
Sales Order Holds
oe_hold_definitions Order Hold and Managing Details.
oe_hold_authorizations
oe_hold_sources_all
oe_order_holds_all

Hold Relaese
oe_hold_releases_all Hold released Sales Order.

Credit Chk Details
oe_credit_check_rules To get the Credit Check Againt Customer.
Cancel Orders
oe_order_lines_all Cancel Order Details.
/by
, , , , ,

Order Management Drop Shipment, Functional Setup and flow

 E-commerce site like ebay, ubid.com and amazon.com which are very common place in advance countries like USA, UK & Singapore where people can sell or buy there product. Have you ever think ,what is similar situation in real world , when the the word ‘drop shipment’ comes to your mind. ……a business situation when the retailer or trader has no stock himself, instead giving customer details directly to the seller, and then saler than fills the order and send it to customer directly.How its sounds…
In other words, Drop Shipment is a process where the customer places a purchase order on a company and this company instructs its supplier to directly ship the items to the customer. A Drop Shipment occurs when a customer order is sourced from and delivered by a supplier.Order Management sends information to the Purchasing Application to create that PO, and then when that PO is received (to indicate shipment from the supplier to your customer), the order line is automatically updated to indicate that it was fulfilled. In this process, the company running Order Management is modeled as the company to whom the end customer places the original order.
We need to make sure these are attribute setup Correctly:
Item Attributes
Purchased : Enabled
Purchasable : Enabled
Transactable : Enabled
Stockable : Optional
Reservable : Optional
Inventory Item : Optional
Customer Ordered : Enabled
Customer Order Enabled : Enabled
Internal Ordered : Disabled
OE Transactable : Enabled
Shippable : Optional
And We do set up for Order Source Type as External.
Drop Shipment Setup checklist
Ensure you have created your Order Management Transaction Types and linked your Transaction Types to order and line workflows that support drop shipments.
Ensure the Oracle Workflow Background Engine is running.
Ensure all Drop ship locations you will use to perform drop shipments have the Ship To Site and Receiving Site defined.
Ensure you have defined the Internal Ship To Locations for your drop shipment customers (Oracle Receivables Standard Customer window, Business Purpose Details Tab).
Ensure your standard items have an associated List Price defined within your PO Inventory organization (Oracle Payables Financial Options window, Supplier-Purchasing Tab).
Drop Shipment – Process Steps
Create a Sales Order with line where the line source is External
Book and Schedule the Sales Order
Run Requisition Import Process
Now the line status will be in Awaiting Receipt
Login to the Receiving Organization (Purchasing) who has been setup as an Approver
Run Requisition Import
Create Purchase Order from the Requisition
Approve the PO
Receive the full quantity
Run Auto Invoice
Verify Invoice in Sales Order
Check the details here .

Things not to forget in a Drop Shipment

Release 11i/12 does not support Drop Shipment across operating units.
Blanket PO’s will not used with Drop Shipment , the reason the PO must be created when OM notifies PO that a Drop Ship order has been created.
You can’t cancelled Drop Shipments once Oracle Purchasing obtains the receipt.
Standard Items can be used for Drop Shipment.
In 11i, PTO’s and ATO’s cannot be drop shipped

Difference between “Internal” and “External” Drop-Ship

In Oracle Context External Drop-Shipping means your Oracle Order Management uses purchase orders to outside suppliers that are automatically generated from sales orders for goods supplied directly from the supplier. The “external ” supplier ships the goods directly to the 3rd Party customer and confirms the shipment through the use of an Advanced Shipment Notice(ASN). Note:Oracle uses this ASN to record a receiving transaction into inventory followed by an immediate logical shipping transaction. From these transactions, conveyance of title takes place and the customer can be invoiced and the supplier’s invoice can be processed. where as “Internal” context Drop-Shipping functions in a similar fashion. The key difference is that no inventory transactions take place on the books of the selling operating unit; transfer of ownership of the goods from shipper to seller to customer with the only physical movement of the goods being out of the shipping organization.

Parallel Pick Release

Parallel pick release enables multiple pick release processes to run simultaneously as child processes through the Pick Release SRS Parameters window and Release Sales Orders window through the concurrent mode. Parallel pick release is available when pick releasing from the Tools menu on the Shipping Transactions form through the concurrent mode.
Parallel pick release enables you to set a default number of child processes by defining the profile option: WSH: Number of Pick Release Child Processes.
Ship sets and ship models are processed first during pick release. They are included as part of the child processes.
Parallel pick release can be run from the Shipping Transactions form Tools menu. It cannot, however, be executed from the Shipping Transactions form action Launch Pick Release. Parallel pick release does not run when Pick Release is executed Online.

/by