Item not available in the Price List

Item not available in the Price List

Recently one of our Technical guy reported that , they have setup a complete New operating Unit , Inventory Org ( Master / Transaction) , New items etc. Everything work fines , but Item is not visible in the Pricelist. For the sake of testing he set ALL_ITEMS in the Price list and able to Book/Ship/close order line with a default price.

Initially I don't see any issue with the Item or any other Setup , but WHY on earth Item still not visible to Price list.But latter I found that it is because of a Silly mistake.We have one Profile option
QP:Item Validation Organization and It should have a value of your Master Validation Inventory Org  , but in this particular case he left it blank and because of it Items were not Visible in the Price List.

Order Header is in Closed Status , even when all the Order lines are cancelled

Order Header is in Closed Status , even when all the Order lines are cancelled

Many a times we have notice that Order Header has value FLOW_STATUS_CODE = 'Closed', open_flag ='N', where as all the lines associated with the Order Header in status Cancelled. FLOW_STATUS_CODE ='Cancelled', Open_flag = 'N' and Cancelled_flag ='Y', well that's what we expect from the system. Though that is the expectations , but many a time user debate that since all the orders lines are cancelled , order header should be in Cancelled status and NOT in Closed status.
Please Note that if we cancelled the order header , in that case cancellation process will cancel all the open lines and then cancel the order header and set the Header flow status code = cancelled and cancelled flag = Y and open_flag = N.

But the case that we are discussing here is not the cancel order process, here we have not cancelled the Order Header rather we have cancel all the line.Please note that when we cancel a order line , it cancels(if possible) the order line and associated delivery details (if possible) and then execute the "CLOSE LINE" workflow process to set the cancel flag = Y and flow_status_code = Y, and at the same time it let the Order header workflow knows that this line is cancelled and open_flag = N.
Thus  the moment last order line got cancelled and inform order header that openflag for it is N , order header workflow evaluate that if there are no more open lines , it progress towards the Close Order process and close the Order ( if you have setup close the order header when all the lines has open_flag = N) .That is the reason when you query such order you can see the Order STATUS as CLOSED and NOT the CANCELLED. Please note that cancelling all lines are different from the Cancel Order .
In Cancel order we explicitly tell the system to cancel the order , but if you cancel all the order lines individually , system will not considered that action same as that of Close Header.

What is ERP? What will ERP fix in my business?

What is ERP? What will ERP fix in my business?

ERP Stands for Enterprise resource Planning system.These systems are comprehensive organizational strategies, implemented at both the technical and operational levels. At its most fundamental level, ERP is a methodology for effectively applying resources—people, processes and technology—to the mission and operational goals of an organization. This methodology encompasses three phases: understanding the goals and strategies of the business; describing how best to implement those objectives; and mapping individual IT projects to the appropriate processes.



ERP is not a pure technical undertaking; it integrates business process reengineering (BPR) with proven IT methodologies. The following principles are intrinsic to achieving best business practices through ERP:

1. A process orientation—Automating workflow ensures that activities are accomplished smoothly and with consistently high quality. This can generate savings in time, money and personnel, and improve the quality of services.
2. A common data model—To share and utilize meaningful information across an enterprise, data definitions and formats and communications protocols must be standardized. Additionally, a common relational database (RDB) can simplify querying, reporting and data analysis.
3. Integrated systems development—Systems within an ERP solution must be conversant. Ideally, each module would use the same components; minimally, they must meet the same technical specifications.
4. A simple architecture—A careful assessment of the existing IT architecture against ERP business requirements will produce many opportunities to reduce costs improve performance and expand services by simplifying the architecture to reduce redundancies and inefficiencies in systems and processes.

The critical components of ERP include:

• A strategic plan describing what will be done.
• A set of process designs that state how the work will be accomplished.
• A set of integrated IT project plans that will support the processes.
• A body of standards to which the data, databases, applications, networks and hardware must conform
• Management support and human resources education.

What will ERP fix in my business?
There are five major reasons why companies undertake ERP.
Integrate financial information—As the CEO tries to understand the company's overall performance, he may find many different versions of the truth. Finance has its own set of revenue numbers, sales has another version, and the different business units may each have their own version of how much they contributed to revenues. ERP creates a single version of the truth that cannot be questioned because everyone is using the same system.

Integrate customer order information—ERP systems can become the place where the customer order lives from the time a customer service representative receives it until the loading dock ships the merchandise and finance sends an invoice. By having this information in one software system, rather than scattered among many different systems that can't communicate with one another, companies can keep track of orders more easily, and coordinate manufacturing, inventory and shipping among many different locations at the same time.

Standardize and speed up manufacturing processes—Manufacturing companies—especially those with an appetite for mergers and acquisitions—often find that multiple business units across the company make the same widget using different methods and computer systems. ERP systems come with standard methods for automating some of the steps of a manufacturing process. Standardizing those processes and using a single, integrated computer system can save time, increase productivity and reduce head count.

Reduce inventory—ERP helps the manufacturing process flow more smoothly, and it improves visibility of the order fulfillment process inside the company. That can lead to reduced inventories of the stuff used to make products (work-in-progress inventory), and it can help users better plan deliveries to customers, reducing the finished good inventory at the warehouses and shipping docks. To really improve the flow of your supply chain, you need supply chain software, but ERP helps too.

Standardize HR information—Especially in companies with multiple business units, HR may not have a unified, simple method for tracking employees' time and communicating with them about benefits and services. ERP can fix that.

