Oracle Order Management DropShip Flow for R12

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.

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.

 Some queries involved in O2C

Select  count(*)
from OE_ORDER_HEADERS_ALL          ORD,
OE_TRANSACTION_TYPES_TL       TYP,
FND_LANGUAGES                 FLA
where TYP.LANGUAGE                  = FLA.LANGUAGE_CODE
and  ORD.ORDER_TYPE_ID             = TYP.TRANSACTION_TYPE_ID;

select count(*)
from OE_ORDER_LINES_ALL                LIN,
MTL_SYSTEM_ITEMS              ITM
where LIN.SHIP_FROM_ORG_ID           = ITM.ORGANIZATION_ID
and  LIN.INVENTORY_ITEM_ID          = ITM.INVENTORY_ITEM_ID;

select COUNT(*)
from OE_SETS                       ST1,
OE_LINE_SETS                  LST
where ST1.SET_ID              = LST.SET_ID;

select COUNT(*)
from OE_ORDER_HEADERS                  ORD,
MTL_PARAMETERS                    PAR,
OE_TRANSACTION_TYPES_V            TYP,
AR_CUSTOMERS                      CUS,
OE_SHIP_TO_ORGS_V                 SHP,
QP_LIST_HEADERS_V                 PRC
where ORD.SHIP_FROM_ORG_ID          = PAR.ORGANIZATION_ID
and  ORD.ORDER_TYPE_ID             = TYP.TRANSACTION_TYPE_ID
and  SHP.SITE_USE_ID               = ORD.SHIP_TO_ORG_ID
and  ORD.SOLD_TO_ORG_ID            = CUS.CUSTOMER_ID
and  ORD.PRICE_LIST_ID             = PRC.LIST_HEADER_ID;

SELECT   COUNT(*)
from OE_ORDER_HOLDS_ALL    HLD,
OE_HOLD_SOURCES_ALL   HSR,
OE_HOLD_DEFINITIONS   HDF,
OE_HOLD_RELEASES      HRL
where   HLD.HOLD_SOURCE_ID       = HSR.HOLD_SOURCE_ID
and  HSR.HOLD_ID         = HDF.HOLD_ID
and  HLD.HOLD_RELEASE_ID = HRL.HOLD_RELEASE_ID;

select COUNT(*)
from oe_processing_msgs_vl     MSG,
oe_processing_msgs_tl     MST,
fnd_languages             FLA
where  msg.transaction_id = mst.transaction_id
and     MST.LANGUAGE       = FLA.LANGUAGE_CODE;

select  COUNT(*)
from MTL_SALES_ORDERS              MSO,
OE_ORDER_HEADERS              ORD,
OE_TRANSACTION_TYPES_VL       TYP
where ORD.ORDER_TYPE_ID             = TYP.TRANSACTION_TYPE_ID
and  TO_CHAR(ORD.ORDER_NUMBER)     = MSO.SEGMENT1
and  TYP.NAME                      = MSO.SEGMENT2;

select COUNT(*)
from MTL_SALES_ORDERS              MSO,
OE_ORDER_HEADERS              ORD,
OE_TRANSACTION_TYPES_TL       TYP,
FND_LANGUAGES                 FLA
where ORD.ORDER_TYPE_ID             = TYP.TRANSACTION_TYPE_ID
and  TYP.LANGUAGE                  = FLA.LANGUAGE_CODE
and  TYP.NAME                      = MSO.SEGMENT2;

select COUNT(*)
from OE_ORDER_LINES                LIN,
OE_TRANSACTION_TYPES          TYP,
MTL_SYSTEM_ITEMS              ITM,
MTL_PARAMETERS                PAR,
OE_SETS                       ST1
where LIN.LINE_TYPE_ID               = TYP.TRANSACTION_TYPE_ID
and  LIN.SHIP_FROM_ORG_ID           = PAR.ORGANIZATION_ID
and     LIN.SHIP_FROM_ORG_ID           = ITM.ORGANIZATION_ID
and     LIN.INVENTORY_ITEM_ID          = ITM.INVENTORY_ITEM_ID
and     LIN.ARRIVAL_SET_ID             = ST1.SET_ID;



select COUNT(*)
from oe_sets os,
oe_line_sets ols
where os.set_id = ols.set_id;

