Oracle Payable R12 Invoice Approval Workflow Part-1

In Oracle Payable we have option to setup Invoice Approval workflow with the Invoice Approval Process. An Oracle Payable Invoice approval process can be workflow based or without workflow.In this post we will talk about Oracle Payable Workflow based Invoice approval process.

 

• In R12 we have separate approval process for the document and lines.We have seprate workflow for Invoice header and lines.
• Approver who has access to Invoice lines will approve invoice lines only and might not have access to approve the Invoice doc. 
• An Invoice document will not be approved till all the invoice lines that required approval are approved. 
• All the approved lines will keep moving further in their flow and will not be stuck/wait for any other invoice line that required approval but stuck or rejected ( this is based on the anticipation that sometime in the future the issue associated with the stuck /approved line will be resolved and it will be approved) , but keep in mind a doc. Will not ne approved unless all the lines that required approval or approved, if any such lines is stuck/rejected the doc will not be approved. 
• Documents or lines will be processed by workflow approval process based on the status of the doc/lines. If status of the line/document is Not Required then Invoice approval workflow will not pick them for approval process, but if status of the document or line is Required /Re-approval Required then they will be picked by Oracle Payable Invoice approval workflow process. 
• In R12 a new functionality has been introduce where by an line level approval now can see /modify the distribution accounts details for the line (especially for the Invoice lines that are Manually created). 
• In R12 a new functionality has been introduce where by an line level approval now can add attachment to lines as well as distributions. 
• Till now no partial process is supported, document or lines can be either fully approved or rejected but not partially approved. 
• In R12 Negotiation process is now added, this is a very important features, now an approver while involves in the approving process now negotiate the Quantity and price with the supplier again( this is very importance in terms if approver feel quantity /price is not as per the teams negotiated). 

OM: Sales Order Form: Refresh Method (Query Coordination)

OM: Sales Order Form: Refresh Method (Query Coordination) , is a very important profile option available in Oracle Order management. Though usage of this profile is not very common. This profile option if set properly helps you to improve performance especially if you are doing some cascade operations on large # of lines.
For example –

1. Cascade operations on 100+ lines 
2. Cascade operations on a model with 100+ options/class, with show lines = yes
3. In continuation of the point # 2 , let’s consider a case where we have a sales order with 5+ model and each model has 100-200 option/class and we need to perform cascade operations, also show lines = yes

Profile option - OM: Sales Order Form: Refresh Method (Query Coordination)
Internal Name - OM_UI_REFRESH_METHOD
This profile option determines whether the screen is refreshed after every save. There are four options available:

1. Automatic Refresh with Repositioning of Cursor: When the profile is set to this option then screen is refreshed and also the cursor is repositioned to the original line from which the save operation was performed.
2. Automatic Refresh without Repositioning of Cursor: When the profile is set to this option then screen is refreshed and the cursor is always positioned on the first line.
3. Manual: With this option, users have to explicitly re-query to see the latest changes. Users are also not prompted with a message.
4. Askme: A dialog box is prompted for the users to decide whether they want to refresh the screen to see the new data. If the user selects yes, then the screen is refreshed. If the user selects No then screen is not refreshed.

With show lines = Yes , if we perform our cascade operations with Profile value is 
•    Automatic Refresh with Repositioning of Cursor
•    Automatic Refresh without Repositioning of Cursor
In such scenario cascade operation will be very time consuming , as cursor will move from line to line till it reach the last line ( as show lines = yes) and then system will reposition to Original record or First record.
On the Other hand other 2 option will not refresh the UI immediately after the cascade operations and also cursor position will remain same and total time consume by cascade operation will be very very less as compared if we choose other 2 options.
For more details feel free to contact us at sgupta@eoracleapps.com

One Time Expense Item for DropShip Sales Order

In Oracle Purchasing, a term called one-time expense item is used. This term refers to an expense item that is not defined in inventory, nor does an associated record exist in the items database table, MTL_SYSTEM_ITEMS. Since a one-time expense item is not defined in inventory, it cannot have the inventory attributes checked and therefore, cannot be drop shipped.

Oracle Supply Chain Management.

With Oracle SCM, companies can build and operate world class value-chains for profitable growth. The Oracle E-Business Suite Supply Chain Management (R12) family of applications integrates and automates all key supply chain processes, from design, planning and procurement to manufacturing and fulfillment, providing a complete solution set to enable companies to power information-driven value chains. Companies can anticipate market requirements and risks, adapt and innovate to respond to volatile market conditions, and align operations across global networks. A unified data model provides a single, accurate view of your entire supply chain. Companies can implement lean, demand driven principles and manage their increasingly complex, global supply chains. And when Oracle Supply Chain Management runs on Oracle technology, you speed implementation, optimize performance, streamline support–and maximize ROI.