In the race to fix these problems, companies often lose sight of the fact that ERP packages are nothing more than generic representations of the ways a typical company does business. While most packages are exhaustively comprehensive, each industry has its quirks that make it unique. Most ERP systems were designed to be used by discrete manufacturing companies (that make physical things that can be counted), which immediately left all the process manufacturers (oil, chemical and utility companies that measure their products by flow rather than individual units) out in the cold. Each of these industries has struggled with the different ERP vendors to modify core ERP programs to their needs.
NOTE - For above Blog I collected the information from various site and compiled here.

Oracle 11g Database (Complete Story)

Oracle 11g Database (Complete Story)

Found Interesting presentation on Oracle 11g database.

CLICK THE BELOW LINK
Oracle Upgrade11gr1 Workshop1

Steps Required to Close Inventory Period in Oracle Application

Steps Required to Close Inventory Period in Oracle Application

This is post about the Steps that we should follow while closing Inventory Period in Oracle Application. Below power point presentation explain

1. Steps that we should follows.
2. Exception reports that we should run before we try to close the Period.

NOTE - Please Note that Period Close in irreversible Process.

How to Close Period in Oracle Apps Inventory
 Click the above link it shows the slides

Query to check all the Picked Delivery Deatils where Reservation not exists

Query to check all the Picked Delivery Deatils where Reservation not exists

 Select ool.line_id,
 wdd.delivery_detail_id,wdd.source_line_id,wdd.ship_from_location_id,wdd.organi
 zation_id,wdd.src_requested_quantity,
  wdd.shipped_quantity,wdd.move_order_line_id,wdd.customer_item_id,wdd.split_fro
 m_delivery_detail_id,
   wdd.source_header_number,wdd.source_line_number,wdd.picked_quantity
  from wsh_delivery_details wdd , oe_order_lines_all ool
  where wdd.source_line_id  = ool.line_id
        and ool.open_flag ='Y'
        and wdd.source_code ='OE'
        and Picked_quantity > 0
        and released_status= 'Y'
        and ool.header_id = &enter_Sales_Order_Header_id
        and not exists
        (
        select 1 from mtl_reservations mr
         where mr.demand_source_line_id = wdd.source_line_id
            and mr.inventory_item_id = wdd.inventory_item_id) 

Above sample script is with Order_Header_id as a Parameter. If you want to check for all the Orders , then remove the parameter Condition.

Query to display Sales Order Line Number

Query to display Sales Order Line Number

Below is Simple Query to display Sales Order Line Number in Oracle Order Management

select to_char(l.line_number) ||
           decode(l.shipment_number, null, null, '.' ||
 to_char(l.shipment_number))||
           decode(l.option_number, null, null, '.' ||
 to_char(l.option_number)) ||
           decode(l.component_number, null, null,
                  decode(l.option_number, null, '.',null)||
                  '.'||to_char(l.component_number))||
           decode(l.service_number,null,null,
                  decode(l.component_number, null, '.' , null) ||
                         decode(l.option_number, null, '.', null ) ||
                         '.'|| to_char(l.service_number))||','||
h.order_number
from
oe_order_headers_all h
, oe_order_lines_all l
where
h.header_id = l.header_id
and l.open_flag='Y'

Sample OE_ORDER_PUB to Create Order with 1 Ship line and 1 RMA Line (API)

Sample OE_ORDER_PUB to Create Order with 1 Ship line and 1 RMA Line

-- API to create Sales Order and Return Order.
declare
l_api_version_number NUMBER := 1;
l_return_status VARCHAR2(2000);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
/*****************PARAMETERS****************************************************/
l_debug_level number := 1; -- OM DEBUG LEVEL (MAX 5)
l_org number  := 204; -- OPERATING UNIT
l_user number := 1318; -- USER
l_resp number := 21623; -- RESPONSIBLILTY
l_appl number := 660; -- ORDER MANAGEMENT
/***INPUT VARIABLES FOR PROCESS_ORDER API*************************/
l_header_rec oe_order_pub.header_rec_type;
l_line_tbl oe_order_pub.line_tbl_type;
l_action_request_tbl oe_order_pub.Request_Tbl_Type;
/***OUT VARIABLES FOR PROCESS_ORDER API***************************/
l_header_rec_out oe_order_pub.header_rec_type;
l_header_val_rec_out oe_order_pub.header_val_rec_type;
l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
l_line_tbl_out oe_order_pub.line_tbl_type;
l_line_val_tbl_out oe_order_pub.line_val_tbl_type;
l_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
l_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;

l_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
l_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
l_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
l_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
l_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
l_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
l_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
l_action_request_tbl_out oe_order_pub.request_tbl_type;
l_msg_index NUMBER;
l_data VARCHAR2(2000);
l_loop_count NUMBER;
l_debug_file VARCHAR2(200);
-- book API vars

b_return_status VARCHAR2(200);
b_msg_count NUMBER;
b_msg_data VARCHAR2(2000);
BEGIN
dbms_application_info.set_client_info(l_org);

/*****************INITIALIZE DEBUG INFO*************************************/
if (l_debug_level > 0) then
l_debug_file := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
oe_debug_pub.initialize;
oe_debug_pub.setdebuglevel(l_debug_level);
Oe_Msg_Pub.initialize;
end if;
/*****************INITIALIZE ENVIRONMENT*************************************/
fnd_global.apps_initialize(l_user, l_resp, l_appl); -- pass in user_id, responsibility_id, and application_id
/*****************INITIALIZE HEADER RECORD******************************/
l_header_rec := oe_order_pub.G_MISS_HEADER_REC;
/***********POPULATE REQUIRED ATTRIBUTES **********************************/