SELECT COUNT(*)
from OE_ORDER_HOLDS_ALL    HLD,
OE_HOLD_SOURCES_ALL   HSR,
OE_HOLD_DEFINITIONS   HDF,
OE_HOLD_RELEASES      HRL,
OE_ORDER_LINES_ALL    LIN
where HLD.LINE_ID               = LIN.LINE_ID
and  HLD.HOLD_SOURCE_ID       = HSR.HOLD_SOURCE_ID
and  HSR.HOLD_ID              = HDF.HOLD_ID
and  HLD.HOLD_RELEASE_ID      = HRL.HOLD_RELEASE_ID;

select   COUNT(*)
from oe_processing_msgs_vl     MSG,
oe_processing_msgs_tl     MST,
oe_order_lines_all        LIN,
fnd_languages             FLA
where MSG.HEADER_ID      = LIN.HEADER_ID
and  msg.transaction_id = mst.transaction_id
and  MST.LANGUAGE                  = FLA.LANGUAGE_CODE
and  MSG.LINE_ID        = LIN.LINE_ID;

select COUNT(*)
from OE_PRICE_ADJUSTMENTS   ADJ,
OE_ORDER_LINES_ALL     LIN
where  ADJ.LINE_ID   = LIN.LINE_ID;

select COUNT(*)
FROM OE_DROP_SHIP_SOURCES    SRC,
OE_ORDER_LINES          LIN,
MTL_PARAMETERS          PAR
WHERE SRC.LINE_ID                      = LIN.LINE_ID
and  SRC.DESTINATION_ORGANIZATION_ID  = PAR.ORGANIZATION_ID;

Select COUNT(*)
from  PO_REQUISITIONS_INTERFACE_ALL   RQI,
OE_DROP_SHIP_SOURCES            SRC,
OE_ORDER_LINES                  LIN
where  SRC.LINE_ID                      = LIN.LINE_ID
and  SRC.DROP_SHIP_SOURCE_ID          = RQI.INTERFACE_SOURCE_LINE_ID;

select COUNT(*)
from PO_INTERFACE_ERRORS             POE,
OE_DROP_SHIP_SOURCES            SRC,
OE_ORDER_LINES                  LIN,
PO_REQUISITIONS_INTERFACE_ALL   RQI
where SRC.LINE_ID                      = LIN.LINE_ID
and  SRC.DROP_SHIP_SOURCE_ID          = RQI.INTERFACE_SOURCE_LINE_ID
and  RQI.TRANSACTION_ID               = POE.INTERFACE_TRANSACTION_ID;

select COUNT(*)
from PO_REQUISITION_HEADERS_ALL      RQH,
OE_DROP_SHIP_SOURCES            SRC,
OE_ORDER_LINES                  LIN
where SRC.LINE_ID                      = LIN.LINE_ID
and SRC.REQUISITION_HEADER_ID         = RQH.REQUISITION_HEADER_ID;

select COUNT(*)
from PO_REQUISITION_HEADERS_ALL      RQH,
OE_ORDER_LINES                  LIN
where LIN.SOURCE_DOCUMENT_ID           = RQH.REQUISITION_HEADER_ID;

select COUNT(*)
from PO_REQUISITION_HEADERS_ALL      RQH,
MTL_RESERVATIONS                RES
where RES.SUPPLY_SOURCE_HEADER_ID   = RQH.REQUISITION_HEADER_ID;

select COUNT(*)     
from PO_REQUISITION_LINES_ALL         RQL,
OE_DROP_SHIP_SOURCES             SRC,
OE_ORDER_LINES                   LIN
where SRC.LINE_ID                      = LIN.LINE_ID
and  RQL.REQUISITION_LINE_ID          = SRC.REQUISITION_LINE_ID;


select  COUNT(*)  
from PO_REQUISITION_LINES_ALL         RQL,
OE_ORDER_LINES                   LIN
where LIN.SOURCE_DOCUMENT_LINE_ID      = RQL.REQUISITION_LINE_ID;

select COUNT(*)
from PO_HEADERS_INTERFACE            PHI,
PO_LINES_INTERFACE              PLI,
OE_DROP_SHIP_SOURCES            SRC,
OE_ORDER_LINES                  LIN
where SRC.LINE_ID                   = LIN.LINE_ID
and  SRC.REQUISITION_LINE_ID       = PLI.REQUISITION_LINE_ID
and  PHI.INTERFACE_HEADER_ID       = PLI.INTERFACE_HEADER_ID;

select COUNT(*)
from PO_LINES_INTERFACE              PLI,
OE_DROP_SHIP_SOURCES            SRC,
OE_ORDER_LINES                  LIN
where SRC.LINE_ID                      = LIN.LINE_ID
and  SRC.REQUISITION_LINE_ID          = PLI.REQUISITION_LINE_ID;

