Post date: Nov 16, 2010 3:56:57 AM
/* Formatted on 2010/08/26 16:40 (Formatter Plus v4.8.8) */
SELECT c.segment1, b.qty, a.cmpnt_cost, b.qty * a.cmpnt_cost,
b.organization_id
FROM (SELECT inventory_item_id, cmpnt_cost, organization_id, period_id
FROM cm_cmpt_dtl) a,
(SELECT SUM (primary_quantity) qty, inventory_item_id,
organization_id, acct_period_id
FROM gmf_period_balances
GROUP BY inventory_item_id, organization_id, acct_period_id) b,
mtl_system_items_b c,
(SELECT cost_organization_id, organization_id
FROM cm_whse_asc) d,
org_acct_periods_v e
WHERE a.inventory_item_id = b.inventory_item_id
AND a.inventory_item_id = c.inventory_item_id
AND a.organization_id = d.cost_organization_id
AND b.organization_id = c.organization_id
AND b.organization_id = d.organization_id
AND a.period_id = e.period_number
AND b.acct_period_id = e.acct_period_id
AND e.period_name = :mon_yy
UNION
SELECT c.segment1, b.qty, a.cmpnt_cost, b.qty * a.cmpnt_cost,
b.organization_id
FROM (SELECT inventory_item_id, cmpnt_cost, organization_id, period_id
FROM cm_cmpt_dtl) a,
(SELECT SUM (primary_quantity) qty, inventory_item_id,
organization_id, acct_period_id
FROM gmf_period_balances
GROUP BY inventory_item_id, organization_id, acct_period_id) b,
mtl_system_items_b c,
org_acct_periods_v e
WHERE a.inventory_item_id = b.inventory_item_id
AND a.inventory_item_id = c.inventory_item_id
AND a.organization_id = b.organization_id
AND a.organization_id = c.organization_id
AND e.acct_period_id = b.acct_period_id
AND a.period_id = e.period_number
AND e.period_name = :mon_yy