l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
l_header_rec.order_type_id := 1437;--1430
l_header_rec.sold_to_org_id :=1006;
l_header_rec.ship_to_org_id :=1026;
l_header_rec.price_list_id := 1000;
l_header_rec.pricing_date := SYSDATE;
l_header_rec.transactional_curr_code := 'USD';
l_header_rec.flow_status_code := 'ENTERED';
l_header_rec.cust_po_number := '06112009-08';
l_header_rec.order_source_id := 0 ;
--l_header_rec.attribute1 := 'ABC';
/*******INITIALIZE ACTION REQUEST RECORD*************************************/
l_action_request_tbl(1) := oe_order_pub.G_MISS_REQUEST_REC;
l_action_request_tbl(1).request_type := oe_globals.g_book_order;
l_action_request_tbl(1).entity_code := oe_globals.g_entity_header;
/*****************INITIALIZE LINE RECORD********************************/


---Create Ship line
l_line_tbl(1) := oe_order_pub.G_MISS_LINE_REC;
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_CREATE;
l_line_tbl(1).inventory_item_id := 149 ;
l_line_tbl(1).ordered_quantity := 1;
l_line_tbl(1).ship_to_org_id := 1026 ;
l_line_tbl(1).tax_code := 'Location' ;
---------
---Create RMA line
l_line_tbl(2) := oe_order_pub.G_MISS_LINE_REC;
l_line_tbl(2).operation := OE_GLOBALS.G_OPR_CREATE;
l_line_tbl(2).inventory_item_id := 149 ;
l_line_tbl(2).ordered_quantity := 1;
l_line_tbl(2).ship_to_org_id := 1026 ;
l_line_tbl(2).tax_code := 'Location' ;
l_line_tbl(2).line_type_id :=1425;
l_line_tbl(2).return_reason_code :='DAMAGED PRODUCT';



/*****************CALLTO PROCESS ORDER API*********************************/
dbms_output.put_line('Calling API');
oe_order_pub.Process_Order( p_api_version_number => l_api_version_number,
p_header_rec => l_header_rec,
p_line_tbl => l_line_tbl,
p_action_request_tbl => l_action_request_tbl,
--OUT variables
x_header_rec => l_header_rec_out,
x_header_val_rec => l_header_val_rec_out,
x_header_adj_tbl => l_header_adj_tbl_out,
x_header_adj_val_tbl => l_header_adj_val_tbl_out,
x_header_price_att_tbl => l_header_price_att_tbl_out,
x_header_adj_att_tbl => l_header_adj_att_tbl_out,
x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out,
x_header_scredit_tbl => l_header_scredit_tbl_out,
x_header_scredit_val_tbl => l_header_scredit_val_tbl_out,
x_line_tbl => l_line_tbl_out,
x_line_val_tbl => l_line_val_tbl_out,
x_line_adj_tbl => l_line_adj_tbl_out,
x_line_adj_val_tbl => l_line_adj_val_tbl_out,
x_line_price_att_tbl => l_line_price_att_tbl_out,
x_line_adj_att_tbl => l_line_adj_att_tbl_out,
x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out,
x_line_scredit_tbl => l_line_scredit_tbl_out,
x_line_scredit_val_tbl => l_line_scredit_val_tbl_out,
x_lot_serial_tbl => l_lot_serial_tbl_out,
x_lot_serial_val_tbl => l_lot_serial_val_tbl_out,
x_action_request_tbl => l_action_request_tbl_out,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);

/*****************CHECK RETURN STATUS***********************************/
if l_return_status = FND_API.G_RET_STS_SUCCESS then
dbms_output.put_line('Return status is success ');
dbms_output.put_line('debug level '||l_debug_level);
if (l_debug_level > 0) then
dbms_output.put_line('success');
end if;
commit;
else
dbms_output.put_line('Return status failure ');
if (l_debug_level > 0) then
dbms_output.put_line('failure');
end if;
rollback;
end if;

/*****************DISPLAY RETURN STATUS FLAGS******************************/
if (l_debug_level > 0) then
 DBMS_OUTPUT.PUT_LINE('process ORDER ret status IS: ' ||l_return_status);
DBMS_OUTPUT.PUT_LINE('process ORDER msg data IS: ' ||l_msg_data);
DBMS_OUTPUT.PUT_LINE('process ORDER msg COUNT IS: ' ||l_msg_count);
DBMS_OUTPUT.PUT_LINE('header.order_number IS: ' ||to_char(l_header_rec_out.order_number));
DBMS_OUTPUT.PUT_LINE('header.return_status IS: '|| l_header_rec_out.return_status);
DBMS_OUTPUT.PUT_LINE('header.booked_flag IS: '|| l_header_rec_out.booked_flag);
DBMS_OUTPUT.PUT_LINE('header.header_id IS: '|| l_header_rec_out.header_id);
DBMS_OUTPUT.PUT_LINE('header.order_source_id IS: '|| l_header_rec_out.order_source_id);
DBMS_OUTPUT.PUT_LINE('header.flow_status_code IS: '|| l_header_rec_out.flow_status_code);
end if;
/*****************DISPLAY ERROR MSGS*************************************/
if (l_debug_level > 0) then
FOR i IN 1 .. l_msg_count LOOP
Oe_Msg_Pub.get(
p_msg_index => i
,p_encoded => Fnd_Api.G_FALSE
,p_data => l_data
,p_msg_index_out => l_msg_index);
DBMS_OUTPUT.PUT_LINE('message is: ' ||l_data);
DBMS_OUTPUT.PUT_LINE('message index is: ' ||l_msg_index);
END LOOP;
end if;
if (l_debug_level > 0) then
DBMS_OUTPUT.PUT_LINE('Debug = ' ||OE_DEBUG_PUB.G_DEBUG);
DBMS_OUTPUT.PUT_LINE('Debug Level = ' ||to_char(OE_DEBUG_PUB.G_DEBUG_LEVEL));
DBMS_OUTPUT.PUT_LINE('Debug File = ' ||OE_DEBUG_PUB.G_DIR||'/'||OE_DEBUG_PUB.G_FILE);
DBMS_OUTPUT.PUT_LINE('****************************************************');
end if;
END;