For more info. please refer Oracle

Interface Trip Stop

In oracle application (order management) we have 3  options to process our Trip.
1.Run Interface trip stop concurrent program.
2.Run Order Management Interface CP
3.Inventory Interface CP
As I have explained in many of my posts that ITS has 2  subprocess
OM interface and Inventory Interface.So if we run option first it will execute both parts and mark oe_interfaced_flag as well as inv_interfaced_flag as Y in wsh_delivery_details.
But if you run option 2 or 3 it will run OM or Inventory part of ITS and set oe_interfaced_flag OR inv_interfaced_flag respectively.But most important thing that we have to take care is, if want to run these 2 interfaces seperately then we have to make sure that OM part of ITS has executed sucvessfully before we execute the inventory interfaces.
In last so many years I am working in this area and notice very few customer are implementing these 2 seperatly because of high volume of data .

Dual UOM

In Oracle inventory we have option to define dual uom for an item.Dual uom means we can track the transaction of an item in the primary as well as secondary UOM.in oracle applications we have database fields to store the data in the secondary uom as well as secondary quantity.
Please note that just defining an item in dual uom doesnot set the system to track the transaction of an item in primary quantity/UOM as well as secondary quantity/UOM.If we want to track an dual control item in dual UOM then while creating an item we sbould set "Tracking " as " Primary and Secondary".once done system will track all transactions in primary and secondary quantity and UOM.

Query to Get Sales Order Line# , Invoice $ Amount associated with a Delivery

below is Simple Query to Get Sales Order Line#(From oe_order_lilnes-all) , Invoice $ Amount(From Ra Interface table )  associated with a Delivery

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,I.Amount,L.Line_Id
From Oe_Order_Lines_All L,oe_order_headers_all h,
Ra_Interface_Lines_All I
Where h.header_id = l.line_id
And L.Line_Id = To_Number(I.Interface_Line_Attribute6)
   and i.interface_line_attribute3 = '&Enter_Delivery_number'

How to Create User and Group with Oracle Weblogic Admin Server Console

This is Short Video Demo about how to Create User and Group with Oracle Weblogic Admin Server Console

API to Convert UOM

Simple API to covert Qty from 'From UOM' to 'To UOM' , there are many API available in apps for same purpose, I used this one .

declare
l number;
l_from_uom VARCHAR2(100):='&Enter_from_uom';
l_to_uom VARCHAR2(100):='&Enter_to_uom';
l_primary_qty NUMBER := &Enetr_qty_in_pri_uom;
l_inv_item_id NUMBER := &Enter_item_id;
begin
--l:=INV_RCV_CACHE.convert_qty(149,120,'EA','DOZ',NULL,NULL,NULL);
l:=INV_RCV_CACHE.convert_qty(l_inv_item_id,l_primary_qty,l_from_uom,l_to_uom,NULL,NULL,NULL);
dbms_output.put_line('l = '||l);
end;

Payable Invoice Approval Workflow (APINV will be replaced with APINVAPR in R12)

Trying to play around with Oracle Payable Invoice Approval workflow , but completely last between APINV and APINVAPR .

Finally discovered that , In R12 Item_Type for Payables Invoice Approval Workflow is changed from APINV to APINVAPR and this new workflow is released through file apfhanwf.wft

APINV no longer available in R12 and you need to upgrade all your APINV to APINVAPR in R12.
(That what I learn @Day-1)Correct me if I am wrong.

API to Check Status of Purchase Order in Oracle Applications.

Below is API that return you the status of Purchase Order -


DECLARE
l_po_header_id          NUMBER;
l_po_status_rec         PO_STATUS_REC_TYPE;
l_return_status         VARCHAR2(1);
l_autorization_status   VARCHAR2(30);
l_po_release_id         NUMBER;
BEGIN
 PO_DOCUMENT_CHECKS_GRP.po_status_check
                                (p_api_version => 1.0
                                , p_header_id => l_po_header_id
                                , p_release_id => l_po_release_id
                                , p_mode => 'GET_STATUS'
                                , x_po_status_rec => l_po_status_rec
                                , x_return_status => l_return_status);
                               
l_autorization_status := l_po_status_rec.authorization_status(1);
dbms_output.put_line('Status = '||l_autorization_status);
END;

Drop Ship Sales Order in Oracle Order Management (Part-3)