select COUNT(*)
from PO_INTERFACE_ERRORS             POE,
PO_HEADERS_INTERFACE            PHI,
PO_LINES_INTERFACE              PLI,
OE_DROP_SHIP_SOURCES            SRC,
OE_ORDER_LINES                  LIN
where SRC.LINE_ID                   = LIN.LINE_ID
and  SRC.REQUISITION_LINE_ID       = PLI.REQUISITION_LINE_ID
and  PHI.INTERFACE_HEADER_ID       = PLI.INTERFACE_HEADER_ID
and  (POE.INTERFACE_TRANSACTION_ID = PLI.INTERFACE_LINE_ID
or   POE.INTERFACE_TRANSACTION_ID = PHI.INTERFACE_HEADER_ID);

select COUNT(*)
from PO_HEADERS           POH,
OE_DROP_SHIP_SOURCES SRC,
OE_ORDER_LINES       LIN
where SRC.LINE_ID                   = LIN.LINE_ID
and  SRC.PO_HEADER_ID              = POH.PO_HEADER_ID;

select COUNT(*)
from PO_HEADERS           POH,
MTL_RESERVATIONS     RES
where RES.SUPPLY_SOURCE_HEADER_ID   = POH.PO_HEADER_ID;

select COUNT(*)
from PO_LINES             POL,
OE_DROP_SHIP_SOURCES SRC,
OE_ORDER_LINES       LIN
where SRC.LINE_ID                   = LIN.LINE_ID
and  SRC.PO_LINE_ID                = POL.PO_LINE_ID;

select  COUNT(*)
from RCV_SHIPMENT_LINES         SHL,
OE_DROP_SHIP_SOURCES       SRC,
OE_ORDER_LINES             LIN
where SRC.LINE_ID                   = LIN.LINE_ID
and  SRC.PO_LINE_ID                = SHL.PO_LINE_ID;

select COUNT(*)
from RCV_SHIPMENT_LINES             SHL,
OE_ORDER_LINES                 LIN
where SHL.OE_ORDER_LINE_ID          = LIN.LINE_ID;

select  COUNT(*)
from RCV_SHIPMENT_LINES         SHL,
MTL_RESERVATIONS           RES,
PO_HEADERS_ALL             POH
where RES.SUPPLY_SOURCE_HEADER_ID   = POH.PO_HEADER_ID        
and  POH.PO_HEADER_ID              = SHL.PO_HEADER_ID;

select COUNT(*)
from RCV_TRANSACTIONS RCV,
OE_DROP_SHIP_SOURCES SRC,
OE_ORDER_LINES       LIN
where SRC.LINE_ID                   = LIN.LINE_ID
and  SRC.PO_LINE_ID                = RCV.PO_LINE_ID;


select COUNT(*)
from RCV_TRANSACTIONS     RCV,
OE_ORDER_LINES       LIN
where RCV.OE_ORDER_LINE_ID          = LIN.LINE_ID;

select  COUNT(*)
from RCV_TRANSACTIONS           RCV,
MTL_RESERVATIONS           RES,
PO_HEADERS_ALL             POH
where RES.SUPPLY_SOURCE_HEADER_ID   = POH.PO_HEADER_ID        
and  POH.PO_HEADER_ID             = RCV.PO_HEADER_ID;

select  COUNT(*)
from RCV_TRANSACTIONS_INTERFACE RTI,
OE_DROP_SHIP_SOURCES       SRC,
OE_ORDER_LINES             LIN
where SRC.LINE_ID                   = LIN.LINE_ID
and  SRC.PO_LINE_ID                = RTI.PO_LINE_ID;

select COUNT(*)
from RCV_TRANSACTIONS_INTERFACE     RTI,
OE_ORDER_LINES                 LIN
where RTI.OE_ORDER_LINE_ID          = LIN.LINE_ID
RTI.REQUISITION_LINE_ID       = LIN.SOURCE_DOCUMENT_LINE_ID;

select  COUNT(*)
from MTL_SUPPLY           SUP,
OE_DROP_SHIP_SOURCES SRC,
OE_ORDER_LINES       LIN
where SRC.LINE_ID                   = LIN.LINE_ID
and  SRC.PO_LINE_ID                = SUP.PO_LINE_ID;