Sample OE_ORDER_PUB to Create and Canel Order Line(API)

Sample OE_ORDER_PUB to Create and Canel Order Line

-- API to create Sales Order and Return Order.
declare
l_api_version_number NUMBER := 1;
l_return_status VARCHAR2(2000);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
/*****************PARAMETERS****************************************************/
l_debug_level number := 1; -- OM DEBUG LEVEL (MAX 5)
l_org number  := 204; -- OPERATING UNIT
l_user number := 1318; -- USER
l_resp number := 21623; -- RESPONSIBLILTY
l_appl number := 660; -- ORDER MANAGEMENT
/***INPUT VARIABLES FOR PROCESS_ORDER API*************************/
l_header_rec oe_order_pub.header_rec_type;
l_line_tbl oe_order_pub.line_tbl_type;
l_action_request_tbl oe_order_pub.Request_Tbl_Type;
/***OUT VARIABLES FOR PROCESS_ORDER API***************************/
l_header_rec_out oe_order_pub.header_rec_type;
l_header_val_rec_out oe_order_pub.header_val_rec_type;
l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
l_line_tbl_out oe_order_pub.line_tbl_type;
l_line_val_tbl_out oe_order_pub.line_val_tbl_type;

l_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
l_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;
l_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
l_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
l_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
l_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
l_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
l_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
l_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
l_action_request_tbl_out oe_order_pub.request_tbl_type;
l_msg_index NUMBER;
l_data VARCHAR2(2000);
l_loop_count NUMBER;
l_debug_file VARCHAR2(200);
-- book API vars

b_return_status VARCHAR2(200);
b_msg_count NUMBER;
b_msg_data VARCHAR2(2000);
BEGIN
dbms_application_info.set_client_info(l_org);

/*****************INITIALIZE DEBUG INFO*************************************/
if (l_debug_level > 0) then
l_debug_file := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
oe_debug_pub.initialize;
oe_debug_pub.setdebuglevel(l_debug_level);
Oe_Msg_Pub.initialize;
end if;
/*****************INITIALIZE ENVIRONMENT*************************************/
fnd_global.apps_initialize(l_user, l_resp, l_appl); -- pass in user_id, responsibility_id, and application_id
/*****************INITIALIZE HEADER RECORD******************************/
l_header_rec := oe_order_pub.G_MISS_HEADER_REC;
/***********POPULATE REQUIRED ATTRIBUTES **********************************/
l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
l_header_rec.order_type_id := 1437;--1430
l_header_rec.sold_to_org_id :=1006;
l_header_rec.ship_to_org_id :=1026;
l_header_rec.price_list_id := 1000;
l_header_rec.pricing_date := SYSDATE;
l_header_rec.transactional_curr_code := 'USD';
l_header_rec.flow_status_code := 'ENTERED';
l_header_rec.cust_po_number := '06112009-08';
l_header_rec.order_source_id := 0 ;
--l_header_rec.attribute1 := 'ABC';
/*******INITIALIZE ACTION REQUEST RECORD*************************************/
l_action_request_tbl(1) := oe_order_pub.G_MISS_REQUEST_REC;
l_action_request_tbl(1).request_type := oe_globals.g_book_order;
l_action_request_tbl(1).entity_code := oe_globals.g_entity_header;
/*****************INITIALIZE LINE RECORD********************************/
/*
---Ship line or RMA line
l_line_tbl(1) := oe_order_pub.G_MISS_LINE_REC;
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_CREATE;
l_line_tbl(1).inventory_item_id := 149 ;
l_line_tbl(1).ordered_quantity := 1;
l_line_tbl(1).ship_to_org_id := 1026 ;
l_line_tbl(1).tax_code := 'Location' ;
--l_line_tbl(1).line_type_id :=1425; (uncomment if you want to Create RMA)
--l_line_tbl(1).return_reason_code :='DAMAGED PRODUCT'; (uncomment if you want to Create RMA)
---------
*/
-- Cancel line
l_line_tbl(1) := oe_order_pub.G_MISS_LINE_REC;
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
l_line_tbl(1).inventory_item_id := 149 ;
l_line_tbl(1).ordered_quantity := 0;
l_line_tbl(1).cancelled_quantity := 1;
l_line_tbl(1).cancelled_flag := 'Y';
l_line_tbl(1).line_id := 196958;
l_line_tbl(1).change_reason :='SYSTEM';


