Forum Stats

  • 3,750,525 Users
  • 2,250,188 Discussions
  • 7,866,998 Comments

Discussions

Can not query custom view from a custom Oracle form

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