Thursday, August 14, 2014

Create Incomplete PO From ASCP Release Work Bench

Create Incomplete PO From ASCP Release Work Bench 

There is no Standard Function available in ASCP to Release the as Incomplete from ASCP

But a Simple Trigger Can Fix This Requirement

For ASCP:  Add a trigger on table msc_purchase_requisitions_interface (on destination), to change the status before insert on MSC_PO_REQUISITIONS_INTERFACE for each row

end if;

Hope the Information is Helpful 

Wednesday, August 13, 2014

Released From ASCP- Required Completion Date In OPM Batch Is Earlier Than The Batch Start Date

Tuesday, August 12, 2014

Re open Purchase Orders - Oracle ( Mass Cancel)

Finally closed or Canceled PO's cannot be reopened.

This is the standard functionality of the application. Finally Close or Cancel actions are meant to prevent further modifications to the purchase order. This includes billing or receiving. Moreover there is a warning message whenever these actions are performed. The user is warned that this cannot be undone.

If there is a receipt against this PO and the requirement is to return it, it will be necessary to create a miscellaneous issue in Inventory for the concerned quantity and it will adjust the inventory stock.

If there is an invoice against this PO and the requirement is to cancel it, it will be necessary to create a credit memo using the same information entered on the original invoice.

Wednesday, August 6, 2014

Mass Cancel Sales Order API Oracle

Mass Cancel Sales Order API Oracle 

You can use the apps.oe_order_pub.process_order to cancel an order line.

Also note that this code was used to create a new order line.

-- Sales Order Cancel Script to be used in R12