/*****************CALLTO PROCESS ORDER API*********************************/
dbms_output.put_line('Calling API');
oe_order_pub.Process_Order( p_api_version_number => l_api_version_number,
p_header_rec => l_header_rec,
p_line_tbl => l_line_tbl,
p_action_request_tbl => l_action_request_tbl,
--OUT variables
x_header_rec => l_header_rec_out,
x_header_val_rec => l_header_val_rec_out,
x_header_adj_tbl => l_header_adj_tbl_out,
x_header_adj_val_tbl => l_header_adj_val_tbl_out,
x_header_price_att_tbl => l_header_price_att_tbl_out,
x_header_adj_att_tbl => l_header_adj_att_tbl_out,
x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out,
x_header_scredit_tbl => l_header_scredit_tbl_out,
x_header_scredit_val_tbl => l_header_scredit_val_tbl_out,
x_line_tbl => l_line_tbl_out,
x_line_val_tbl => l_line_val_tbl_out,
x_line_adj_tbl => l_line_adj_tbl_out,
x_line_adj_val_tbl => l_line_adj_val_tbl_out,
x_line_price_att_tbl => l_line_price_att_tbl_out,
x_line_adj_att_tbl => l_line_adj_att_tbl_out,
x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out,
x_line_scredit_tbl => l_line_scredit_tbl_out,
x_line_scredit_val_tbl => l_line_scredit_val_tbl_out,
x_lot_serial_tbl => l_lot_serial_tbl_out,
x_lot_serial_val_tbl => l_lot_serial_val_tbl_out,
x_action_request_tbl => l_action_request_tbl_out,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);

/*****************CHECK RETURN STATUS***********************************/
if l_return_status = FND_API.G_RET_STS_SUCCESS then
dbms_output.put_line('Return status is success ');
dbms_output.put_line('debug level '||l_debug_level);
if (l_debug_level > 0) then
dbms_output.put_line('success');
end if;
commit;
else
dbms_output.put_line('Return status failure ');
if (l_debug_level > 0) then
dbms_output.put_line('failure');
end if;
rollback;
end if;

/*****************DISPLAY RETURN STATUS FLAGS******************************/
if (l_debug_level > 0) then
 DBMS_OUTPUT.PUT_LINE('process ORDER ret status IS: ' ||l_return_status);
DBMS_OUTPUT.PUT_LINE('process ORDER msg data IS: ' ||l_msg_data);
DBMS_OUTPUT.PUT_LINE('process ORDER msg COUNT IS: ' ||l_msg_count);
DBMS_OUTPUT.PUT_LINE('header.order_number IS: ' ||to_char(l_header_rec_out.order_number));
DBMS_OUTPUT.PUT_LINE('header.return_status IS: '|| l_header_rec_out.return_status);
DBMS_OUTPUT.PUT_LINE('header.booked_flag IS: '|| l_header_rec_out.booked_flag);
DBMS_OUTPUT.PUT_LINE('header.header_id IS: '|| l_header_rec_out.header_id);
DBMS_OUTPUT.PUT_LINE('header.order_source_id IS: '|| l_header_rec_out.order_source_id);
DBMS_OUTPUT.PUT_LINE('header.flow_status_code IS: '|| l_header_rec_out.flow_status_code);
end if;
/*****************DISPLAY ERROR MSGS*************************************/
if (l_debug_level > 0) then
FOR i IN 1 .. l_msg_count LOOP
Oe_Msg_Pub.get(
p_msg_index => i
,p_encoded => Fnd_Api.G_FALSE
,p_data => l_data
,p_msg_index_out => l_msg_index);
DBMS_OUTPUT.PUT_LINE('message is: ' ||l_data);
DBMS_OUTPUT.PUT_LINE('message index is: ' ||l_msg_index);
END LOOP;
end if;
if (l_debug_level > 0) then
DBMS_OUTPUT.PUT_LINE('Debug = ' ||OE_DEBUG_PUB.G_DEBUG);
DBMS_OUTPUT.PUT_LINE('Debug Level = ' ||to_char(OE_DEBUG_PUB.G_DEBUG_LEVEL));
DBMS_OUTPUT.PUT_LINE('Debug File = ' ||OE_DEBUG_PUB.G_DIR||'/'||OE_DEBUG_PUB.G_FILE);
DBMS_OUTPUT.PUT_LINE('****************************************************');
end if;
END;

Query to get Contact Assocaited with Party ID and Account ID

Query to get Contact Assocaited with Party ID and Account ID

SELECT party.party_type,party.party_name Contact_Name,
rel.relationship_code,rel.relationship_id,
--org_cont.*,cont_point.*,
--party.party_id,rel_party.party_id,rel.subject_id,rel.object_id, rel.party_id,
SUBSTRB(PARTY.PERSON_FIRST_NAME,1,40) FIRST_NAME ,
SUBSTRB(PARTY.PERSON_LAST_NAME,1,50) LAST_NAME ,
PARTY.CUSTOMER_KEY CONTACT_KEY ,
REL_PARTY.EMAIL_ADDRESS ,
-- PARTY.PARTY_ID ,
ORG_CONT.ORG_CONTACT_ID ,
CONT_POINT.CONTACT_POINT_ID ,
ORG_CONT.CONTACT_NUMBER
FROM HZ_CONTACT_POINTS CONT_POINT,
HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
HZ_PARTIES PARTY,
HZ_PARTIES REL_PARTY,
HZ_RELATIONSHIPS REL,
HZ_ORG_CONTACTS ORG_CONT ,
HZ_CUST_ACCOUNTS ROLE_ACCT
WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL_PARTY.PARTY_ID = REL.PARTY_ID
AND CONT_POINT.OWNER_TABLE_ID(+) = REL_PARTY.PARTY_ID
AND CONT_POINT.PRIMARY_FLAG = 'Y'
AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID
AND CONT_POINT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
and ROLE_ACCT.cust_account_id = &Enter_Account_id
and rel.object_id = &Enter_Party_id
AND rel.subject_table_name ='HZ_PARTIES'
AND rel.object_table_name ='HZ_PARTIES'
and party.status ='A'
and rel_party.status ='A'
and rel.status ='A'

