Inventory Aging Report as ON Stock

/* Formatted on 2014/12/16 10:51 (Formatter Plus v4.8.8) */
SELECT   NAME, organization_name, item_code, item_type, uom,
         subinventory_code, description, inventory_item_id, organization_id,
         item_cost, on_hand_quantity, CASE
            WHEN b1 < 0
               THEN 0
            ELSE b1
         END days, CASE
            WHEN b2 < 0
               THEN 0
            ELSE b2
         END days1, CASE
            WHEN b3 < 0
               THEN 0
            ELSE b3
         END days2, CASE
            WHEN b4 < 0
               THEN 0
            ELSE b4
         END days3, CASE
            WHEN b5 < 0
               THEN 0
            ELSE b5
         END days4, CASE
            WHEN b6 < 0
               THEN 0
            ELSE b6
         END days5, CASE
            WHEN b7 < 0
               THEN 0
            ELSE b7
         END days6, total
    FROM (SELECT NAME, organization_name, item_code, item_type, uom,
                 subinventory_code, description, inventory_item_id,
                 organization_id, item_cost, on_hand_quantity, issued_qty,
                 (CASE
                     WHEN (  bucket2
                           + bucket3
                           + bucket4
                           + bucket5
                           + bucket6
                           + bucket7
                           + issued_qty
                          ) < 0
                        THEN (  bucket1
                              + bucket2
                              + bucket3
                              + bucket4
                              + bucket5
                              + bucket6
                              + bucket7
                              + issued_qty
                             )
                     ELSE bucket1
                  END
                 ) b1,
                 (CASE
                     WHEN (  bucket3
                           + bucket4
                           + bucket5
                           + bucket6
                           + bucket7
                           + issued_qty
                          ) < 0
                        THEN (  bucket2
                              + bucket3
                              + bucket4
                              + bucket5
                              + bucket6
                              + bucket7
                              + issued_qty
                             )
                     ELSE bucket2
                  END
                 ) b2,
                 (CASE
                     WHEN (bucket4 + bucket5 + bucket6 + bucket7 + issued_qty
                          ) < 0
                        THEN (  bucket3
                              + bucket4
                              + bucket5
                              + bucket6
                              + bucket7
                              + issued_qty
                             )
                     ELSE bucket3
                  END
                 ) b3,
                 (CASE
                     WHEN (bucket5 + bucket6 + bucket7 + issued_qty) < 0
                        THEN (bucket4 + bucket5 + bucket6 + bucket7
                              + issued_qty
                             )
                     ELSE bucket4
                  END
                 ) b4,
                 (CASE
                     WHEN (bucket6 + bucket7 + issued_qty) < 0
                        THEN (bucket5 + bucket6 + bucket7 + issued_qty)
                     ELSE bucket5
                  END
                 ) b5,
                 (CASE
                     WHEN (bucket7 + issued_qty) < 0
                        THEN (bucket6 + bucket7 + issued_qty)
                     ELSE bucket6
                  END
                 ) b6,
                 (bucket7 + issued_qty) b7, total
            FROM (SELECT NAME, organization_name, item_code, item_type, uom,
                         subinventory_code, description, inventory_item_id,
                         organization_id, item_cost, on_hand_quantity,
                         bucket1, bucket2, bucket3, bucket4, bucket5, bucket6,
                         bucket7,
                         (SELECT NVL (SUM (mmt.primary_quantity),
                                      0
                                     )
                            FROM mtl_material_transactions mmt
                           WHERE 1 = 1
                             AND mmt.organization_id = z.organization_id
                             AND mmt.inventory_item_id = z.inventory_item_id
                             AND mmt.subinventory_code = z.subinventory_code
                             AND mmt.primary_quantity < 0
                             AND TRUNC (mmt.transaction_date) <=
                                                               TRUNC (:p_date))
                                                                   issued_qty,
                         total
                    FROM (SELECT a.NAME, a.organization_name, a.item_code,
                                 a.item_type, a.uom, a.subinventory_code,
                                 a.description, a.inventory_item_id,
                                 a.organization_id, a.item_cost,
                                 a.on_hand_quantity,
                                                    --(a.item_cost * a.on_hand_quantity) total
                                                    total,
                                 ((SELECT NVL (SUM (primary_quantity),
                                               0
                                              )
                                     FROM mtl_material_transactions b
                                    WHERE 1 = 1
                                      AND b.inventory_item_id =
                                                           a.inventory_item_id
                                      AND b.organization_id =
                                                             a.organization_id
                                      AND b.subinventory_code =
                                                           a.subinventory_code
                                      AND b.transaction_action_id NOT IN
                                                                     (24, 30)
                                      AND b.transaction_type_id NOT IN
                                                                      (10008)
                                      AND b.primary_quantity > 0
                                      AND TRUNC (transaction_date)
                                             BETWEEN TRUNC (:p_date) - 30
                                                 AND TRUNC (:p_date)))
                                                                      bucket1,
                                 ((SELECT NVL (SUM (primary_quantity),
                                               0
                                              )
                                     FROM mtl_material_transactions b
                                    WHERE 1 = 1
                                      AND b.inventory_item_id =
                                                           a.inventory_item_id
                                      AND b.organization_id =
                                                             a.organization_id
                                      AND b.subinventory_code =
                                                           a.subinventory_code
                                      AND b.transaction_action_id NOT IN
                                                                     (24, 30)
                                      AND b.transaction_type_id NOT IN
                                                                      (10008)
                                      AND b.primary_quantity > 0
                                      AND TRUNC (transaction_date)
                                             BETWEEN TRUNC (:p_date) - 60
                                                 AND TRUNC (:p_date) - 31))
                                                                      bucket2,
                                 (SELECT NVL (SUM (primary_quantity),
                                              0
                                             )
                                    FROM mtl_material_transactions b
                                   WHERE 1 = 1
                                     AND b.inventory_item_id =
                                                           a.inventory_item_id
                                     AND b.organization_id = a.organization_id
                                     AND b.subinventory_code =
                                                           a.subinventory_code
                                     AND b.transaction_action_id NOT IN
                                                                     (24, 30)
                                     AND b.transaction_type_id NOT IN (10008)
                                     AND b.primary_quantity > 0
                                     AND TRUNC (transaction_date)
                                            BETWEEN TRUNC (:p_date) - 90
                                                AND TRUNC (:p_date) - 61)
                                                                      bucket3,
                                 (SELECT NVL (SUM (primary_quantity),
                                              0
                                             )
                                    FROM mtl_material_transactions b
                                   WHERE 1 = 1
                                     AND b.inventory_item_id =
                                                           a.inventory_item_id
                                     AND b.organization_id = a.organization_id
                                     AND b.subinventory_code =
                                                           a.subinventory_code
                                     AND b.transaction_action_id NOT IN
                                                                     (24, 30)
                                     AND b.transaction_type_id NOT IN (10008)
                                     AND b.primary_quantity > 0
                                     AND TRUNC (transaction_date)
                                            BETWEEN TRUNC (:p_date) - 180
                                                AND TRUNC (:p_date) - 91)
                                                                      bucket4,
                                 (SELECT NVL (SUM (primary_quantity),
                                              0
                                             )
                                    FROM mtl_material_transactions b
                                   WHERE 1 = 1
                                     AND b.inventory_item_id =
                                                           a.inventory_item_id
                                     AND b.organization_id = a.organization_id
                                     AND b.subinventory_code =
                                                           a.subinventory_code
                                     AND b.transaction_action_id NOT IN
                                                                     (24, 30)
                                     AND b.transaction_type_id NOT IN (10008)
                                     AND b.primary_quantity > 0
                                     AND TRUNC (transaction_date)
                                            BETWEEN TRUNC (:p_date) - 240
                                                AND TRUNC (:p_date) - 181)
                                                                      bucket5,
                                 (SELECT NVL (SUM (primary_quantity),
                                              0
                                             )
                                    FROM mtl_material_transactions b
                                   WHERE 1 = 1
                                     AND b.inventory_item_id =
                                                           a.inventory_item_id
                                     AND b.organization_id = a.organization_id
                                     AND b.subinventory_code =
                                                           a.subinventory_code
                                     AND b.transaction_action_id NOT IN
                                                                     (24, 30)
                                     AND b.transaction_type_id NOT IN (10008)
                                     AND b.primary_quantity > 0
                                     AND TRUNC (transaction_date)
                                            BETWEEN TRUNC (:p_date) - 360
                                                AND TRUNC (:p_date) - 241)
                                                                      bucket6,
                                 (SELECT NVL (SUM (primary_quantity),
                                              0
                                             )
                                    FROM mtl_material_transactions b
                                   WHERE 1 = 1
                                     AND b.inventory_item_id =
                                                           a.inventory_item_id
                                     AND b.organization_id = a.organization_id
                                     AND b.subinventory_code =
                                                           a.subinventory_code
                                     AND b.transaction_action_id NOT IN
                                                                     (24, 30)
                                     AND b.transaction_type_id NOT IN (10008)
                                     AND b.primary_quantity > 0
                                     AND TRUNC (transaction_date) <
                                                          TRUNC (:p_date)
                                                          - 360) bucket7
                            FROM (SELECT   msi.concatenated_segments
                                                                    item_code,
                                           msi.item_type, hou.NAME,
                                           ood.organization_name,
                                           msi.primary_unit_of_measure uom,
                                           mmt.subinventory_code,
                                           msi.description,
                                           msi.inventory_item_id,
                                           msi.organization_id, cic.item_cost,
                                           SUM
                                              (mmt.primary_quantity
                                              ) on_hand_quantity,
                                           (  SUM (mmt.primary_quantity)
                                            * cic.item_cost
                                           ) total
                                      FROM mtl_system_items_kfv msi,
                                           mtl_material_transactions mmt,
                                           cst_item_costs cic,
                                           org_organization_definitions ood,
                                           hr_operating_units hou
                                     WHERE msi.inventory_item_id =
                                                         mmt.inventory_item_id
                                       AND msi.organization_id =
                                                           mmt.organization_id
                                       AND cic.inventory_item_id =
                                                         msi.inventory_item_id
                                       AND cic.organization_id =
                                                           msi.organization_id
                                       AND cic.cost_type_id = 2
                                       AND mmt.subinventory_code IS NOT NULL
                                       AND ood.organization_id =
                                                           mmt.organization_id
                                       AND ood.operating_unit =
                                                           hou.organization_id
                                       AND hou.organization_id =
                                              NVL (:p_operation_unit,
                                                   hou.organization_id
                                                  )
                                       AND msi.organization_id =
                                              NVL (:p_organia1ation,
                                                   msi.organization_id
                                                  )
                                       AND mmt.transaction_action_id NOT IN
                                                                     (24, 30)
                                       AND mmt.transaction_type_id NOT IN
                                                                      (10008)
                                       AND TRUNC (mmt.transaction_date) <=
                                                               TRUNC (:p_date)
                                    HAVING SUM (mmt.primary_quantity) IS NOT NULL
                                  --AND moqd.inventory_item_id = 18339
                                  GROUP BY msi.concatenated_segments,
                                           msi.description,
                                           msi.inventory_item_id,
                                           msi.organization_id,
                                           ood.organization_name,
                                           msi.primary_unit_of_measure,
                                           mmt.subinventory_code,
                                           msi.item_type,
                                           hou.NAME,
                                           cic.item_cost) a
                                                           --WHERE item_code = :p_item
                         ) z))
ORDER BY subinventory_code, inventory_item_id