select COUNT(*)
from MTL_SUPPLY           SUP,
OE_ORDER_LINES       LIN
where LIN.SOURCE_DOCUMENT_LINE_ID       = SUP.REQ_LINE_ID;

select COUNT(*)
from MTL_RESERVATIONS              RES,
OE_ORDER_LINES                LIN,
MTL_SYSTEM_ITEMS              ITM,
WIP_ENTITIES                  WIP,
WIP_DISCRETE_JOBS_V           JOB
where RES.DEMAND_SOURCE_LINE_ID     = LIN.LINE_ID
and  RES.ORGANIZATION_ID           = ITM.ORGANIZATION_ID
and  RES.INVENTORY_ITEM_ID         = ITM.INVENTORY_ITEM_ID
and  RES.SUPPLY_SOURCE_HEADER_ID   = WIP.WIP_ENTITY_ID
and  WIP.WIP_ENTITY_ID             = JOB.WIP_ENTITY_ID;

select COUNT(*)
from WIP_JOB_SCHEDULE_INTERFACE WJS,
OE_ORDER_LINES_ALL         LIN
where WJS.SOURCE_LINE_ID      =  LIN.LINE_ID;

select COUNT(*)
from WIP_INTERFACE_ERRORS       WIE,
WIP_JOB_SCHEDULE_INTERFACE WJS,
OE_ORDER_LINES_ALL         LIN
where WIE.INTERFACE_ID        = WJS.INTERFACE_ID
and WJS.SOURCE_LINE_ID      =  LIN.LINE_ID;

select COUNT(*)
from WIP_DISCRETE_JOBS   WIP,
WIP_DISCRETE_JOBS_V WIV,
MTL_SYSTEM_ITEMS_B  ITM,
OE_ORDER_LINES_ALL  LIN
where WIP.WIP_ENTITY_ID       = WIV.WIP_ENTITY_ID
and WIP.PRIMARY_ITEM_ID     = ITM.INVENTORY_ITEM_ID
and WIP.ORGANIZATION_ID     = ITM.ORGANIZATION_ID
and WIP.SOURCE_LINE_ID      =  LIN.LINE_ID;

select COUNT(*)
from    wip_discrete_jobs  WIP,
OE_ORDER_LINES_ALL LIN,
wip_entities WIE,
mtl_material_transactions TRN,
mtl_system_items_b ITM,
mtl_unit_transactions UNT
where WIE.wip_entity_id              = WIP.wip_entity_id
and LIN.LINE_ID                    = WIP.source_line_id
and TRN.transaction_source_id      = WIP.wip_entity_id
and TRN.inventory_item_id          = ITM.inventory_item_id
and TRN.organization_id            = ITM.organization_id
and TRN.transaction_id             = UNT.transaction_id;

select COUNT(*)
FROM OE_ORDER_LINES                   LIN,
WSH_DELIVERY_DETAILS             DET,
WSH_NEW_DELIVERIES               DEL,
WSH_DELIVERY_LEGS                LEG,
WSH_TRIP_STOPS                   STP,
WSH_DELIVERY_ASSIGNMENTS         ASG
WHERE DEL.DELIVERY_ID                 = ASG.DELIVERY_ID AND
ASG.DELIVERY_DETAIL_ID          = DET.DELIVERY_DETAIL_ID AND
DET.SOURCE_LINE_ID              = LIN.LINE_ID AND
STP.STOP_ID                     = LEG.PICK_UP_STOP_ID AND
LEG.DELIVERY_ID                 = DEL.DELIVERY_ID;

select COUNT(*)
FROM WSH_TRIP_STOPS                   STP,
WSH_SRS_TRIP_STOPS_V             STP1,
WSH_TRIPS                        TRP
where STP.TRIP_ID                     = TRP.TRIP_ID AND
STP1.STOP_ID                    = STP.STOP_ID;

select COUNT(*)
from  OE_ORDER_LINES                   LIN,
WSH_DELIVERY_DETAILS             DET,
WSH_NEW_DELIVERIES               DEL,
WSH_DELIVERY_LEGS                LEG,
WSH_DELIVERY_ASSIGNMENTS         ASG
where DEL.DELIVERY_ID                 = ASG.DELIVERY_ID AND
ASG.DELIVERY_DETAIL_ID          = DET.DELIVERY_DETAIL_ID AND
DET.SOURCE_LINE_ID              = LIN.LINE_ID AND
LEG.DELIVERY_ID                 = DEL.DELIVERY_ID;