This is Presentation about Drop Ship Sales Order , here I have tested
1.Few Partial Shipment Scnearios.
2.Try to change the Qty on the Drop Ship Purchase Order.
3.Try to cancel the Sales Order line when we have Approval PO exits for it.

For more details about Drop Ship please refer Part- 1 and Part -2 of Drop Ship Presentation.

Drop Ship Sales Order in Oracle Order Management (Part-2)

Features of Drop Ship Order lines -

Reservations
Once an order line has been specified as an External order, Order Management does notallow reservations to be placed against the order line.

Scheduling
When performing drop shipments of models or kits or standard items, the scheduled ship date is defaulted from the order line request date, and Oracle Global ATP calculations are ignored (demand for drop ship orders are not visible to Oracle Planning products).

Drop Ship Sales Order in Oracle Order Management (Part-1)

Drop Ship Sales Order
Drop shipping functionality enables you to take an order from your customer and fulfill it directly from your supplier's site. Order Management enables you to enter drop ship sales orders and lines for standard, model and kit, and configured items, although you currently cannot perform a drop shipment for Ship Model Complete (SMC) PTO's.
You can receive orders for items that you do not stock or for which you lack sufficient inventory, and have a supplier provide the items directly to your customer

The supply and demand details for drop ship orders are

Android ADF Mobile Browser app to creates sales order in Oracle Application.

This is very interesting post about Android Mobile Browser App Created with Oracle ADF to creates sales order in Oracle Application.

This Post is exactly same as the Previous Post  iphone ADF Mobile Browser app to creates sales order in Oracle Application. Only difference is,  here I have executed same application on Android based Samsung Galaxy( I like this device , though iad/iPhone has no comparison).
For more information about ADF Browser and renderer Kit please refer my post  iphone ADF Mobile Browser app to creates sales order in Oracle Application

Query to Get the Order lines where Order lines are Closed , Workflow is also closed , but ITS is not yet executed.

Below is query to Get the Order lines where Order lines are Closed , Workflow is also closed , but ITS is not yet executed.

SELECT line_id, header_id,
           ordered_quantity
    FROM   oe_order_lines_all l,
           wf_items wi
    WHERE  l.open_flag = 'N'
    AND    Nvl(l.cancelled_flag,'N') = 'N'
    AND    l.booked_flag = 'Y'
    AND    l.shipping_interfaced_flag = 'Y'
    AND    Nvl(l.shipped_quantity,0) = 0
    AND    Nvl(l.fulfilled_quantity,0) = 0
    AND    l.fulfilled_flag = 'Y'
    AND    wi.item_type = 'OEOL'
    AND    wi.item_key = To_Char(l.line_id)
    AND    wi.end_date IS NOT NULL
    AND    EXISTS
    (
           SELECT 1
           FROM   wsh_delivery_details
           WHERE  source_code = 'OE'
           AND    released_status = 'C'
           AND    source_line_id = l.line_id
           AND    Nvl(oe_interfaced_flag,'N') = 'N'
           AND    Nvl(inv_interfaced_flag,'N') = 'N'
    );

Query to get Order line, Delivery Details , Delivery assocaited with a Trip(Not yet Interfaced)

Below is Query to get Order line, Delivery Details , Delivery associated with a Trip , that is not yet interfaced.

Select L.Line_Id,l.Shipped_Quantity,l.flow_status_code,wnd.delivery_id
From Oe_Order_Lines_All L,
Wsh_Delivery_Details Wdd,
Wsh_New_Deliveries Wnd,
wsh_delivery_assignments wda,
Wsh_Delivery_Legs Wdl,
wsh_trip_stops wts
Where L.Line_Id = Wdd.Source_Line_Id
And L.Open_Flag = 'Y'
And Nvl(L.Shipped_Quantity,0) = 0
and nvl(l.fulfilled_quantity,0)= 0
And Wdd.Delivery_Detail_Id = Wda.Delivery_Detail_Id
and wda.delivery_id = wnd.delivery_id
and wnd.delivery_id = wdl.delivery_id
And Wdl.Pick_Up_Stop_Id = Wts.Stop_Id
And Nvl(Wts.Pending_Interface_Flag,'X') = 'Y'
And Wnd.Status_Code <> 'OP'
and wts.stop_id = &Enter_Trip_id
And Wdd.Source_Code='OE'
And Wdd.Released_Status = 'C'
And Nvl(Wdd.Oe_Interfaced_Flag,'N')='N'
And Nvl(Wdd.Inv_Interfaced_Flag,'N')='N'