TRANSACTION HISTORICAL SUMMARY REPORT ON HAND QTY

                    TRANSACTION HISTORICAL SUMMARY REPORT ON HAND QTY


SELECT   SUM (mmt.primary_quantity)
--  INTO   i
  FROM   apps.mtl_transaction_types mtt,
         apps.MTL_MATERIAL_TRANSACTIONS mmt,
         apps.mtl_system_items msi
 WHERE       mtt.TRANSACTION_TYPE_ID = mmt.TRANSACTION_TYPE_ID
         AND mmt.organization_id = msi.organization_id
         AND mmt.inventory_item_id = msi.inventory_item_id
         AND (mmt.organization_id = 109)
         AND msi.segment1 = 'PLPCBWLAPI'
         AND TRUNC (MMT.transaction_date) <= TO_DATE (:P_FROM_DATE);

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