0 Replies Latest reply on Apr 16, 2019 7:40 PM by SomnathBPI

    Can not query custom view from a custom Oracle form

    SomnathBPI

      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