OPENING QTY IN INVENTORY

SELECT   NVL (SUM (mmt.transaction_quantity), 0) AS stock , mmt.inventory_item_id
    FROM   apps.mtl_system_items_b msib,
           apps.mtl_material_transactions mmt,
           apps.rcv_transactions rt
   WHERE   msib.inventory_item_id = mmt.inventory_item_id
           AND msib.organization_id = mmt.organization_id
           AND msib.inventory_item_id BETWEEN NVL (:P_Seg, 61674)
            AND  NVL (:P_Seg, 61676) --= NVL (p_seg, msib.inventory_item_id)
           AND rt.transaction_id(+) = mmt.transaction_id
           AND msib.organization_id = 104
           --and mmt.transaction_id = j.trancation_id
           AND mmt.transaction_date < :p_from_date
           AND transaction_action_id NOT IN (24, 30)
           AND (logical_transaction = 2 OR logical_transaction IS NULL)
group by mmt.inventory_item_id 

6AM TO 6AM DATE FORMAT

 AND (mmt.transaction_date) BETWEEN TO_DATE (
                                                                                           TO_CHAR (
                                                                                              :P_From_date,
                                                                                              'fmdd/mon/yyyy'
                                                                                           )
                                                                                           || ' '
                                                                                           || '06:00:00',
                                                                                           'fmdd/mm/yyyy hh24:mi:ss'
                                                                                        )
                                                                                    AND  TO_DATE (
                                                                                            TO_CHAR (
                                                                                               :P_TO_DATE
                                                                                               + 1,
                                                                                               'fmdd/mm/yyyy'
                                                                                            )
                                                                                            || ' '
                                                                                            || '05:59:59',
                                                                                            'fmdd/mm/yyyy hh24:mi:ss'
                                                                                         )

FINANCIAL YEAR DATE_FORMAT LIKE FROM_DATE (APR-13) AND TO_DATE(AUG-13)

TRUNC (FFIC.TRANSACTION_DATE_ENTERED) BETWEEN TO_DATE (
                                                              TO_CHAR (
                                                                 TO_DATE (
                                                                    :FROM_DATE,
                                                                    'MON-RR'
                                                                 ),
                                                                 'MON-rr'
                                                              ),
                                                              'MON-rr'
                                                           )
                                                       AND  TO_DATE (
                                                               TO_CHAR (
                                                                  TO_DATE (
                                                                     :DATE_TO,
                                                                     'MON-RR'
                                                                  ),
                                                                  'MON-rr'
                                                               ),
                                                               'MON-rr'
                                                            )

6 AM TO 6 AM FOR A DAY (FINANCIAL YEAR )

AND jowl.creation_date
                                                                   BETWEEN   TRUNC
                                                                                (TO_DATE
                                                                                    (t_date
                                                                                    ),
                                                                                 'DD'
                                                                                )
                                                                           + (  6
                                                                              / 24
                                                                             )
                                                                       AND   TRUNC
                                                                                (TO_DATE
                                                                                    (t_date
                                                                                    ),
                                                                                 'dd'
                                                                                )
                                                                           + (  359.99
                                                                              / 1440
                                                                             )
                                                                           + 1

6 AM TO 6 AM (FOR AN MONTH ) LIKE MONTH WISE (FINANCIAL YEAR)

AND jowl.creation_date
                                                                   BETWEEN   TRUNC
                                                                                (TO_DATE
                                                                                    (t_date
                                                                                    ),
                                                                                 'mm'
                                                                                )
                                                                           + (  6
                                                                              / 24
                                                                             )
                                                                       AND   TRUNC
                                                                                (TO_DATE
                                                                                    (t_date
                                                                                    ),
                                                                                 'dd'
                                                                                )
                                                                           + (  359.99
                                                                              / 1440
                                                                             )
                                                                           + 1

6 AM TO 6 AM FOR THE FINANCIAL YEAR (FOR AN YEAR) 01-APR-2013 TO 31-MAR-2014

 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

DISPATCH QUERY FOR BRANCH 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, 'Branch 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.Oe_Order_Headers_All Oh,
                                           Apps.Po_Requisition_Headers_All Prh,
                                           Apps.Po_Requisition_Lines_All Prl,
                                           Apps.Po_Req_Distributions_All Prd,
                                           Apps.Rcv_Transactions Rt,
                                           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
                                     Where
                                           --Oh.Order_Number = 32012003611
                                           Oh.Orig_Sys_Document_Ref =
                                                                  Prh.Segment1
                                       And Prh.Requisition_Header_Id =
                                                     Prl.Requisition_Header_Id
--And Prd.Requistion_Header_Id = Prl.Requisition_Header_Id
                                       And Prd.Requisition_Line_Id =
                                                       Prl.Requisition_Line_Id
                                       And Rt.Req_Distribution_Id =
                                                           Prd.Distribution_Id
                                       And Rsh.Shipment_Header_Id =
                                                         Rt.Shipment_Header_Id
                                       And Rt.Transaction_Type = 'DELIVER'
                                       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 Ott.Name Like 'Stock Transfer'
--                                       And Mc.Segment1 =
--                                                Nvl (:P_Category, Mc.Segment1)
                                       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 Prl.Destination_Organization_Id =
                                                                       Orgn_Id)
                          Group By Typ, Machine1, Machine2, Machine3)
                Group By Typ, Machine1, Machine2, Machine3
                Union All
                Select 'Branch Return' Typ, 0 Machine1, 0 Machine2,
                       0 Machine3, 0 Mach1, 0 Mach2, 0 Mach3
                  From Dual)

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