set serveroutput on
 p_api_version_number        NUMBER :=1.0;
 p_init_msg_list             VARCHAR2(10) := FND_API.G_FALSE;
 p_return_values             VARCHAR2(10) := FND_API.G_FALSE;
 p_action_commit             VARCHAR2(10) := FND_API.G_FALSE;
 x_return_status             VARCHAR2(1);
 x_msg_count                 NUMBER;
 x_msg_data                  VARCHAR2(100);
 l_header_rec                OE_ORDER_PUB.Header_Rec_Type;
 l_header_adj_tbl            OE_ORDER_PUB.Header_Adj_Tbl_Type;
 l_header_scr_tbl            OE_ORDER_PUB.Header_Scredit_Tbl_Type;
 l_action_request_tbl        OE_ORDER_PUB.Request_Tbl_Type;

 l_line_tbl                  OE_ORDER_PUB.Line_Tbl_Type;
 l_line_adj_tbl              OE_ORDER_PUB.line_adj_tbl_Type;
 l_line_scredit_tbl          OE_ORDER_PUB.Line_Scredit_Tbl_Type;

 l_return_status             VARCHAR2(1000);
 l_msg_count                 NUMBER;
 l_msg_data                  VARCHAR2(1000);

 p_action_request_tbl        OE_ORDER_PUB.Request_Tbl_Type :=  oe_order_pub.G_MISS_REQUEST_TBL;

 -- out variables need to be defined seperately in R12 as per doc bug 7337251  

 o_header_rec                OE_ORDER_PUB.Header_Rec_Type;
 o_line_tbl                  OE_ORDER_PUB.Line_Tbl_Type;
 o_action_request_tbl        OE_ORDER_PUB.Request_Tbl_Type;
 o_header_adj_tbl            OE_ORDER_PUB.Header_Adj_Tbl_Type;
 o_line_adj_tbl              OE_ORDER_PUB.line_adj_tbl_Type;
 o_header_scr_tbl            OE_ORDER_PUB.Header_Scredit_Tbl_Type;
 o_line_scredit_tbl          OE_ORDER_PUB.Line_Scredit_Tbl_Type;

 o_header_val_rec            OE_ORDER_PUB.Header_Val_Rec_Type;
 o_Header_Adj_val_tbl        OE_ORDER_PUB.Header_Adj_Val_Tbl_Type;
 o_Header_price_Att_tbl      OE_ORDER_PUB.Header_Price_Att_Tbl_Type;
 o_Header_Adj_Att_tbl        OE_ORDER_PUB.Header_Adj_Att_Tbl_Type;
 o_Header_Adj_Assoc_tbl      OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;
 o_Header_Scredit_tbl        OE_ORDER_PUB.Header_Scredit_Tbl_Type;
 o_Header_Scredit_val_tbl    OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type;
 o_line_val_tbl              OE_ORDER_PUB.Line_Val_Tbl_Type;
 o_Line_Adj_val_tbl          OE_ORDER_PUB.Line_Adj_Val_Tbl_Type;
 o_Line_price_Att_tbl        OE_ORDER_PUB.Line_Price_Att_Tbl_Type;
 o_Line_Adj_Att_tbl          OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;
 o_Line_Adj_Assoc_tbl        OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;
 o_Line_Scredit_val_tbl      OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type;
 o_Lot_Serial_tbl            OE_ORDER_PUB.Lot_Serial_Tbl_Type;
 o_Lot_Serial_val_tbl        OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type;

 l_msg_index_out NUMBER(10);
 oe_debug_pub.SetDebugLevel(5); -- Use 5 for the most debuging output, I warn  you its a lot of data
 dbms_output.put_line('START OF NEW DEBUG');

 --This is to cancel an order
 -- Initialize the record to missing

 l_header_rec                   := OE_ORDER_PUB.G_MISS_HEADER_REC;
 l_header_rec.header_id         := 193343;
 l_header_rec.cancelled_flag    := 'Y';
 l_header_rec.change_reason     := 'Not provided';
 l_header_rec.operation         := OE_GLOBALS.G_OPR_UPDATE;

 -- Call To Process Order API

 OE_ORDER_PUB.process_order (
  p_api_version_number       => 1.0
  , p_init_msg_list          => fnd_api.g_false
  , p_return_values          => fnd_api.g_false
  , p_action_commit          => fnd_api.g_false
  , x_return_status          => l_return_status
  , x_msg_count              => l_msg_count
  , x_msg_data               => l_msg_data
  , p_header_rec             => l_header_rec
  , p_line_tbl               => l_line_tbl
  , p_action_request_tbl     => l_action_request_tbl
  , x_header_rec             => o_header_rec
  , x_header_val_rec         => o_header_val_rec
  , x_Header_Adj_tbl         => o_Header_Adj_tbl
  , x_Header_Adj_val_tbl     => o_Header_Adj_val_tbl
  , x_Header_price_Att_tbl   => o_Header_price_Att_tbl
  , x_Header_Adj_Att_tbl     => o_Header_Adj_Att_tbl
  , x_Header_Adj_Assoc_tbl   => o_Header_Adj_Assoc_tbl
  , x_Header_Scredit_tbl     => o_Header_Scredit_tbl 
  , x_Header_Scredit_val_tbl => o_Header_Scredit_val_tbl
  , x_line_tbl               => o_line_tbl
  , x_line_val_tbl           => o_line_val_tbl
  , x_Line_Adj_tbl           => o_Line_Adj_tbl
  , x_Line_Adj_val_tbl       => o_Line_Adj_val_tbl
  , x_Line_price_Att_tbl     => o_Line_price_Att_tbl
  , x_Line_Adj_Att_tbl       => o_Line_Adj_Att_tbl
  , x_Line_Adj_Assoc_tbl     => o_Line_Adj_Assoc_tbl
  , x_Line_Scredit_tbl       => o_Line_Scredit_tbl
  , x_Line_Scredit_val_tbl   => o_Line_Scredit_val_tbl
  , x_Lot_Serial_tbl         => o_Lot_Serial_tbl
  , x_Lot_Serial_val_tbl     => o_Lot_Serial_val_tbl
  , x_action_request_tbl     => o_action_request_tbl

 dbms_output.put_line('OM Debug file: '  ||oe_debug_pub.G_DIR||'/'||oe_debug_pub.G_FILE);
 FOR i IN 1 .. l_msg_count
  Oe_Msg_Pub.get( p_msg_index => i
   , p_encoded => Fnd_Api.G_FALSE
   , p_data => l_msg_data
   , p_msg_index_out => l_msg_index_out
  DBMS_OUTPUT.PUT_LINE('message is: ' || l_msg_data);
  DBMS_OUTPUT.PUT_LINE('message index is: ' || l_msg_index_out);
 -- Check the return status
 IF l_return_status = FND_API.G_RET_STS_SUCCESS
  dbms_output.put_line('Process order Sucess');
-- debug output
 dbms_output.put_line('Debug Output');
 FOR i in 1..OE_DEBUG_PUB.g_debug_count

Saturday, July 26, 2014

Invalid Object MSC_EXA_UTIL

Step if the package MSC_EXA_UTIL is invalid

Error statement in Package body(Line 23)

FUNCTION check_exa_nodes return number IS
vCount number;
 select count(*) into vCount from v$cell;
 if ( vCount > 0) then


This is due to a missing grant.

To allow the package to be valid run the command below ss sys user,


Wednesday, July 16, 2014

hub-and-spoke planning single plan ASCP

Oracle® Advanced Supply Chain Planning enables you to run holistic plans that span long term aggregate planning to short term detailed schedules, multiple manufacturing processes (lot based, process, discrete, configure-to-order, and project based), and all organizations across a virtual supply chain. 

As a single solution for distribution, supply chain, and manufacturing planning, it is based on one supply chain model, one planning engine, and one setup. 

Its flexible configuration, however, enables you to define different models that can co-exist (hub-and-spoke planning; single plan) and evolve as your organization grows without requiring re-implementation. 

Extensive defaulting logic, paired with a productivity enhancing UI and strong exception management, enables planners to quickly use the tool to make their planning decisions.

PS Does not snap shot Alternate Resource For Firmed Batches

On : version, Anything not covered by other codes

Find that the alternate resource is not present in Production Scheduling (PS) for firmed OPM Batches

Expect to see the alternate resources present in PS

The issue can be reproduced at will with the following steps:
1- Define Alternate Resource for a certain Resource- Item
2- Create Firmed Batch on that Item
3- N/A
4- Run Data Collection
5- Run Production Scheduling Schedule (Without ASCP Plan Reference)

The issue has the following business impact:
Due to this issue, users cannot manually assign alternate resources in PS for OPM firmed batches


The alternate resource for batches that are Firmed at the header are not collected into ASCP and hence this data is not available to PS. This is the correct functionality as it is a positive user action to firm the batch header, without this action the alternate resource is collected into ASCP and is available in PS


To implement the solution, please execute the following steps:

1. Go into the responsibility: Production Supervisor

2. Navigate to Batches

3. Do not set the batch header as firmed or create the batches from ASCP/PS and firm the resources without firming the header