Query to get Customer Role in TCA

Query to get Customer Role in TCA

Below is simple query to get the Customer Role Details in TCA

select a.cust_account_role_id,a.*
from
hz_cust_account_roles a, hz_role_responsibility b, hz_cust_acct_sites_all c
where
a.role_type = 'CONTACT'
and a.party_id = p_party_id
and a.cust_account_id = p_cust_account_id
and a.cust_acct_site_id = c.cust_acct_site_id
and a.cust_account_id = c.cust_account_id
and c.party_site_id = p_party_site_id
and a.cust_account_role_id = b.cust_account_role_id
and responsibility_type = p_acct_site_type;

Oracle Order Management DropShip Flow for R12

Oracle Order Management DropShip Flow for R12

In this blog I will explain Oracle  Drop Ship Flow for R12i.
What is Dropship Order - In dropship Order, sales order line creates a requisition line that becomes a PO and sent to your supplier. You instruct your supplier to send the item or configured item directly to your customer only and items never physically pass through your warehouse, and therefore you do not pick, pack or ship them yourselves

In this blog I will explain the R12 Dataflow of Dropship order from OM to Oracle Payables.
Topics covered in this blog are
Drop Ship Sales order creation and booking.
Requisition Import
Auto Creation of PO
Receipt against PO
AP against PO



Since in Dropship we don’t receive the goods in our warehouse, SOURCE_TYPE will be “External”, where as for non-dropship orders it is Internal. Once Book, we cannot Changes SOURCE_TYPE.

We can change the SOURCE_TYPE as “External” for an item at the time of order creation, or we can define it permanently in the Item creation Setup, by setting Default SO Source Type = External.

Also as we don’t have any shipping activity in dropship orders, Receiving Transaction Processor (RTP) , triggered after Submitting the receipt will push the workflow from Shipping activity to Fulfillment.
R12 Dataflow of Dropship order from OM to Oracle Payables.
Step1 - Drop ship Sales Order Creation
Step2 - Source Type = External


Step3 -Once order is Book, order line workflow will be at Purchase release Deferred Status .

Step4 -Submit Workflow to progress the Order line workflow.



Step5 -Submit the requisition Import to create Requisition for Drop Ship


Step6 -Once requisition Import Completed successfully , Navigate to Order lines Action >Additional Information and then go to DropShip tab.




Step7 - Switch to Purchasing Responsibility and Navigate to Requisition Summary



Step8 -Query for Requisition Number



Step9 – Open the Auto Create Window and Query for Requisition #



Step9 –Auto Create


Step10 -Approve the PO


Step11 - Browse the Shipment Details of PO , (Shipment Button)



Step11 - Browse the Shipment Details of PO, (Shipment Button).We can select the Approval Level and Invoice Match Option based on your business requirements.



Step11 - Browse the Shipment Details of PO , (Shipment button).


Step11 - Browse the Shipment Details of PO , (Shipment Button), it will display Qty Ordered/billed/shipped etc.







Step12 - Browse the Distribution Details of PO , (Distribution Button).




Step13 - Navigate to receipts.



Step13 - Navigate to Receipt , system will prompt for Inventory Org , Select your Inventory Organization.

Step14 - Enter Receipt against PO.


Step14 -

Step14 -


Step15 - Once you save the Receipt , System will submit Receiving Transaction processor.



Step16 - RTP on successful completion Progress the OM Workflow and Line status change from Awaiting receipt to SHIPPED.

Queries to connect RA_CUSTOMER_TRX_ALL and OE_ORDER_HEADERS_ALL/LINES_ALL

Queries to connect RA_CUSTOMER_TRX_ALL and OE_ORDER_HEADERS_ALL/LINES_ALL

SELECT rt.trx_number,rt.interface_header_attribute1,rt.interface_header_context,h.order_number from
RA_CUSTOMER_TRX_ALL rt , oe_order_headers_all h
where 1=1
and interface_header_context = 'ORDER ENTRY'
and interface_header_attribute1 = to_char(h.order_number)



SELECT rl.customer_trx_line_id,rl.customer_trx_id,rl.line_number Invoice_line_num,
rl.interface_line_attribute1,h.order_number, l.line_id, rl.sales_order_line
from
RA_CUSTOMER_TRX_LINES_ALL rl,
oe_order_lines_all l,
oe_order_headers_all h
where line_type = 'LINE'
and interface_line_context = 'ORDER ENTRY'
and h.header_id = l.header_id
and interface_line_attribute6 = to_char(l.line_id)
and interface_line_attribute1 = to_char(h.order_number)
and sales_order = h.order_number


SELECT rl.interface_status,rl.line_number Invoice_line_num,
h.order_number, l.line_id
from RA_INTERFACE_LINES_ALL rl,
oe_order_lines_all l,
oe_order_headers_all h
where line_type = 'LINE'
and interface_line_context = 'ORDER ENTRY'
and h.header_id = l.header_id
and interface_line_attribute6 = to_char(l.line_id)
and interface_line_attribute1 = to_char(h.order_number)
and sales_order_line IS NOT NULL;

Oracle R12 Internal Requisition and Internal Sales Order Process Flow.

Oracle R12 Internal Requisition and Internal Sales Order Process Flow.

In this blog I will explain the Process flow and Setup required to create Oracle Order Management Internal Sales order (ISO) from Internal requisition (IR)

