, , , , , , , , ,

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
, , , ,

R12 Upgrade Vs Re-implement

When a company is planning to adopt R12 from previous versions, the first question arises is whether to Upgrade or Re-implement?

First let us see what is the difference between an upgrade and re-implement.

An R12 upgrade involves running the scripts on a database to transform it into R12 structure. During the upgrade all the data will be moved to the respective  tables in R12.

A re-implementation involves creating a completely new oracle applications installation, doing all the set-ups from scratch and moving the data using data conversions to the new R12 database.

Let us see the advantages and disadvantages of an upgrade and re-implement

Upgrade:

The major advantages of an upgrade process are:

1. The upgrade process has become easy as the technology, tools and the upgrade scripts are significantly improved
2. There will be no effort of data conversions and testing, as the data will be moved during the upgrade process
3. Not many customizations are needed
4. No changes in the functional setups
5. Upgrade is often cheaper as it involves significantly less work for setting up the applications

But an upgrade process requires very significant effort technically and is more challenging. In comparison with re-implementation process an upgrade is risky in technical perspective.

Re-implement:

The main advantage with Re-implementation process is that the technical risk is low when compared with an upgrade.

Disadvantages:

1. Re-implementation process is very extensive as the data conversions and testing is involved.
2. Lot of effort is needed for application configuration.

Organizations generally go for following considerations before going for the re-implementation.

1. There are major changes in the Organization structure or business processes, and the existing application configuration doesn’t fit for the business
2. There are lot of customizations that can be avoided with the new features and functionality
3. The original implementation has disturbed and unusable.
4. There is lot of bad data exists.

/by
, , , , , , , ,

Period-End Process

Period-End process is performed at the end of each period(depends on the organization). It is very important in any organization because if the period is not closed, the accounting for that period can not be closed, which will affect the financial results reporting of the respective period.
One can not close any module without following the order.

The suggested module wise sequence to be followed for Period-End process is as follows.

1. Projects
2. Payables
3. Purchasing
4. Assets 
5. Receivables
6. Inventory
7. General Ledger

/by
, , , ,

Collection Element ‘REV’ has no LOV in R12 Oracle Quality

In Oracle Quality module, the Quality Element ‘Revision’ on Enter Quality Results window has no list of values after R12 upgrade, where it was available in 11i. The users who want to capture the ‘Revision’ for an item on quality plans have no option to select the list of values for Revision. 
According to Oracle, this is expected functionality in R12. The Item revision collection element would be enabled and editable only for a Revision controlled Item. 
The behavior in 11.5.10 was a bug because of which even though the revision field was grayed out, the user could still enter Revision values by invoking the LOV. This was fixed in R12.
The following are the three possible options available to resolve the issue:

Srl# Option Remarks
1 Activate “Revision Control Code” for the items in question:
All items for which Revision needs to be captured in the Quality Collection Plan’s results, using an LOV, we need to active the Revision Control code for the items in inventory. 		The drawback of this option is that all material transactions would mandate the entry of Revision of the item, which makes it very cumbersome for the Inventory and other modules’ users.
Hence, may not be a viable option
2 Go with the new functionality suggested by Oracle This also may not be a good option if users need to capture the Revision of items on QC plans
3 Creation of a new Quality Collection Element that has the capability of accepting Revision of items;
In this option, the “Revision Control” code for items need not be activated. 		This workaround would enable the users to capture Revision of items during the entry of Quality Results.
This option is explained in detail in the subsequent section.
  

Solution for Option 3: Create new collection Element

  1. Create a new collection element with SQL Validation statement to populate the LOV. The following screen shot show the details of the new custom collection element to be created
     Navigation:  Quality Setup Collection Elements

The “select” statement to fetch the item’s revisions within the LOV is as under: 
  
2.   Identify all the QA Plans that use the standard / seeded QA element “Revision” for all the inventory organizations 
3.   Add the new custom QA element to all the QA Plans (stored in the table QA_PLAN_CHARS) identified in step 2. This could be programmatically done using an open API

4.  Using standard APIs, copy the existing data present in the “Revision” column of QA_RESULTS table into the column of newly added “custom revision” of the same table. Once the change is activated, the data captured under the new “custom revision” column would be stored in one of the custom (user-defined) columns titled CHARACTER1 through CHARACTER100. 

 
5.   Finally, once the copying of the Revision data is completed into the QA_RESULTS table (via Oracle APIs), nullify the data in “Revision” column
 
6.   Modification of Reports: All reports (Oracle Reports / SQL / Discoverer based) that were referring to “Revision” column should now be referring to the custom / user-defined columns defined for the new “custom revision” QA collection element.
/by
, , , , , , ,

Accounting entries in Purchasing, Payables and Receivables

When we enter transactions in Purchasing, the accounting entries will get generated with respect to the following accounts.

1. When Creating a receipt:
Inventory Receiving A/C DR
Accrual Account CR

2. At the time of Receiving transactions:
Asset clearing A/c or Inventory valuation a/c DR
Inventory Receiving A/c CR

3. When a Payables invoice matched with PO:
Accrual A/C DR
Liability A/c

4. when asset addition done:
Asset A/c DR
Asset Clearing A/c CR(Asset item)
           (OR)
expense a/c DR
inventory Valuation A/C(Inventory item)

When we enter transactions in Payables, the accounting entries will get generated with respect to the following accounts.

1. Invoice: When we create accounting for an invoice, the accounting will be generated with respect to the following accounts.

Item Expense A/C      DR
Liability  A/C             CR

2. Payment: There are two stages in which the accounting may generate, based on the accounting option that we setup in Payables.
  
> Direct Pay-No Clearance: In this stage, there will be no clearance process separately and the cash account will get credited directly.

Liability  A/C    DR
Cash  A/C         CR

> Pay and Clear: In this stage, the payment and clearance process will have separate accounting entries.

At the time of Payment:

Liability A/C               DR
Cash Clearing A/C      CR

At the time of Clearance:

Cash Clearing A/C    DR
Cash A/C                  CR

 When we enter transactions in Receivables, the accounting entries will get generated with respect to the following accounts.

1. Accounting for invoice:
Receivables A/C DR
Revenue Account Cr

2. Credit memo:
Revenue Dr
Receivables A/C CR

3. Receipts:
Cash A/C DR
Receivables A/c CR

/by