select COUNT(*)
FROM OE_ORDER_LINES                   LIN,
WSH_DELIVERY_DETAILS             DET,
WSH_NEW_DELIVERIES               DEL,
WSH_DELIVERY_LEGS                LEG,
WSH_TRIP_STOPS                   STP,
WSH_DELIVERY_ASSIGNMENTS         ASG,
WSH_TRIPS                        TRP
where DEL.DELIVERY_ID                 = ASG.DELIVERY_ID AND
ASG.DELIVERY_DETAIL_ID          = DET.DELIVERY_DETAIL_ID AND
DET.SOURCE_LINE_ID              = LIN.LINE_ID AND
STP.STOP_ID(+)                  = LEG.PICK_UP_STOP_ID AND
STP.TRIP_ID                     = TRP.TRIP_ID AND
LEG.DELIVERY_ID(+)              = DEL.DELIVERY_ID ;

select COUNT(*)
FROM OE_ORDER_LINES                   LIN,
WSH_DELIVERY_DETAILS             DET,
WSH_NEW_DELIVERIES               DEL,
WSH_DELIVERY_ASSIGNMENTS         ASG
where DEL.DELIVERY_ID                 = ASG.DELIVERY_ID AND
ASG.DELIVERY_DETAIL_ID          = DET.DELIVERY_DETAIL_ID AND
DET.SOURCE_LINE_ID              = LIN.LINE_ID;

select COUNT(*)
FROM OE_ORDER_LINES                   LIN,
WSH_DELIVERY_DETAILS             DET,
WSH_DELIVERY_ASSIGNMENTS         ASG
where ASG.DELIVERY_DETAIL_ID          = DET.DELIVERY_DETAIL_ID AND
DET.SOURCE_LINE_ID              = LIN.LINE_ID;

select COUNT(*)
FROM OE_ORDER_LINES                   LIN,
WSH_DELIVERY_DETAILS             DET,
WSH_NEW_DELIVERIES               DEL,
WSH_DELIVERY_LEGS                LEG,
WSH_TRIP_STOPS                   STP,
MTL_SYSTEM_ITEMS                 ITM,
WSH_DELIVERY_ASSIGNMENTS         ASG,
WSH_TRIPS                        TRP
where DEL.DELIVERY_ID(+)              = ASG.DELIVERY_ID AND
ASG.DELIVERY_DETAIL_ID          = DET.DELIVERY_DETAIL_ID AND
DET.SOURCE_LINE_ID              = LIN.LINE_ID AND
STP.STOP_ID(+)                  = LEG.PICK_UP_STOP_ID AND
STP.TRIP_ID                     = TRP.TRIP_ID(+) AND
LEG.DELIVERY_ID(+)              = DEL.DELIVERY_ID AND
LIN.SHIP_FROM_ORG_ID            = ITM.ORGANIZATION_ID(+) AND
LIN.INVENTORY_ITEM_ID           = ITM.INVENTORY_ITEM_ID(+);

select COUNT(*)
FROM OE_ORDER_LINES                   LIN,
WSH_DELIVERY_DETAILS             DET,
WSH_NEW_DELIVERIES               DEL,
WSH_DELIVERY_LEGS                LEG,
WSH_TRIP_STOPS                   STP,
MTL_SYSTEM_ITEMS                 ITM,
WSH_DELIVERY_ASSIGNMENTS         ASG,
WSH_TRIPS                        TRP
where DEL.DELIVERY_ID(+)              = ASG.DELIVERY_ID AND
ASG.DELIVERY_DETAIL_ID          = DET.DELIVERY_DETAIL_ID AND
DET.ORGANIZATION_ID             = ITM.ORGANIZATION_ID(+) AND
DET.INVENTORY_ITEM_ID           = ITM.INVENTORY_ITEM_ID(+) AND
DET.SOURCE_LINE_ID              = LIN.LINE_ID AND
STP.STOP_ID(+)                  = LEG.PICK_UP_STOP_ID AND
STP.TRIP_ID                     = TRP.TRIP_ID(+) AND
LEG.DELIVERY_ID(+)              = ASG.DELIVERY_ID AND
LIN.SHIP_FROM_ORG_ID            = ITM.ORGANIZATION_ID(+) AND
LIN.INVENTORY_ITEM_ID           = ITM.INVENTORY_ITEM_ID(+) ;

select COUNT(*)
FROM OE_ORDER_LINES                   LIN,
WSH_DELIVERY_DETAILS             DET,
WSH_SERIAL_NUMBERS               WSN
where DET.DELIVERY_DETAIL_ID          = WSN.DELIVERY_DETAIL_ID AND
DET.SOURCE_LINE_ID              = LIN.LINE_ID;

