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