What is internal sales order
A request within your company for goods or services. An internal sales order originates
from an employee or from another process as a requisition, such as inventory or
manufacturing, and becomes an internal sales order when the information is transferred
from Purchasing to Order Management. Also known as internal requisition or purchase
requisition.

Oracle Purchasing uses Order Import to transfer requisitions for internally sourced
products to Order Management. Once imported, the internal sales orders are processed
similar to regular sales orders.

Note: You can create requisitions (with same number) in two different operating units.

You can partially or fully cancel the line quantities on internal sales orders.
You can make the following attribute changes after an internal sales order has been generated:
Update Scheduled Shipment Date
Decrement order line quantity
Note: If you cancel an internal sales order, order line, or backordered order line that is linked to an internal requisition, manually cancel the internal requisition. The order cancelProcessing issues a reminder.

Test Case –
Item – IR Item004
Assign to Inventory Org M1 and M2
Create Requisition with Destination Org M1 and Source Org M2.Create Internal Sales Order in M2

1# Create Item IR Item4 , with “Purchased Item” as template , in Oracle Inventory Management.


2# Mark Item as Internal Order Enabled.


3# Assign Item to M1 and M2 Inventory Orgs.


4# Define Cost for Item Navigation >Costs >Item Costs







5# Update the cost
Navigation
     Costs
        Update Standard Costs
             Update Cost







6# Set the Internal Order Setup at Customer Ship To Site in Oracle Order Management.





7# Define Shipping Network setup Between M2 and M2 in Oracle Shipping.
Inventory > Setup > Organization > shipping Network





8# Define Purchasing Option Setup for Internal Sales Order and define
Internal requisition Order Type
Internal requisition Order Source.
in Oracle Purchasing Module



9#Create & Approve Requisition in Oracle Purchasing


Save and Approve Requisition.


10#submit “Create Internal Order” Concurrent program in Oracle Purchasing.


11#Submit Order Import




Query for Sales Order based on requisition Number.


12# Progress the Sales Order from Awaiting Shipping to Close.

Sample Code for oe_order_pub.Process_Order(API)

Sample Code for oe_order_pub.Process_Order

Got sample code to create Sales Order in Oracle Order Management .Please note that this is not my code , one of my friend has forwaded this .But I have tested it throughly and succesfully created Sales Orders.

create or replace procedure createsalesorder
(p_org_id NUMBER,
p_user_id NUMBER,
p_resp_id NUMBER,
p_appl_id NUMBER,
p_order_type_id NUMBER,
p_sold_to_org_id NUMBER,
p_ship_to_org_id NUMBER,
p_price_list_id NUMBER,
p_curr_code VARCHAR2,
p_flow_status_code VARCHAR2,
p_po_num VARCHAR2,
p_order_source_id NUMBER,
p_inventory_item_id NUMBER,
p_ordered_quantity NUMBER,
p_tax_code VARCHAR2)
IS
 l_api_version_number NUMBER := 1;

l_return_status VARCHAR2(2000);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
/*****************PARAMETERS****************************************************/
l_debug_level number := 1; -- OM DEBUG LEVEL (MAX 5)
l_org number := p_org_id;--204; -- OPERATING UNIT
l_user number := p_user_id;--1318; -- USER
l_resp number := p_resp_id;--21623; -- RESPONSIBLILTY
l_appl number := p_appl_id;--660; -- ORDER MANAGEMENT
/***INPUT VARIABLES FOR PROCESS_ORDER API*************************/
l_header_rec oe_order_pub.header_rec_type;
l_line_tbl oe_order_pub.line_tbl_type;
l_action_request_tbl oe_order_pub.Request_Tbl_Type;
/***OUT VARIABLES FOR PROCESS_ORDER API***************************/
l_header_rec_out oe_order_pub.header_rec_type;
l_header_val_rec_out oe_order_pub.header_val_rec_type;
l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
l_line_tbl_out oe_order_pub.line_tbl_type;
l_line_val_tbl_out oe_order_pub.line_val_tbl_type;
l_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
l_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;
l_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
l_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
l_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
l_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
l_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
l_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
l_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
l_action_request_tbl_out oe_order_pub.request_tbl_type;
l_msg_index NUMBER;
l_data VARCHAR2(2000);
l_loop_count NUMBER;
l_debug_file VARCHAR2(200);
-- book API vars

