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(nvl(new_time(:p_asofdate,'PDT','GMT'), SYSDATE)) BETWEEN
costorganizationvpeo.effective_start_date AND
costorganizationvpeo.effective_end_date
) )
), aim_cst_inv_onhand AS (
-- customized cst_costed_attr_onhand_v / cst_b_csoted_attr_onhand_v seeded view
SELECT
COST_ORG_ID
, COST_BOOK_ID
, VAL_UNIT_ID
, INVENTORY_ITEM_ID
, SNAPSHOT_DATE
, NVL(LEAD (SNAPSHOT_DATE, 1)
OVER (PARTITION BY COST_ORG_ID, COST_BOOK_ID,
INVENTORY_ITEM_ID, VAL_UNIT_ID,INVENTORY_ORG_ID, SUBINVENTORY_CODE, LOCATOR_ID
,lot_number,uom_code
ORDER BY SNAPSHOT_DATE), to_date('4712-12-
31', 'yyyy-mm-dd')) EFF_TO_DATE
,SUM(QUANTITY) OVER
(PARTITION BY COST_ORG_ID, COST_BOOK_ID, INVENTORY_ITEM_ID,
VAL_UNIT_ID,INVENTORY_ORG_ID, SUBINVENTORY_CODE, LOCATOR_ID
ORDER BY SNAPSHOT_DATE) QUANTITY
, SUM(QUANTITY) OVER (PARTITION BY COST_ORG_ID, COST_BOOK_ID,
INVENTORY_ITEM_ID, VAL_UNIT_ID
ORDER BY SNAPSHOT_DATE) VU_QUANTITY
, --added for bug 22382996
INVENTORY_ORG_ID
, SUBINVENTORY_CODE
, LOCATOR_ID
, lot_number
, SUM(lot_level_qty) OVER
(PARTITION BY COST_ORG_ID, COST_BOOK_ID, INVENTORY_ITEM_ID,
VAL_UNIT_ID,INVENTORY_ORG_ID, SUBINVENTORY_CODE, LOCATOR_ID, lot_number,uom_code
ORDER BY SNAPSHOT_DATE) qty
,uom_code
FROM
( SELECT
CT.COST_ORG_ID
, CT.COST_BOOK_ID
, CT.VAL_UNIT_ID
, CT.INVENTORY_ITEM_ID
, CT.INVENTORY_ORG_ID
, CIT.SUBINVENTORY_CODE
, CIT.LOCATOR_ID
, citd.lot_number
, TRUNC(NVL(CTL.COST_DATE, NVL(CT.COST_DATE,
CT.TRANSACTION_DATE))) SNAPSHOT_DATE
, CT.UOM_CODE
,
sum( nvl(ctl.quantity,nvl(citd.primary_qty,0))) lot_level_qty
, SUM(NVL(CTL.QUANTITY, CT.QUANTITY)) QUANTITY
FROM
CST_TRANSACTION_LAYERS CTL
, CST_TRANSACTIONS CT
, CST_INV_TRANSACTIONS CIT
, CST_INV_TRANSACTION_DTLS citd
WHERE
NVL(CTL.POSTED_FLAG, 'Y') NOT IN ('N'
,'X'
,'W')
AND CTL.TRANSACTION_ID (+) = CT.TRANSACTION_ID
AND CT.CST_INV_TRANSACTION_ID =
CIT.CST_INV_TRANSACTION_ID
AND DECODE(CTL.COST_ORG_ID, NULL, CT.POSTED_FLAG,
'X') = 'X'
AND CT.INTRANSIT_FLAG
= 'N'
and ct.CST_INV_TRANSACTION_DTL_ID =
citd.CST_INV_TRANSACTION_DTL_ID(+)
GROUP BY
CT.COST_ORG_ID
, CT.COST_BOOK_ID
, CT.VAL_UNIT_ID
, CT.INVENTORY_ITEM_ID
, CT.INVENTORY_ORG_ID
, CIT.SUBINVENTORY_CODE
, CIT.LOCATOR_ID
, citd.lot_number
, TRUNC(NVL(CTL.COST_DATE, NVL(CT.COST_DATE,
CT.TRANSACTION_DATE)))
, CT.UOM_CODE
)
)
, ohasof AS
(SELECT cost_org_id,
cost_book_id,
val_unit_id,
inventory_item_id,
inventory_org_id organization_id ,
subinventory_code,
lot_number,
uom_code uom,
sum(qty) qty
FROM aim_cst_inv_onhand
WHERE nvl(:p_asofdate, SYSDATE) BETWEEN snapshot_date AND (eff_to_date-1)
group by
cost_org_id,cost_book_id,val_unit_id,inventory_item_id,inventory_org_id,subinventor
y_code, lot_number ,uom_code
)
SELECT
trunc(nvl(new_time(:p_asofdate,'PDT','GMT'), SYSDATE)) AS as_of_date,
100 * ( round(ival.total_cost / ival.report_total, 4) ) pct,
SUM(100*(round(ival.total_cost / ival.report_total, 5))) OVER(
ORDER BY
ival.item_number, ival.inventory_organization, 100
*(round(ival.total_cost / ival.report_total, 4)) ASC
) rsum,
ival.inventory_organization,
ival.item_number,
ival.item_description
--,ival.item_Status
,
TO_CHAR(trunc(ival.expiration_date),'MON-YYYY') month_yr,
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,
round(ival.standard_cost, 5) standard_cost,
round(ival.total_cost, 2) total_cost,
ival.report_total report_total,
'' gl_accounts,
ivms.description lot_status,
cvb.val_unit_code value_unit_code,
round(nvl(ival.expiration_date, SYSDATE) - SYSDATE, 2) diff
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,
oh.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 trunc(nvl(new_time(:p_asofdate,'PDT','GMT'), SYSDATE)) BETWEEN
trunc(cstinv.from_date) AND trunc(cstinv.TO_DATE)
AND oh.inventory_item_id = cost.inventory_item_id (+)
AND ( item.item_number IN (
:p_item
)
OR coalesce(:p_item, NULL) IS NULL )
AND ( trunc(cost.effective_start_date) IS NULL
OR trunc(nvl(new_time(:p_asofdate,'PDT','GMT'), 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,
oh.val_unit_id
) ival
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
AND ( ival.cost_org_name IN (
:p_cost_organization
)
OR coalesce(:p_cost_organization, NULL) IS NULL )
AND ( cvb.val_unit_code IN (
:p_val_unit
)
OR coalesce(:p_val_unit, NULL) IS NULL )
AND ( ival.cost_book IN (
:p_cost_book
)
OR coalesce(:p_cost_book, NULL) IS NULL )
AND ( cat.category_name IN (
:p_category
)
OR coalesce(:p_category, NULL) IS NULL )
ORDER BY
ival.item_number,
ival.inventory_organization ASC
