CAC Receiving Value (Period-End)

➜ Back to Library
XLSExcel example XMLXML import file

Description

Categories
Enginatics
Repository
GitHub
Report to show receiving value for all locations, as of the end of an accounting period. You may run this report for open or closed accounting periods. With parameters to display or not display WIP outside processing information (WIP job, status, close date and OSP resource code). If run for a prior period the report automatically rolls back the quantities and values to the prior period's period end date. In addition, for any receipt with an item number, this report displays Inventory, Shop Floor (Outside Processing) and Expense destination types.

Note: The Adjustment Amount column show differences between the PO unit price for the Receipt and Deliver transactions, which currently is not included in the receiving accounting entries.

Reconciliation Notes to Oracle Reports:
The Oracle All Inventory Values Report does not display Shop Floor (Outside Processing) or Expense destination types.
If the PO line is closed, the Oracle Receiving Value Report does not display Expense destination types, even if the receipt is still in receiving and has not been delivered.

/* +=============================================================================+
-- | Copyright 2009 - 2025 Douglas Volz Consulting, Inc. and Enginatics GmbH
-- | All rights reserved.
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 25 Nov 2009 Douglas Volz Created initial Report for client
-- | based on xxx_inv_value.sql
-- | 1.36 10 Aug 2022 Douglas Volz Performance improvements for main query. Allow expense
-- | receipts with items, add PO line status column.
-- | 1.37 12 Aug 2022 Douglas Volz Add parameter to Show WIP OSP columns and remove extra
-- | join to the item master table.
-- | 1.38 16 Aug 2022 Douglas Volz Fixes for quantities for Correction transactions and
-- | for when the PO unit price is zero.
-- | 1.39 02 Jun 2025 Douglas Volz Removed tabs.
-- | 1.40 05 Jun 2025 Douglas Volz Added section 1.6 for PO unit price differences between
-- | Piyush Khandelwal the PO Receipt and the PO Deliver transactions. Put
-- | this into an "Adjustment Amount" column, as this is
-- | included in the Oracle Receiving Value Report but is
-- | not recorded on any Receiving Accounting entry.
-- | Also added in inventory org access, XXEN G/L and OU security profiles.
-- | 1.44 23 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards.
-- +=============================================================================+*/

SQL 582 lines

Parameters 12

ParameterTypeList of values SQL
Period NameLOV
oap.period_name=:p_period_name
Show WIP Outside ProcessingLOV Oracle
(select we.wip_entity_name from wip_entities we where x.wip_entity_id=we.wip_entity_id) wip_job,
(select xxen_util.meaning(wdj.status_type,'WIP_JOB_STATUS',700) from wip_discrete_jobs wdj where x.wip_entity_id=wdj.wip_entity_id) job_status,
(select wdj.date_closed from wip_discrete_jobs wdj where x.wip_entity_id=wdj.wip_entity_id) job_close_date,
(select br.resource_code from bom_resources br where x.bom_resource_id=br.resource_id) osp_resource,
Category Set 1LOV
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'x') sql_text from dual
Category Set 2LOV
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'x') sql_text from dual
Category Set 3LOV
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'x') sql_text from dual
Item NumberLOV
msiv.concatenated_segments=:p_item_number
Organization CodeLOV
rt.organization_id in (select mp.organization_id from mtl_parameters mp where mp.organization_code=:p_org_code)
Operating UnitLOV
haouv_ou.name=:p_operating_unit
LedgerLOV
gl.name=:p_ledger
Product Line AccountingLOV Oracle
Item Master Account TypeLOV
Product Line SegmentLOV
select
'gcc'||decode(fifsv.form_left_prompt,:product_segment,'_item')||'.'||lower(fifsv.application_column_name)||'=gcc2.'||lower(fifsv.application_column_name)||' and' text
from
fnd_id_flex_segments_vl fifsv
where
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.enabled_flag='Y' and
fifsv.id_flex_num=(select fifsv0.id_flex_num from fnd_id_flex_structures_vl fifsv0 where fifsv0.application_id=101 and fifsv0.id_flex_code='GL#' and fifsv0.id_flex_structure_name=:chart_of_accounts)
order by
fifsv.segment_num