b_return_status VARCHAR2(200);
b_msg_count NUMBER;
b_msg_data VARCHAR2(2000);
BEGIN
dbms_application_info.set_client_info(l_org);
/*****************INITIALIZE DEBUG INFO*************************************/
if (l_debug_level > 0) then
l_debug_file := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
oe_debug_pub.initialize;
oe_debug_pub.setdebuglevel(l_debug_level);
Oe_Msg_Pub.initialize;
end if;
/*****************INITIALIZE ENVIRONMENT*************************************/
fnd_global.apps_initialize(l_user, l_resp, l_appl); -- pass in user_id, responsibility_id, and application_id
/*****************INITIALIZE HEADER RECORD******************************/
l_header_rec := oe_order_pub.G_MISS_HEADER_REC;
/***********POPULATE REQUIRED ATTRIBUTES **********************************/
l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
l_header_rec.order_type_id := p_order_type_id;--1430;
l_header_rec.sold_to_org_id := p_sold_to_org_id;--1006;
l_header_rec.ship_to_org_id := p_ship_to_org_id;--1026;
l_header_rec.price_list_id := p_price_list_id;--1000;
l_header_rec.pricing_date := SYSDATE;
l_header_rec.transactional_curr_code := p_curr_code;--'USD';
l_header_rec.flow_status_code := p_flow_status_code;--'ENTERED';
l_header_rec.cust_po_number := p_po_num;--'06112009-08';
l_header_rec.order_source_id := p_order_source_id;--0 ;
--l_header_rec.attribute1 := 'ABC';
/*******INITIALIZE ACTION REQUEST RECORD*************************************/
l_action_request_tbl(1) := oe_order_pub.G_MISS_REQUEST_REC;
l_action_request_tbl(1).request_type := oe_globals.g_book_order;
l_action_request_tbl(1).entity_code := oe_globals.g_entity_header;
/*****************INITIALIZE LINE RECORD********************************/
l_line_tbl(1) := oe_order_pub.G_MISS_LINE_REC;
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_CREATE;
l_line_tbl(1).inventory_item_id := p_inventory_item_id;--149 ;
l_line_tbl(1).ordered_quantity := p_ordered_quantity;--1;
l_line_tbl(1).ship_to_org_id := p_ship_to_org_id;--1026 ;
l_line_tbl(1).tax_code := p_tax_code;--'Location' ;
/*****************CALLTO PROCESS ORDER API*********************************/
dbms_output.put_line('Calling API');
oe_order_pub.Process_Order( p_api_version_number => l_api_version_number,
p_header_rec => l_header_rec,
p_line_tbl => l_line_tbl,
p_action_request_tbl => l_action_request_tbl,
--OUT variables
x_header_rec => l_header_rec_out,
x_header_val_rec => l_header_val_rec_out,
x_header_adj_tbl => l_header_adj_tbl_out,
x_header_adj_val_tbl => l_header_adj_val_tbl_out,
x_header_price_att_tbl => l_header_price_att_tbl_out,
x_header_adj_att_tbl => l_header_adj_att_tbl_out,
x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out,
x_header_scredit_tbl => l_header_scredit_tbl_out,
x_header_scredit_val_tbl => l_header_scredit_val_tbl_out,
x_line_tbl => l_line_tbl_out,
x_line_val_tbl => l_line_val_tbl_out,
x_line_adj_tbl => l_line_adj_tbl_out,
x_line_adj_val_tbl => l_line_adj_val_tbl_out,
x_line_price_att_tbl => l_line_price_att_tbl_out,
x_line_adj_att_tbl => l_line_adj_att_tbl_out,
x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out,
x_line_scredit_tbl => l_line_scredit_tbl_out,
x_line_scredit_val_tbl => l_line_scredit_val_tbl_out,
x_lot_serial_tbl => l_lot_serial_tbl_out,
x_lot_serial_val_tbl => l_lot_serial_val_tbl_out,
x_action_request_tbl => l_action_request_tbl_out,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);

/*****************CHECK RETURN STATUS***********************************/
if l_return_status = FND_API.G_RET_STS_SUCCESS then
dbms_output.put_line('Return status is success ');
dbms_output.put_line('debug level '
l_debug_level);
if (l_debug_level > 0) then
dbms_output.put_line('success');
end if;
commit;
else
dbms_output.put_line('Return status failure ');
if (l_debug_level > 0) then
dbms_output.put_line('failure');
end if;
rollback;
end if;

/*****************DISPLAY RETURN STATUS FLAGS******************************/
if (l_debug_level > 0) then
 DBMS_OUTPUT.PUT_LINE('process ORDER ret status IS: '
l_return_status);
DBMS_OUTPUT.PUT_LINE('process ORDER msg data IS: '
l_msg_data);
DBMS_OUTPUT.PUT_LINE('process ORDER msg COUNT IS: '
l_msg_count);
DBMS_OUTPUT.PUT_LINE('header.order_number IS: ' to_char(l_header_rec_out.order_number));
DBMS_OUTPUT.PUT_LINE('header.return_status IS: ' l_header_rec_out.return_status);
DBMS_OUTPUT.PUT_LINE('header.booked_flag IS: ' l_header_rec_out.booked_flag);
DBMS_OUTPUT.PUT_LINE('header.header_id IS: ' l_header_rec_out.header_id);
DBMS_OUTPUT.PUT_LINE('header.order_source_id IS: ' l_header_rec_out.order_source_id);
DBMS_OUTPUT.PUT_LINE('header.flow_status_code IS: ' l_header_rec_out.flow_status_code);
end if;
/*****************DISPLAY ERROR MSGS*************************************/
if (l_debug_level > 0) then
FOR i IN 1 .. l_msg_count LOOP
Oe_Msg_Pub.get(
p_msg_index => i
,p_encoded => Fnd_Api.G_FALSE
,p_data => l_data
,p_msg_index_out => l_msg_index);
DBMS_OUTPUT.PUT_LINE('message is: ' l_data);
DBMS_OUTPUT.PUT_LINE('message index is: ' l_msg_index);
END LOOP;
end if;
if (l_debug_level > 0) then
DBMS_OUTPUT.PUT_LINE('Debug = ' OE_DEBUG_PUB.G_DEBUG);
DBMS_OUTPUT.PUT_LINE('Debug Level = ' to_char(OE_DEBUG_PUB.G_DEBUG_LEVEL));
DBMS_OUTPUT.PUT_LINE('Debug File = ' OE_DEBUG_PUB.G_DIR'/'OE_DEBUG_PUB.G_FILE);
DBMS_OUTPUT.PUT_LINE('****************************************************');

OE_DEBUG_PUB.DEBUG_OFF;
end if;
--END;
end createsalesorder;