select COUNT(*)
from wsh_freight_costs_v              wfc,
wsh_freight_cost_types           wft,
fnd_lookup_values                lkp,
OE_ORDER_LINES                   LIN,
WSH_DELIVERY_DETAILS             DET
WHERE wfc.freight_cost_type_id      = wft.freight_cost_type_id AND
wft.freight_cost_type_code    = lkp.lookup_code AND
lkp.lookup_type               = 'FREIGHT_COST_TYPE' AND
DET.SOURCE_LINE_ID            = LIN.LINE_ID;


select COUNT(*)
from wsh_freight_costs_v              wfc,
wsh_freight_cost_types           wft,
fnd_lookup_values                lkp,
OE_ORDER_LINES                   LIN,
WSH_DELIVERY_DETAILS             DET,
WSH_NEW_DELIVERIES               DEL,
WSH_DELIVERY_LEGS                LEG,
WSH_TRIP_STOPS                   STP,
WSH_DELIVERY_ASSIGNMENTS         ASG
WHERE wfc.freight_cost_type_id      = wft.freight_cost_type_id AND
wft.freight_cost_type_code    = lkp.lookup_code AND
lkp.lookup_type               = 'FREIGHT_COST_TYPE' AND
DEL.DELIVERY_ID               = ASG.DELIVERY_ID AND
ASG.DELIVERY_DETAIL_ID        = DET.DELIVERY_DETAIL_ID AND
DET.SOURCE_LINE_ID            = LIN.LINE_ID AND
STP.STOP_ID                   = LEG.PICK_UP_STOP_ID AND
LEG.DELIVERY_ID               = DEL.DELIVERY_ID;

select COUNT(*)
from wsh_freight_costs_v              wfc,
wsh_freight_cost_types           wft,
fnd_lookup_values                lkp,
OE_ORDER_LINES                   LIN,
WSH_DELIVERY_DETAILS             DET,
WSH_NEW_DELIVERIES               DEL,
WSH_DELIVERY_LEGS                LEG,
WSH_TRIP_STOPS                   STP,
WSH_DELIVERY_ASSIGNMENTS         ASG
WHERE wfc.freight_cost_type_id      = wft.freight_cost_type_id AND
wft.freight_cost_type_code    = lkp.lookup_code AND
lkp.lookup_type               = 'FREIGHT_COST_TYPE' AND
DEL.DELIVERY_ID               = ASG.DELIVERY_ID AND
ASG.DELIVERY_DETAIL_ID        = DET.DELIVERY_DETAIL_ID AND
DET.SOURCE_LINE_ID            = LIN.LINE_ID AND
STP.STOP_ID                   = LEG.PICK_UP_STOP_ID AND
LEG.DELIVERY_ID               = DEL.DELIVERY_ID;

select COUNT(*)     
FROM RA_INTERFACE_LINES            RAI,
OE_ORDER_LINES                LIN,
OE_ORDER_HEADERS              ORD,
OE_TRANSACTION_TYPES_V        TYP,
OE_PRICE_ADJUSTMENTS          ADJ
WHERE RAI.SALES_ORDER = to_char(ORD.ORDER_NUMBER)
and  RAI.INTERFACE_LINE_ATTRIBUTE2 = TYP.NAME
and  TYP.TRANSACTION_TYPE_ID       = ORD.ORDER_TYPE_ID
and  ORD.HEADER_ID                 = LIN.HEADER_ID
and  to_number(RAI.INTERFACE_LINE_ATTRIBUTE6) = ADJ.PRICE_ADJUSTMENT_ID
and  RAI.LINE_TYPE                 = 'LINE';

select COUNT(*)
FROM RA_CUSTOMER_TRX               RAH,
RA_CUSTOMER_TRX_LINES         RAL,
OE_ORDER_LINES                LIN,
OE_ORDER_HEADERS              ORD,
OE_TRANSACTION_TYPES_V        TYP
WHERE RAH.CUSTOMER_TRX_ID           = RAL.CUSTOMER_TRX_ID
and  RAL.SALES_ORDER               = to_char(ORD.ORDER_NUMBER)
and  RAL.INTERFACE_LINE_ATTRIBUTE2 = TYP.NAME
and  TYP.TRANSACTION_TYPE_ID       = ORD.ORDER_TYPE_ID;