WITH cob AS (

SELECT
costorgbookpeo.currency_code currency,
costbooktlpeo.cost_book_desc cost_book,
costorganizationvpeo.cost_org_id cost_org_id,
costorganizationvpeo.cost_org_name cost_org_name
FROM
cst_cost_org_books costorgbookpeo,
cst_cost_books_b costbookbpeo,
cst_cost_books_tl costbooktlpeo,
cst_cost_orgs_v costorganizationvpeo
WHERE
( costorgbookpeo.cost_book_id = costbookbpeo.cost_book_id
AND costbookbpeo.cost_book_id = costbooktlpeo.cost_book_id
AND costorgbookpeo.cost_org_id = costorganizationvpeo.cost_org_id
AND ( trunc(SYSDATE) BETWEEN costorganizationvpeo.effective_start_date
AND costorganizationvpeo.effective_end_date ) )
), ohasof AS (
SELECT
organization_id,
inventory_item_id,
subinventory_code,
locator_id,
SUM(qty) qty,
uom,
lot_number
FROM
(
SELECT
ohd.organization_id,
ohd.inventory_item_id,
ohd.subinventory_code,
ohd.locator_id,
SUM(ohd.transaction_quantity) qty,
transaction_uom_code uom,
lot_number
FROM
inv_onhand_quantities_detail ohd
WHERE
1 = 1
GROUP BY
ohd.organization_id,
ohd.inventory_item_id,
ohd.subinventory_code,
ohd.locator_id,
transaction_uom_code,
lot_number
UNION ALL
SELECT
trx.organization_id,
trx.inventory_item_id,
trx.subinventory_code,
trx.locator_id,
- SUM(transaction_quantity) qty,
transaction_uom uom,
orig_lot_number lot_number
FROM
inv_material_txns trx
WHERE
 1 = 1
-- trunc(trx.transaction_date) > trunc(SYSDATE)--nvl(:p_asofdate,
SYSDATE)
AND trunc(trx.transaction_date) > trunc(nvl(:p_asofdate,sysdate))
GROUP BY
trx.organization_id,
trx.inventory_item_id,
trx.subinventory_code,
trx.locator_id,
transaction_uom,
orig_lot_number
)
GROUP BY
organization_id,
inventory_item_id,
subinventory_code,
locator_id,
uom,
lot_number
)
SELECT
round(ival.total_cost / ival.report_total, 6) pct,
SUM(round(ival.total_cost / ival.report_total, 6)) OVER(
ORDER BY
round(ival.total_cost / ival.report_total, 6) DESC
) rsum,
ival.inventory_organization,
ival.item_number,
ival.item_description
--,ival.item_Status
,
cat.category_name,
ival.uom,
ival.currency,
ival.cost_book,
ival.cost_org_name,
ival.lot_number,
ival.expiration_date,
ccat.cst_category_name costing_category,
ival.sub_inventory,
round(ival.total_quantity,2) total_quantity,
ival.standard_cost,
ival.total_cost,
ival.report_total report_total,
'' gl_accounts,
ivms.description lot_status,
cvb.val_unit_code value_unit_code
FROM
cst_xla_cst_categories_v ccat,
egp_categories_vl cat,
egp_default_category_sets edcs,
egp_item_categories eic,
inv_material_statuses_vl ivms,
cst_val_units_b cvb,
(
SELECT
item.inventory_item_id,
iuom.unit_of_measure uom,
oh.lot_number,
 iln.status_id,
iln.expiration_date,
item.organization_id,
params.organization_code inventory_organization,
item.item_number item_number,
item_tl.description item_description,
oh.subinventory_code sub_inventory,
cst_org_book.currency,
cst_org_book.cost_book,
cst_org_book.cost_org_name,
cost.val_unit_id,
nvl(SUM(oh.qty), 0) total_quantity,
nvl(AVG(cost.total_cost), 0) standard_cost,
SUM(nvl(oh.qty * cost.total_cost, 0)) total_cost,
SUM(SUM(nvl(oh.qty * cost.total_cost, 0))) OVER() report_total
FROM
cst_std_costs cost,
cst_cost_inv_orgs cstinv,
egp_system_items_tl item_tl,
egp_system_items_b item,
ohasof oh,
cst_cost_orgs_v cparams,
inv_org_parameters params,
inv_lot_numbers iln,
cob cst_org_book,
INV_UNITS_OF_MEASURE_VL iuom
WHERE
1 = 1
and (params.organization_code in (:p_inventory_organization) or
coalesce(:p_inventory_organization,null) is null)
and (oh.subinventory_code in (:p_subinventory_code) or
coalesce(:p_subinventory_code,null) is null)
AND cparams.cost_org_id = cost.cost_org_id
AND cst_org_book.cost_org_id = cparams.cost_org_id
AND oh.organization_id = params.organization_id
AND oh.inventory_item_id = item.inventory_item_id
AND oh.organization_id = item.organization_id
AND item_tl.inventory_item_id = item.inventory_item_id
AND item_tl.organization_id = item.organization_id
AND oh.organization_id = cstinv.inv_org_id
AND cstinv.cost_org_id = cost.cost_org_id
AND SYSDATE BETWEEN cstinv.from_date AND cstinv.TO_DATE
AND oh.inventory_item_id = cost.inventory_item_id (+)
AND (trunc(cost.effective_start_date) is null or
trunc( nvl(:p_asofdate,sysdate)) between trunc(cost.effective_start_date) and
trunc(cost.effective_end_date)
)
-- AND ( trunc(cost.effective_start_date) IS NULL
-- OR trunc(SYSDATE) BETWEEN trunc(cost.effective_start_date) AND
trunc(cost.effective_end_date) )
AND cost.status_code (+) = 'PUBLISHED'
AND iln.inventory_item_id = item.inventory_item_id
AND iln.organization_id = item.organization_id
AND oh.lot_number = iln.lot_number
and iuom.uom_code=oh.uom
GROUP BY
item.inventory_item_id,
item.organization_id,
params.organization_code,
 iln.status_id,
item.item_number,
item_tl.description,
oh.subinventory_code,
iuom.unit_of_measure,
oh.lot_number,
iln.expiration_date,
cst_org_book.currency,
cst_org_book.cost_book,
cst_org_book.cost_org_name,
cost.val_unit_id
) ival
-- item category joins
WHERE
1 = 1
AND eic.inventory_item_id = ival.inventory_item_id
AND ivms.status_id = ival.status_id
AND cvb.val_unit_id = ival.val_unit_id
AND eic.organization_id = ival.organization_id
AND eic.category_set_id = edcs.category_set_id
AND edcs.functional_area_id = 1 /* Inventory */
AND cat.category_id = eic.category_id
-- Costing Category
AND ccat.inventory_item_id = ival.inventory_item_id
AND ccat.inv_org_id = ival.organization_id
ORDER BY
1 DESC