DISPATCH QUERY FOR SALES RETURN (ORDER MANAGEMENT & INVENTORY)

Select   Typ, Machine1, Machine2, Machine3, Sum (Mach1) Mach1,
                         Sum (Mach2) Mach2, Sum (Mach3) Mach3
                    From ((Select   Typ, Machine1, Machine2, Machine3,
                                    Sum (Mc1) Mach1, Sum (Mc2) Mach2,
                                    Sum (Mc3) Mach3
                               From (Select Ott.Name, 'Sales Return' Typ,
                                            0 Machine1, 0 Machine2,
                                            0 Machine3,
                                            Abs
                                               ((Case
                                                    When Substr (Msi.Segment1,
                                                                 2,
                                                                 1
                                                                ) = '1'
                                                       Then (Nvl
                                                                (Rt.Primary_Quantity,
                                                                 0
                                                                )
                                                            )
                                                    Else 0
                                                 End
                                                )
                                               ) Mc1,
                                            Abs
                                               ((Case
                                                    When Substr (Msi.Segment1,
                                                                 2,
                                                                 1
                                                                ) = '2'
                                                       Then (Nvl
                                                                (Rt.Primary_Quantity,
                                                                 0
                                                                )
                                                            )
                                                    Else 0
                                                 End
                                                )
                                               ) Mc2,
                                            Abs
                                               ((Case
                                                    When Substr (Msi.Segment1,
                                                                 2,
                                                                 1
                                                                ) = '3'
                                                       Then (Nvl
                                                                (Rt.Primary_Quantity,
                                                                 0
                                                                )
                                                            )
                                                    Else 0
                                                 End
                                                )
                                               ) Mc3
                                       From Apps.Rcv_Transactions Rt,
                                            Apps.Oe_Order_Headers_All Oh,
                                            Apps.Oe_Order_Lines_All Ol,
                                            Apps.Rcv_Shipment_Headers Rsh,
                                            Apps.Rcv_Shipment_Lines Rsl,
                                            Apps.Mtl_System_Items_B Msi,
                                            Apps.Mtl_Item_Categories Mic,
                                            Apps.Mtl_Categories Mc,
                                            Apps.Oe_Transaction_Types_Tl Ott
--Apps.Jai_Om_Wsh_Lines_All Jowl
                                     Where
                                            --Rt.Oe_Order_Header_Id = 49231
                                            Transaction_Type = 'DELIVER'
                                        And Rt.Oe_Order_Header_Id =
                                                                  Ol.Header_Id
                                        And Rt.Oe_Order_Line_Id = Ol.Line_Id
                                        And Oh.Header_Id = Ol.Header_Id
                                        And Rsh.Shipment_Header_Id =
                                                         Rt.Shipment_Header_Id
                                        And Rsl.Shipment_Line_Id =
                                                           Rt.Shipment_Line_Id
                                        And Msi.Organization_Id =
                                                            Rt.Organization_Id
                                        And Msi.Inventory_Item_Id =
                                                                   Rsl.Item_Id
                                        And Mic.Inventory_Item_Id =
                                                                   Rsl.Item_Id
                                        And Mic.Organization_Id =
                                                            Rt.Organization_Id
                                        And Mic.Category_Set_Id = 5
                                        And Mic.Category_Id = Mc.Category_Id(+)
                                        And Ott.Transaction_Type_Id =
                                                              Oh.Order_Type_Id
                                        And Mc.Segment1 Not In
                                                          ('NB', 'NA', 'COM')
                                        And Substr (Msi.Segment1, 2, 1) In
                                                              ('1', '2', '3')
--And Upper(Flv.Lookup_Code) = Upper(Ott.Name)
--                                        And Mc.Segment1 =
--                                                Nvl (:P_Category, Mc.Segment1)
                                        And Ott.Name = 'Sales Return'
                                        And Rsh.Creation_Date BETWEEN   TRUNC
                                       (TO_DATE
                                           (   '01'
                                            || '-'
                                            || 'APR'
                                            || '-'
                                            || TRUNC
                                                  (TO_NUMBER
                                                          (TO_CHAR
                                                                  (t_date,
                                                                   'YYYY'
                                                                  )
                                                          )
                                                  )
                                           ),
                                        'dd'
                                       )
                                  + (6 / 24)   --to_date (01, 'dd') + (6 / 24)
                              AND TRUNC (t_date, 'dd') + (6 / 24) + 1
                                        And Rt.Organization_Id = Orgn_Id)
                           Group By Typ, Machine1, Machine2, Machine3))
                Group By Typ, Machine1, Machine2, Machine3