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'

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'

Behaviour Of Item Type Code in Order Management

This Post is about How Order Management Process various type of Items, For example

1. Standard
2. ATO - Model
3. PTO - Model
4. KIT  etc

Scope - Here I  want to highlight how and what Quantity fields

• Shipping Quantity
• Shipped Quantity
• Fulfill Quantity
• Invoiced Quantity 

populated during the Booking/Shipping and Invoicing Process.

Item Type Code = PTO Model

For PTO Model
#1 We SHip Option and Included items.
#2 Items with item type code Model/Class or not Shippable
#3 Shipped/Fulfilled quantity populated for all items.
#4 Shipping Qty Populated for Option/Included, Shipping qty is qty that we physiacally shipped from Shipping Module
#5 we Invoice Model and Option

Item Type Code = ATO Model

Retain Unstaged Quantity during Over Shipment or Under Shipment (R12i feature)

Oracle Shipping has offered another New Long awaited feature in R12. Now in R12 you can Retain Unstaged Quantity during over shipment or under shipment.

To enable this new feature in R12i, oracle has introduced below to additional Setup.

1. Introduce a new  field(check cox)‘Retain Unstaged quantity during Over pick’  on Shipping Parameters form. To enable this feature navigate to Shipping Parameter UI and check this check box.
2. Oracle has introduced a new custom hook  WSH_CUSTOM_PUB.Cancel_Unpicked_Details_At_ITS  and to enable this feature make sure it return a value of ‘N’.
N – Do not cancel the unpicked quantity
Y – cancel the unpicked quantity.

Please refer my previous post on R11i behavior (In r11i . system cancelled the unpicked quantity for over/under shipment if tolerance met).

In this Post I will explain how System will Retain Unstaged Quantity during over shipment or under shipment even when tolerance met. This is little hard to explain in words, so let me explain that with 2 simple examples, One for Under Shipment and one for Over Shipment.

Under Shipment
1.    If you have Order quantity = 10 and Over /Under Tolerance is say 20%.
2.    Book the Sales Order.
3.    Query the Order in shipping transaction Form.
4.    Manual Split the Delivery details with Qty 9 and 1
5.    Pick Release and Ship Confirm the Delivery Details with qty 9.
6.    After ITS execution for delivery with Qty = 9, after successful execution of ITS system will create a New Order with qty = 1 (even though Tolerance of 20% easily met.)

Over Shipment

Parallel Pick Release in Shipping ( R12 New Feature)

Oracle has introduced lots of Interesting features in R12 (e-business Suite).In this post I will talk about the one such feature.
Parallel Pick Release
With this feature user can now run the Pick Release processes in parallel and improve the over all performance.

Advantages
One of Key Advantage of with Parallel Processing of the Pick release process is user can apply /engage additional (if available) resources to process the Pick release data that was previously process by 1 process only and hence save lot of time by submitting this process in Parallel.

Following are the changes made for this feature –

1. A new Parameter (No. Of Child Processes) has introduced in Pick release Concurrent Program.
2. A new profile option (WSH: No. Of pick release child processes) has introduced , it indicate how many child processes you want to spawn.

Shipping Purge in Oracle e business Suite (R12 - New Feature)

In R12 E-business Suite Oracle has introduced another new feature to Purge Shipping Execution (WSH) data.
(Oracle Apps)

In this Post I will explain the various feature offered in Oracle Shipping (e-business ).
Shipping Purge allow us to purge Shipping data for

•  Delivery
•  Trip
•  Delivery Lines
•  LPN

For purge, Oracle in R12 (e-business suite) has added a new concurrent program “Shipping Purge”, It has lot of parameters and allow you to purge shipping data based on parameters.

Some of parameters for the concurrent Program.

Workflow in Oracle Shipping Execution (R12 New feature)

Oracle has offered many new features in R12.In this post I will talk about the Introduction of the workflow in Oracle Application Shipping Module offered in R12.

In R12 Oracle Shipping (WSH) is utilizing the workflow.

Advantages of Workflow

• Automate the Shipping business process by adding them in the Shipping workflow.
• Routing based on the business rules.
• Send the Notification to user about some Important event, e.g sending notification about
• Backorder deliver
• Dollar Amount or Volume in Delivery etc
• Best of all now with workflow user can visualize the Shipping process, by means of workflow monitor and thus improve our visibility.
• Integration with the 3rd party APIs will be easy , we can just add them where every they requited by customizing the workflow.