CAC New Standard Item Costs

➜ Back to Library
XMLXML import file

Description

Categories
Enginatics
Repository
GitHub
Report to show items which have recent item cost changes, including various item controls, last cost update submission, new standard item cost, prior item cost and current (Frozen) item cost, by cost update date. And whether or not BOMs, routings or (inventory organization) sourcing rules exist for the item. As allowed with no onhand or intransit quantities, if an item cost has been directly entered into the Frozen Cost Type, the Cost Update Request Id, Cost Update Description, Cost Update Status and related cost update submission columns will be blank or empty.

Parameters:
Cost Update Date From: starting cost update date, based on standard cost submission history (required).
Cost Update Date To: ending cost update date, based on standard cost submission history (required).
From Cost Type: enter the cost type implemented by the Standard Cost Update into the Frozen costs (optional).
Category Set 1: any item category you wish, typically the Cost or Product Line category set (optional).
Category Set 2: any item category you wish, typically the Inventory category set (optional).
Item Number: enter the specific item number(s) you wish to report (optional).
Organization Code: enter the specific inventory organization(s) you wish to report (optional).
Operating Unit: enter the specific operating unit(s) you wish to report (optional).
Ledger: enter the specific ledger(s) you wish to report (optional).

/* +=============================================================================+
-- | Copyright 2023 Douglas Volz Consulting, Inc.
-- | All rights reserved.
-- | Permission to use this code is granted provided the original author is
-- | acknowledged. No warranties, express or otherwise is included in this permission.
-- +=============================================================================+
-- |
-- | Original Author: Douglas Volz ([email protected])
-- |
-- | Program Name: xxx_new_standard_item_cost_rept.sql
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 29 Sep 2023 Douglas Volz Initial coding based on the Cost Update
-- | Submissions report.
-- | 1.1 30 Oct 2023 Douglas Volz Added Item Created By, Last Updated By
-- | and BOM/Routing/Sourcing Rules exist columns.
-- | 1.2 22 Nov 2023 Douglas Volz Add item master and costing lot sizes, and
-- | use default controls columns.
-- | 1.3 05 Dec 2023 Douglas Volz Added G/L and Operating Unit security restrictions.
-- | 1.4 23 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards.
-- +=============================================================================+*/

SQL 124 lines

Parameters 10

ParameterTypeList of values SQL
Cost Update Date FromDate
csc.standard_cost_revision_date>=:p_cost_update_date_from
Cost Update Date ToDate
csc.standard_cost_revision_date<:p_cost_update_date_to+1
From Cost TypeLOV
cct.cost_type=:p_cost_type
Category Set 1LOV
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual
Category Set 2LOV
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual
Category Set 3LOV
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual
Item NumberLOV
csc.inventory_item_id=(select msiv.inventory_item_id from mtl_system_items_vl msiv where csc.organization_id=msiv.organization_id and msiv.concatenated_segments=:p_item_number)
Organization CodeLOV
mp.organization_code=:p_org_code
Operating UnitLOV
haouv_ou.name=:p_operating_unit
LedgerLOV
gl.name=:p_ledger