Hi,
We've created 2 views and trying use those in a custom Form. But while executing the query on the form, Oracle returns error message no records retrieved. Though the System> last query can be executed successfully in Sql tools like Toad.
The blocks are defined as Insert, Update, Delete disabled. We've tried creating instead of triggers on the views but it did not resolve the issue.
This is first time we're trying this particular Oracle instance.
Views are:
Create Or Replace View Xxagmx_Delivery_Details_V As
Select Wts.Trip_Id,
Wda.Delivery_Id,
Wdd.Delivery_Detail_Id,
Decode (Wdb.Container_Type_Code,
'PALLET', Wdb.Container_Name,
Decode (Wdp.Container_Type_Code,
'PALLET', Wdp.Container_Name,
Null
)
) Pallet_Name,
Decode (Wdb.Container_Type_Code,
'PALLET', Wdb.Gross_Weight,
Decode (Wdp.Container_Type_Code,
'PALLET', Wdp.Gross_Weight,
Null
)
) Pallet_Gross_Weight,
Decode (Wdb.Container_Type_Code,
'PALLET', Wdb.Net_Weight,
Decode (Wdp.Container_Type_Code,
'PALLET', Wdp.Net_Weight,
Null
)
) Pallet_Net_Weight,
Decode (Wdb.Container_Type_Code,
'PALLET', Wdb.Weight_Uom_Code,
Decode (Wdp.Container_Type_Code,
'PALLET', Wdp.Weight_Uom_Code,
Null
)
) Pallet_Weight_Uom_Code,
Decode (Wdb.Container_Type_Code,
'BOX', Wdb.Container_Name,
Decode (Wdp.Container_Type_Code,
'BOX', Wdp.Container_Name,
Null
)
) Box_Name,
Decode (Wdb.Container_Type_Code,
'BOX', Wdb.Gross_Weight,
Decode (Wdp.Container_Type_Code,
'BOX', Wdp.Gross_Weight,
Null
)
) Box_Gross_Weight,
Decode (Wdb.Container_Type_Code,
'BOX', Wdb.Net_Weight,
Decode (Wdp.Container_Type_Code,
'BOX', Wdp.Net_Weight,
Null
)
) Box_Net_Weight,
Decode (Wdb.Container_Type_Code,
'BOX', Wdb.Weight_Uom_Code,
Decode (Wdp.Container_Type_Code,
'BOX', Wdp.Weight_Uom_Code,
Null
)
) Box_Weight_Uom_Code,
Wdd.Source_Header_Number,
Wdd.Source_Header_Id,
Wdd.Source_Line_Id,
Wdd.Cust_Po_Number,
Wdd.Ship_From_Location_Id,
Ola.Customer_Number||' : '||Ola.Sold_To Customer,
Ola.Ship_To_Location||' : '||Ola.Ship_To_Address1||' '||
Ola.Ship_To_Address2||' '||
Ola.Ship_To_Address3||' '||
Ola.Ship_To_Address4||' '||
Ola.Ship_To_Address5 Ship_To,
Wdd.Inventory_Item_Id,
Msib.Segment1 Item,
Wdd.Item_Description,
Wdd.Shipped_Quantity,
Wdd.Requested_Quantity_Uom,
Wdd.Net_Weight,
Wdd.Weight_Uom_Code
From Oe_Order_Lines_V Ola,
Wsh_Deliverables_V Wdd,
Wsh_Delivery_Assignments Wda,
Wsh_Delivery_Details Wdb,
Wsh_Delivery_Assignments Wdab,
Wsh_Delivery_Details Wdp,
Wsh_Trip_Stops Wts,
Wsh_Delivery_Legs Wdl,
Mtl_System_Items_B Msib
Where Wda.Delivery_Detail_Id = Wdd.Delivery_Detail_Id
And Ola.Line_Id = Wdd.Source_Line_Id
And Wdb.Delivery_Detail_Id(+) = Wda.Parent_Delivery_Detail_Id
And Wdab.Delivery_Detail_Id(+) = Wdb.Delivery_Detail_Id
And Wdp.Delivery_Detail_Id(+) = Wdab.Parent_Delivery_Detail_Id
And Wdl.Drop_Off_Stop_Id = Wts.Stop_Id
And Wda.Delivery_Id = Wdl.Delivery_Id
And Msib.Inventory_Item_Id = Wdd.Inventory_Item_Id
And Msib.Organization_Id = Wdd.Organization_Id
And Wdd.Source_Code = 'OE';
Create Or Replace View Xxagmx_Deliveries_V
As
Select Trip_Id,
Delivery_Id,
Customer,
Ship_To,
Sum(Shipped_Quantity) Shipped_Quantity,
Count(Distinct Inventory_Item_Id) Item_Count,
Sum(Net_Weight) Net_Weight,
Min(Weight_Uom_Code)Weight_Uom_Code
From Xxagmx_Delivery_Details_V
Group By
Trip_Id,
Delivery_Id,
Customer,
Ship_To;
Any advice will be helpful.
Regards,
Somnath