Scribd Logo
Upload

Welcome to Scribd!

  • 688 views

    Inventory Valiuation Raw Query

    Uploaded by

    satyanarayana NVS
    This SQL query performs the following: 1) Joins data from multiple inventory-related tables to retrieve item, location, costing and category information. 2) Calculates total quantities, costs and other metrics for each unique combination of item, location, lot, etc. 3) Orders the results by the percentage that each item/location total cost represents of the overall total cost.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd

    Inventory Valiuation Raw Query

    Uploaded by

    satyanarayana NVS
    688 views4 pages
    This SQL query performs the following: 1) Joins data from multiple inventory-related tables to retrieve item, location, costing and category information. 2) Calculates total quantities, costs and other metrics for each unique combination of item, location, lot, etc. 3) Orders the results by the percentage that each item/location total cost represents of the overall total cost.

    Original Title

    Inventory_Valiuation_Raw_query

    Copyright

    © © All Rights Reserved

    Available Formats

    TXT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This SQL query performs the following: 1) Joins data from multiple inventory-related tables to retrieve item, location, costing and category information. 2) Calculates total quantities, costs and other metrics for each unique combination of item, location, lot, etc. 3) Orders the results by the percentage that each item/location total cost represents of the overall total cost.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    688 views4 pages

    Inventory Valiuation Raw Query

    Uploaded by

    satyanarayana NVS
    This SQL query performs the following: 1) Joins data from multiple inventory-related tables to retrieve item, location, costing and category information. 2) Calculates total quantities, costs and other metrics for each unique combination of item, location, lot, etc. 3) Orders the results by the percentage that each item/location total cost represents of the overall total cost.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    of 4
    At a glance
    Powered by AI
    The SQL query is performing an inventory valuation and costing analysis by organization, item, location etc. It is aggregating quantities, costs and totals from various tables.

    The SQL query is doing an inventory valuation report by joining various inventory tables like inventory_items, locations, lots, costs etc. It is aggregating quantities, costs and totals for analysis.

    The main tables involved are inv_onhand_quantities_detail, inv_material_txns, egp_system_items_b, egp_system_items_tl, inv_item_locations, cst_std_costs etc.

    WITH 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
    AND trunc(trx.transaction_date) > trunc(SYSDATE)--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
    ,
    ival.lifecycle_phase,
    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,
    ival.locator,
    ival.locator_description,
    ival.total_quantity,
    ival.standard_cost,
    ival.total_cost,
    ival.report_total report_total,
    (
    SELECT
    nvl(sinv.attribute1, ccat.cst_category_attribute1)
    FROM
    inv_secondary_inventories sinv
    WHERE
    1 = 1
    AND sinv.secondary_inventory_name = ival.sub_inventory
    AND sinv.organization_id = ival.organization_id
    ) gl_accounts,
    (
    SELECT
    inv_material_statuses_vl.description
    FROM
    inv_material_statuses_vl
    WHERE
    ival.status_id = inv_material_statuses_vl.status_id
    ) lot_status
    FROM
    cst_xla_cst_categories_v ccat,
    egp_categories_vl cat,
    egp_default_category_sets edcs,
    egp_item_categories eic,
    (
    SELECT
    item.inventory_item_id,
    oh.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,
    lphase.phase_name lifecycle_phase,
    oh.subinventory_code sub_inventory,
    cst_org_book.currency,cst_org_book.cost_book,
    cst_org_book.cost_org_name,
    iil.segment1
    || '.'
    || iil.segment2
    || '.'
    || iil.segment3
    || '.'
    || iil.segment4 locator,
    iil.description locator_description,
    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
    egp_phases_vl lphase,
    cst_std_costs cost,
    cst_cost_inv_orgs cstinv,
    inv_item_locations iil,
    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,
    (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
    ) )) cst_org_book
    WHERE
    1 = 1
    AND item.current_phase_code = lphase.phase_code (+)
    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.locator_id = iil.inventory_location_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(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
    GROUP BY
    item.inventory_item_id,
    item.organization_id,
    params.organization_code,
    iln.status_id,
    item.item_number,
    item_tl.description,
    oh.subinventory_code,
    iil.segment1
    || '.'
    || iil.segment2
    || '.'
    || iil.segment3
    || '.'
    || iil.segment4,
    iil.description,
    lphase.phase_name,
    oh.uom,
    oh.lot_number,
    iln.expiration_date,cst_org_book.currency,cst_org_book.cost_book,
    cst_org_book.cost_org_name
    ) ival
    -- item category joins
    WHERE
    1 = 1
    AND eic.inventory_item_id = ival.inventory_item_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

    You might also like