Elemental Inventory Value Oracle Standard Report Query

Elemental Inventory Value Oracle Standard Report Query, askhareesh blog for Oracle Apps
Query To Get  Totals By Element Of Cost Of an Item :  

SELECT ROUND (material_cost.VALUE, 2) material,
       ROUND (material_overhead_cost.VALUE, 2) material_overhead,
       ROUND (resource_cost.VALUE, 2) resource_cost,
       ROUND (outside_processing_cost.VALUE, 2) outside_processing,
       ROUND (overhead_cost.VALUE, 2) overhead,
       (  ROUND (material_cost.VALUE, 2)
        + ROUND (material_overhead_cost.VALUE, 2)
        + ROUND (resource_cost.VALUE, 2)
        + ROUND (outside_processing_cost.VALUE, 2)
        + ROUND (overhead_cost.VALUE, 2))
          total,
       outside_processing_cost.organization_id organization_id,
       DECODE (outside_processing_cost.organization_id,
               85, 'MST',
               86, 'PLP',
               87, 'SBP',
               88, 'CSP',
               89, 'SEN',
               101, 'MES')
          organization_code,
       DECODE (outside_processing_cost.organization_id,
               86, '10',
               87, '40',
               88, '30',
               89, '50')
          org_company
  FROM (SELECT --cost_details.item_number,
               --cost_details.organization_id,
               ROUND (SUM (cost_details.VALUE), 2) total
          --cost_details.qty
          FROM (  SELECT x.inventory_item_id,
                         x.organization_id,
                         x.item_number,
                         x.qty,
                         SUM (x.qty * NVL (z.material_overhead_cost, '0'))
                            VALUE
                    FROM (  SELECT b.segment1 AS item_number,
                                   a.organization_id,
                                   a.inventory_item_id,
                                   a.subinventory_code,
                                   SUM (a.primary_transaction_quantity) AS qty
                              FROM mtl_onhand_quantities_detail a,
                                   mtl_system_items_b b
                             WHERE a.inventory_item_id = b.inventory_item_id
                                   AND a.organization_id = b.organization_id
                          GROUP BY b.segment1,
                                   a.organization_id,
                                   a.inventory_item_id,
                                   a.subinventory_code) x,
                         (SELECT a.organization_id,
                                 a.asset_inventory,
                                 a.secondary_inventory_name,
                                 b.segment3 material_account,
                                 f.segment3 material_overhead_account,
                                 c.segment3 resource_account,
                                 d.segment3 overhead_account,
                                 e.segment3 expense_account
                            FROM mtl_secondary_inventories a,
                                 gl_code_combinations b,
                                 gl_code_combinations c,
                                 gl_code_combinations d,
                                 gl_code_combinations e,
                                 gl_code_combinations f
                           WHERE     1 = 1             --a.asset_inventory='1'
                                 AND a.organization_id = P_ORGANIZATION_ID
                                 AND a.material_account = b.code_combination_id
                                 AND a.material_overhead_account =
                                        f.code_combination_id
                                 AND a.resource_account = c.code_combination_id
                                 AND a.overhead_account = d.code_combination_id
                                 AND a.expense_account = e.code_combination_id) y,
                         (SELECT inventory_item_id,
                                 organization_id,
                                 material_cost,
                                 material_overhead_cost,
                                 resource_cost,
                                 overhead_cost,
                                 item_cost
                            FROM cst_item_costs
                           WHERE     1 = 1
                                 AND cost_type_id = 2
                                 AND organization_id = P_ORGANIZATION_ID  --86
                                                                        ) z
                   WHERE     x.organization_id = y.organization_id
                         AND x.subinventory_code = y.secondary_inventory_name
                         AND z.inventory_item_id = x.inventory_item_id
                         AND z.organization_id = x.organization_id
                --and z.material_overhead_cost <> '0'
                GROUP BY x.item_number,
                         x.subinventory_code,
                         x.qty,
                         x.inventory_item_id,
                         x.organization_id,
                         z.material_overhead_cost,
                         y.material_overhead_account
                UNION ALL
                  SELECT x.inventory_item_id,
                         x.organization_id,
                         x.item_number,
                         x.qty,
                         SUM (x.qty * NVL (z.material_cost, '0')) VALUE
                    FROM (  SELECT b.segment1 AS item_number,
                                   a.organization_id,
                                   a.inventory_item_id,
                                   a.subinventory_code,
                                   SUM (a.primary_transaction_quantity) AS qty
                              FROM mtl_onhand_quantities_detail a,
                                   mtl_system_items_b b
                             WHERE a.inventory_item_id = b.inventory_item_id
                                   AND a.organization_id = b.organization_id
                          GROUP BY b.segment1,
                                   a.organization_id,
                                   a.inventory_item_id,
                                   a.subinventory_code) x,
                         (SELECT a.organization_id,
                                 a.asset_inventory,
                                 a.secondary_inventory_name,
                                 b.segment3 material_account,
                                 f.segment3 material_overhead_account,
                                 c.segment3 resource_account,
                                 d.segment3 overhead_account,
                                 e.segment3 expense_account
                            FROM mtl_secondary_inventories a,
                                 gl_code_combinations b,
                                 gl_code_combinations c,
                                 gl_code_combinations d,
                                 gl_code_combinations e,
                                 gl_code_combinations f
                           WHERE     1 = 1             --a.asset_inventory='1'
                                 AND a.organization_id = P_ORGANIZATION_ID
                                 AND a.material_account = b.code_combination_id
                                 AND a.material_overhead_account =
                                        f.code_combination_id
                                 AND a.resource_account = c.code_combination_id
                                 AND a.overhead_account = d.code_combination_id
                                 AND a.expense_account = e.code_combination_id) y,
                         (SELECT inventory_item_id,
                                 organization_id,
                                 material_cost,
                                 material_overhead_cost,
                                 resource_cost,
                                 overhead_cost,
                                 item_cost
                            FROM cst_item_costs
                           WHERE     1 = 1
                                 --inventory_item_id= NVL(:P_INVENTORY_ITEM_ID,inventory_item_id)
                                 AND cost_type_id = 2
                                 AND organization_id = P_ORGANIZATION_ID  --86
                                                                        ) z
                   WHERE     x.organization_id = y.organization_id
                         AND x.subinventory_code = y.secondary_inventory_name
                         AND z.inventory_item_id = x.inventory_item_id
                         AND z.organization_id = x.organization_id
                --and z.material_overhead_cost <> '0'
                GROUP BY x.item_number,
                         x.subinventory_code,
                         x.qty,
                         x.inventory_item_id,
                         x.organization_id,
                         z.material_overhead_cost,
                         y.material_overhead_account
                UNION ALL
                  SELECT x.inventory_item_id,
                         x.organization_id,
                         x.item_number,
                         x.qty,
                         SUM (x.qty * NVL (z.resource_cost, '0')) VALUE
                    FROM (  SELECT b.segment1 AS item_number,
                                   a.organization_id,
                                   a.inventory_item_id,
                                   a.subinventory_code,
                                   SUM (a.primary_transaction_quantity) AS qty
                              FROM mtl_onhand_quantities_detail a,
                                   mtl_system_items_b b
                             WHERE a.inventory_item_id = b.inventory_item_id
                                   AND a.organization_id = b.organization_id
                          GROUP BY b.segment1,
                                   a.organization_id,
                                   a.inventory_item_id,
                                   a.subinventory_code) x,
                         (SELECT a.organization_id,
                                 a.asset_inventory,
                                 a.secondary_inventory_name,
                                 b.segment3 material_account,
                                 f.segment3 material_overhead_account,
                                 c.segment3 resource_account,
                                 d.segment3 overhead_account,
                                 e.segment3 expense_account
                            FROM mtl_secondary_inventories a,
                                 gl_code_combinations b,
                                 gl_code_combinations c,
                                 gl_code_combinations d,
                                 gl_code_combinations e,
                                 gl_code_combinations f
                           WHERE     1 = 1             --a.asset_inventory='1'
                                 AND a.organization_id = P_ORGANIZATION_ID
                                 AND a.material_account = b.code_combination_id
                                 AND a.material_overhead_account =
                                        f.code_combination_id
                                 AND a.resource_account = c.code_combination_id
                                 AND a.overhead_account = d.code_combination_id
                                 AND a.expense_account = e.code_combination_id) y,
                         (SELECT inventory_item_id,
                                 organization_id,
                                 material_cost,
                                 material_overhead_cost,
                                 resource_cost,
                                 overhead_cost,
                                 item_cost
                            FROM cst_item_costs
                           WHERE     1 = 1
                                 AND cost_type_id = 2
                                 AND organization_id = P_ORGANIZATION_ID  --86
                                                                        ) z
                   WHERE     x.organization_id = y.organization_id
                         AND x.subinventory_code = y.secondary_inventory_name
                         AND z.inventory_item_id = x.inventory_item_id
                         AND z.organization_id = x.organization_id
                --and z.material_overhead_cost <> '0'
                GROUP BY x.item_number,
                         x.subinventory_code,
                         x.qty,
                         x.inventory_item_id,
                         x.organization_id,
                         z.material_overhead_cost,
                         y.material_overhead_account
                UNION ALL
                  SELECT x.inventory_item_id,
                         x.organization_id,
                         x.item_number,
                         x.qty,
                         SUM (x.qty * NVL (z.overhead_cost, '0')) VALUE
                    FROM (  SELECT b.segment1 AS item_number,
                                   a.organization_id,
                                   a.inventory_item_id,
                                   a.subinventory_code,
                                   SUM (a.primary_transaction_quantity) AS qty
                              FROM mtl_onhand_quantities_detail a,
                                   mtl_system_items_b b
                             WHERE a.inventory_item_id = b.inventory_item_id
                                   AND a.organization_id = b.organization_id
                          GROUP BY b.segment1,
                                   a.organization_id,
                                   a.inventory_item_id,
                                   a.subinventory_code) x,
                         (SELECT a.organization_id,
                                 a.asset_inventory,
                                 a.secondary_inventory_name,
                                 b.segment3 material_account,
                                 f.segment3 material_overhead_account,
                                 c.segment3 resource_account,
                                 d.segment3 overhead_account,
                                 e.segment3 expense_account
                            FROM mtl_secondary_inventories a,
                                 gl_code_combinations b,
                                 gl_code_combinations c,
                                 gl_code_combinations d,
                                 gl_code_combinations e,
                                 gl_code_combinations f
                           WHERE     1 = 1             --a.asset_inventory='1'
                                 AND a.organization_id = P_ORGANIZATION_ID
                                 AND a.material_account = b.code_combination_id
                                 AND a.material_overhead_account =
                                        f.code_combination_id
                                 AND a.resource_account = c.code_combination_id
                                 AND a.overhead_account = d.code_combination_id
                                 AND a.expense_account = e.code_combination_id) y,
                         (SELECT inventory_item_id,
                                 organization_id,
                                 material_cost,
                                 material_overhead_cost,
                                 resource_cost,
                                 overhead_cost,
                                 item_cost
                            FROM cst_item_costs
                           WHERE     1 = 1
                                 AND cost_type_id = 2
                                 AND organization_id = P_ORGANIZATION_ID  --86
                                                                        ) z
                   WHERE     x.organization_id = y.organization_id
                         AND x.subinventory_code = y.secondary_inventory_name
                         AND z.inventory_item_id = x.inventory_item_id
                         AND z.organization_id = x.organization_id
                --and z.material_overhead_cost <> '0'
                GROUP BY x.item_number,
                         x.subinventory_code,
                         x.qty,
                         x.inventory_item_id,
                         x.organization_id,
                         z.material_overhead_cost,
                         y.material_overhead_account
                UNION ALL
                  SELECT x.inventory_item_id,
                         x.organization_id,
                         x.item_number,
                         x.qty,
                         SUM (x.qty * NVL (z.outside_processing_cost, '0'))
                            VALUE
                    FROM (  SELECT b.segment1 AS item_number,
                                   a.organization_id,
                                   a.inventory_item_id,
                                   a.subinventory_code,
                                   SUM (a.primary_transaction_quantity) AS qty
                              FROM mtl_onhand_quantities_detail a,
                                   mtl_system_items_b b
                             WHERE a.inventory_item_id = b.inventory_item_id
                                   AND a.organization_id = b.organization_id
                          GROUP BY b.segment1,
                                   a.organization_id,
                                   a.inventory_item_id,
                                   a.subinventory_code) x,
                         (SELECT a.organization_id,
                                 a.asset_inventory,
                                 a.secondary_inventory_name,
                                 b.segment3 material_account,
                                 f.segment3 material_overhead_account,
                                 c.segment3 resource_account,
                                 d.segment3 overhead_account,
                                 e.segment3 expense_account
                            FROM mtl_secondary_inventories a,
                                 gl_code_combinations b,
                                 gl_code_combinations c,
                                 gl_code_combinations d,
                                 gl_code_combinations e,
                                 gl_code_combinations f
                           WHERE     1 = 1             --a.asset_inventory='1'
                                 AND a.organization_id = P_ORGANIZATION_ID
                                 AND a.material_account = b.code_combination_id
                                 AND a.material_overhead_account =
                                        f.code_combination_id
                                 AND a.resource_account = c.code_combination_id
                                 AND a.overhead_account = d.code_combination_id
                                 AND a.expense_account = e.code_combination_id) y,
                         (SELECT inventory_item_id,
                                 organization_id,
                                 material_cost,
                                 material_overhead_cost,
                                 resource_cost,
                                 overhead_cost,
                                 outside_processing_cost,
                                 item_cost
                            FROM cst_item_costs
                           WHERE     1 = 1
                                 AND cost_type_id = 2
                                 AND organization_id = P_ORGANIZATION_ID  --86
                                                                        ) z
                   WHERE     x.organization_id = y.organization_id
                         AND x.subinventory_code = y.secondary_inventory_name
                         AND z.inventory_item_id = x.inventory_item_id
                         AND z.organization_id = x.organization_id
                --and z.material_overhead_cost <> '0'
                GROUP BY x.item_number,
                         x.subinventory_code,
                         x.qty,
                         x.inventory_item_id,
                         x.organization_id,
                         z.material_overhead_cost,
                         y.material_overhead_account) cost_details,
               mtl_item_categories mic,
               mtl_parameters mp
         --mtl_material_transactions mmt
         WHERE     1 = 1
               AND cost_details.inventory_item_id = mic.inventory_item_id
               AND cost_details.organization_id = mic.organization_id
               AND cost_details.organization_id = mp.organization_id
               AND mic.organization_id = mp.organization_id
               AND mp.primary_cost_method = 2
               AND mp.default_cost_group_id = 5000
               --and cost_details.inventory_item_id = mmt.inventory_item_id
               --and cost_details.organization_id = mmt.organization_id
               --and trunc (mmt.transaction_date) <= '20-APR-2015'
               AND mic.category_set_id = 1100000046--group by eivr_total.ORGANIZATION_ID,
                                                   --        eivr_total.ORGANIZATION_CODE,
                                                   --        eivr_total.CREATION_DATE,
                                                   --        eivr_total.CREATED_BY,
                                                   --        eivr_total.LAST_UPDATE_DATE,
                                                   --        eivr_total.LAST_UPDATED_BY,
                                                   --        eivr_total.LAST_UPDATE_LOGIN
                                                   --cost_details.item_number,
                                                   --cost_details.organization_id,
                                                   --cost_details.inventory_item_id,
                                                   --cost_details.qty
       ) eivr_total,
       (SELECT SUM (x.qty * NVL (z.material_cost, '0')) VALUE
          FROM (  SELECT b.segment1 AS item_number,
                         a.organization_id,
                         a.inventory_item_id,
                         a.subinventory_code,
                         SUM (a.primary_transaction_quantity) AS qty
                    FROM mtl_onhand_quantities_detail a, mtl_system_items_b b
                   WHERE a.inventory_item_id = b.inventory_item_id
                         AND a.organization_id = b.organization_id
                GROUP BY b.segment1,
                         a.organization_id,
                         a.inventory_item_id,
                         a.subinventory_code) x,
               (SELECT a.organization_id,
                       a.asset_inventory,
                       a.secondary_inventory_name,
                       b.segment3 material_account,
                       f.segment3 material_overhead_account,
                       c.segment3 resource_account,
                       d.segment3 overhead_account,
                       e.segment3 expense_account
                  FROM mtl_secondary_inventories a,
                       gl_code_combinations b,
                       gl_code_combinations c,
                       gl_code_combinations d,
                       gl_code_combinations e,
                       gl_code_combinations f
                 WHERE     1 = 1                       --a.asset_inventory='1'
                       AND a.organization_id = P_ORGANIZATION_ID
                       AND a.material_account = b.code_combination_id
                       AND a.material_overhead_account =
                              f.code_combination_id
                       AND a.resource_account = c.code_combination_id
                       AND a.overhead_account = d.code_combination_id
                       AND a.expense_account = e.code_combination_id) y,
               (SELECT inventory_item_id,
                       organization_id,
                       material_cost,
                       material_overhead_cost,
                       resource_cost,
                       overhead_cost,
                       item_cost
                  FROM cst_item_costs
                 WHERE     1 = 1
                       --inventory_item_id= NVL(:P_INVENTORY_ITEM_ID,inventory_item_id)
                       AND cost_type_id = 2
                       AND organization_id = P_ORGANIZATION_ID            --86
                                                              ) z
         WHERE     x.organization_id = y.organization_id
               AND x.subinventory_code = y.secondary_inventory_name
               AND z.inventory_item_id = x.inventory_item_id
               AND z.organization_id = x.organization_id--and z.material_overhead_cost <> '0'
                                                        --group by x.item_number, x.subinventory_code,x.QTY,x.inventory_item_id,x.organization_id,z.material_overhead_cost,
                                                        --y.material_overhead_account
       ) material_cost,
       (SELECT --x.inventory_item_id
               --,x.organization_id
               --,x.item_number
               --,x.QTY
               SUM (x.qty * NVL (z.material_overhead_cost, '0')) VALUE
          FROM (  SELECT b.segment1 AS item_number,
                         a.organization_id,
                         a.inventory_item_id,
                         a.subinventory_code,
                         SUM (a.primary_transaction_quantity) AS qty
                    FROM mtl_onhand_quantities_detail a, mtl_system_items_b b
                   WHERE a.inventory_item_id = b.inventory_item_id
                         AND a.organization_id = b.organization_id
                GROUP BY b.segment1,
                         a.organization_id,
                         a.inventory_item_id,
                         a.subinventory_code) x,
               (SELECT a.organization_id,
                       a.asset_inventory,
                       a.secondary_inventory_name,
                       b.segment3 material_account,
                       f.segment3 material_overhead_account,
                       c.segment3 resource_account,
                       d.segment3 overhead_account,
                       e.segment3 expense_account
                  FROM mtl_secondary_inventories a,
                       gl_code_combinations b,
                       gl_code_combinations c,
                       gl_code_combinations d,
                       gl_code_combinations e,
                       gl_code_combinations f
                 WHERE     1 = 1                       --a.asset_inventory='1'
                       AND a.organization_id = P_ORGANIZATION_ID
                       AND a.material_account = b.code_combination_id
                       AND a.material_overhead_account =
                              f.code_combination_id
                       AND a.resource_account = c.code_combination_id
                       AND a.overhead_account = d.code_combination_id
                       AND a.expense_account = e.code_combination_id) y,
               (SELECT inventory_item_id,
                       organization_id,
                       material_cost,
                       material_overhead_cost,
                       resource_cost,
                       overhead_cost,
                       item_cost
                  FROM cst_item_costs
                 WHERE     1 = 1
                       AND cost_type_id = 2
                       AND organization_id = P_ORGANIZATION_ID            --86
                                                              ) z
         WHERE     x.organization_id = y.organization_id
               AND x.subinventory_code = y.secondary_inventory_name
               AND z.inventory_item_id = x.inventory_item_id
               AND z.organization_id = x.organization_id--and z.material_overhead_cost <> '0'
                                                        --group by x.item_number, x.subinventory_code,x.QTY,x.inventory_item_id,x.organization_id,z.material_overhead_cost,
                                                        --y.material_overhead_account
       ) material_overhead_cost,
       (SELECT --x.inventory_item_id
               --,x.organization_id
               --,x.item_number
               --,x.QTY
               SUM (x.qty * NVL (z.resource_cost, '0')) VALUE
          FROM (  SELECT b.segment1 AS item_number,
                         a.organization_id,
                         a.inventory_item_id,
                         a.subinventory_code,
                         SUM (a.primary_transaction_quantity) AS qty
                    FROM mtl_onhand_quantities_detail a, mtl_system_items_b b
                   WHERE a.inventory_item_id = b.inventory_item_id
                         AND a.organization_id = b.organization_id
                GROUP BY b.segment1,
                         a.organization_id,
                         a.inventory_item_id,
                         a.subinventory_code) x,
               (SELECT a.organization_id,
                       a.asset_inventory,
                       a.secondary_inventory_name,
                       b.segment3 material_account,
                       f.segment3 material_overhead_account,
                       c.segment3 resource_account,
                       d.segment3 overhead_account,
                       e.segment3 expense_account
                  FROM mtl_secondary_inventories a,
                       gl_code_combinations b,
                       gl_code_combinations c,
                       gl_code_combinations d,
                       gl_code_combinations e,
                       gl_code_combinations f
                 WHERE     1 = 1                       --a.asset_inventory='1'
                       AND a.organization_id = P_ORGANIZATION_ID
                       AND a.material_account = b.code_combination_id
                       AND a.material_overhead_account =
                              f.code_combination_id
                       AND a.resource_account = c.code_combination_id
                       AND a.overhead_account = d.code_combination_id
                       AND a.expense_account = e.code_combination_id) y,
               (SELECT inventory_item_id,
                       organization_id,
                       material_cost,
                       material_overhead_cost,
                       resource_cost,
                       overhead_cost,
                       item_cost
                  FROM cst_item_costs
                 WHERE     1 = 1
                       AND cost_type_id = 2
                       AND organization_id = P_ORGANIZATION_ID            --86
                                                              ) z
         WHERE     x.organization_id = y.organization_id
               AND x.subinventory_code = y.secondary_inventory_name
               AND z.inventory_item_id = x.inventory_item_id
               AND z.organization_id = x.organization_id--and z.material_overhead_cost <> '0'
                                                        --group by x.item_number, x.subinventory_code,x.QTY,x.inventory_item_id,x.organization_id,z.material_overhead_cost,
                                                        --y.material_overhead_account
       ) resource_cost,
       (SELECT --x.inventory_item_id
               --,x.organization_id
               --,x.item_number
               --,x.QTY
               SUM (x.qty * NVL (z.overhead_cost, '0')) VALUE
          FROM (  SELECT b.segment1 AS item_number,
                         a.organization_id,
                         a.inventory_item_id,
                         a.subinventory_code,
                         SUM (a.primary_transaction_quantity) AS qty
                    FROM mtl_onhand_quantities_detail a, mtl_system_items_b b
                   WHERE a.inventory_item_id = b.inventory_item_id
                         AND a.organization_id = b.organization_id
                GROUP BY b.segment1,
                         a.organization_id,
                         a.inventory_item_id,
                         a.subinventory_code) x,
               (SELECT a.organization_id,
                       a.asset_inventory,
                       a.secondary_inventory_name,
                       b.segment3 material_account,
                       f.segment3 material_overhead_account,
                       c.segment3 resource_account,
                       d.segment3 overhead_account,
                       e.segment3 expense_account
                  FROM mtl_secondary_inventories a,
                       gl_code_combinations b,
                       gl_code_combinations c,
                       gl_code_combinations d,
                       gl_code_combinations e,
                       gl_code_combinations f
                 WHERE     1 = 1                       --a.asset_inventory='1'
                       AND a.organization_id = P_ORGANIZATION_ID
                       AND a.material_account = b.code_combination_id
                       AND a.material_overhead_account =
                              f.code_combination_id
                       AND a.resource_account = c.code_combination_id
                       AND a.overhead_account = d.code_combination_id
                       AND a.expense_account = e.code_combination_id) y,
               (SELECT inventory_item_id,
                       organization_id,
                       material_cost,
                       material_overhead_cost,
                       resource_cost,
                       overhead_cost,
                       item_cost
                  FROM cst_item_costs
                 WHERE     1 = 1
                       AND cost_type_id = 2
                       AND organization_id = P_ORGANIZATION_ID            --86
                                                              ) z
         WHERE     x.organization_id = y.organization_id
               AND x.subinventory_code = y.secondary_inventory_name
               AND z.inventory_item_id = x.inventory_item_id
               AND z.organization_id = x.organization_id--and z.material_overhead_cost <> '0'
                                                        --group by x.item_number, x.subinventory_code,x.QTY,x.inventory_item_id,x.organization_id,z.material_overhead_cost,
                                                        --y.material_overhead_account
       ) overhead_cost,
       (  SELECT --x.inventory_item_id
                 x.organization_id,
                 --,x.item_number
                 --,x.QTY
                 SUM (x.qty * NVL (z.outside_processing_cost, '0')) VALUE
            FROM (  SELECT b.segment1 AS item_number,
                           a.organization_id,
                           a.inventory_item_id,
                           a.subinventory_code,
                           SUM (a.primary_transaction_quantity) AS qty
                      FROM mtl_onhand_quantities_detail a, mtl_system_items_b b
                     WHERE a.inventory_item_id = b.inventory_item_id
                           AND a.organization_id = b.organization_id
                  GROUP BY b.segment1,
                           a.organization_id,
                           a.inventory_item_id,
                           a.subinventory_code) x,
                 (SELECT a.organization_id,
                         a.asset_inventory,
                         a.secondary_inventory_name,
                         b.segment3 material_account,
                         f.segment3 material_overhead_account,
                         c.segment3 resource_account,
                         d.segment3 overhead_account,
                         e.segment3 expense_account
                    FROM mtl_secondary_inventories a,
                         gl_code_combinations b,
                         gl_code_combinations c,
                         gl_code_combinations d,
                         gl_code_combinations e,
                         gl_code_combinations f
                   WHERE     1 = 1                     --a.asset_inventory='1'
                         AND a.organization_id = P_ORGANIZATION_ID
                         AND a.material_account = b.code_combination_id
                         AND a.material_overhead_account =
                                f.code_combination_id
                         AND a.resource_account = c.code_combination_id
                         AND a.overhead_account = d.code_combination_id
                         AND a.expense_account = e.code_combination_id) y,
                 (SELECT inventory_item_id,
                         organization_id,
                         material_cost,
                         material_overhead_cost,
                         resource_cost,
                         overhead_cost,
                         outside_processing_cost,
                         item_cost
                    FROM cst_item_costs
                   WHERE     1 = 1
                         AND cost_type_id = 2
                         AND organization_id = P_ORGANIZATION_ID) z
           WHERE     x.organization_id = y.organization_id
                 AND x.subinventory_code = y.secondary_inventory_name
                 AND z.inventory_item_id = x.inventory_item_id
                 AND z.organization_id = x.organization_id
        --and z.material_overhead_cost <> '0'
        GROUP BY x.organization_id--x.item_number, x.subinventory_code,x.QTY,x.inventory_item_id,x.organization_id,z.material_overhead_cost,
                                  --y.material_overhead_account

       ) outside_processing_cost


*/

No comments:

Post a Comment