CAC Missing WIP Accounting Transactions

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Report to find work in process (WIP) accounting entries where the WIP transaction has been created but the WIP accounting entries do not exist. If you enter Yes for "Only Costed Resources" the report ignores WIP transactions where the resource code is defined as not allowing costs (not costed). If you enter No for "Only Costed Resources" the report includes WIP transactions where the resource co ...  Report to find work in process (WIP) accounting entries where the WIP transaction has been created but the WIP accounting entries do not exist. If you enter Yes for "Only Costed Resources" the report ignores WIP transactions where the resource code is defined as not allowing costs (not costed). If you enter No for "Only Costed Resources" the report includes WIP transactions where the resource code does not allow costs as well as costed resources. And to get all transactions which are missing the WIP accounting entries, even for transactions where the resources are not costed, set the "Only Costed Resources" to No and the Minimum Transaction Amount to zero (0).

/* +=============================================================================+
-- | Copyright 2022 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: missing_wip_accounting_transactions.sql
-- |
-- | Parameters:
-- | p_trx_date_from -- Starting transaction date, mandatory
-- | p_trx_date_to -- Ending transaction date, mandatory
-- | p_minimum_amount -- The absolute smallest transaction amount to be reported
-- | p_only_costed_resources -- Only include transactions where the resource code is costed.
-- | p_org_code -- Specific inventory organization you wish to report (optional)
-- | p_operating_unit -- Operating Unit you wish to report, leave blank for all
-- | operating units (optional)
-- | p_ledger -- general ledger you wish to report, leave blank for all
-- | ledgers (optional)
-- |
-- | Description:
-- | Report to find WIP accounting entries where the WIP accounting entries do not
-- | exist.
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 21 Jul 2022 Douglas Volz Initial Coding based on missing_material_accounting_transactions.sql
-- | 1.1 23 Jul 2022 Douglas Volz Added Ledger and Operating Unit columns.
-- +=============================================================================+*/
   more
Run CAC Missing WIP Accounting Transactions and other Oracle EBS reports with Blitz Report™ on our demo environment
select	nvl(gl.short_name, gl.name) Ledger,
-- ================================================================
-- First get the resource and overhead transactions
-- ================================================================
	haou2.name Operating_Unit,
	mp.organization_code Org_Code,
	oap.period_name Period_Name,
	mtst.transaction_source_type_name Transaction_Source,
	ml1.meaning Transaction_Type,
	wt.transaction_id Transaction_Id,
	wt.transaction_date Transaction_Date,
	wt.creation_date Creation_Date,
	ml7.meaning WIP_Type,
	we.wip_entity_name WIP_Job,
	wt.operation_seq_num Operation_Seq_Number,
	wt.resource_seq_num Resource_Seq_Number,
	br.resource_code Resource_Code,
	br.description Resource_Description,
	cce.cost_element Cost_Element,
	msiv.concatenated_segments Outside_Processing_Item,
	msiv.description Outside_Processing_Description,
	ml2.meaning Resource_Type,
	ml3.meaning Charge_Type,
	ml4.meaning Basis_Type,
	ml5.meaning Allow_Costs,
	br.unit_of_measure UOM_Code,
	wt.primary_quantity Primary_Quantity,
	ml6.meaning Standard_Rate,
	gl.currency_code Currency_Code,
	wt.actual_resource_rate Actual_Resource_Rate,
	wt.standard_resource_rate Standard_Resource_Rate,
	wt.usage_rate_or_amount Usage_Rate_or_Amount,
	decode(br.standard_rate_flag,
		1, round(nvl(wt.primary_quantity,0) * nvl(wt.usage_rate_or_amount,0) * nvl(wt.standard_resource_rate,0),2),
		2, round(nvl(wt.primary_quantity,0) * nvl(wt.usage_rate_or_amount,0) * nvl(wt.actual_resource_rate,0),2)
	      ) Extended_WIP_Amount,
	(select	cct.cost_type
	 from	cst_cost_types cct
	 where	cct.cost_type_id = mp.primary_cost_method) Cost_Method,
	(select	crc.resource_rate
	 from	cst_resource_costs crc
	 where	crc.resource_id       = wt.resource_id
	 and	crc.organization_id   = wt.organization_id
	 and	crc.cost_type_id      = decode(mp.primary_cost_method, 1,1, avg_rates_cost_type_id)) Current_Resource_Cost
from	wip_transactions wt,
	wip_entities we,
	bom_resources br,
	cst_cost_elements cce,
	mtl_system_items_vl msiv,
	mtl_txn_source_types mtst,
	org_acct_periods oap,
	mtl_parameters mp,
	mfg_lookups ml1, -- WIP Transaction Type
	mfg_lookups ml2, -- 'BOM_RESOURCE_TYPE'
	mfg_lookups ml3, -- Charge Type
	mfg_lookups ml4, -- Basis Type
	mfg_lookups ml5, -- Allow Costs
	mfg_lookups ml6, -- Standard Rate Flag
	mfg_lookups ml7, -- WIP Entity Type
	hr_organization_information hoi,
	hr_all_organization_units_vl haou,  -- inv_organization_id
	hr_all_organization_units_vl haou2, -- operating unit
	gl_ledgers gl
-- ========================================================
-- WIP Transaction, org and item joins
-- ========================================================
where	we.wip_entity_id                 = wt.wip_entity_id
and	br.resource_id                   = wt.resource_id
and	br.purchase_item_id              = msiv.inventory_item_id (+)
and	br.organization_id               = msiv.organization_id (+)
and	br.cost_element_id               = cce.cost_element_id
and	mtst.transaction_source_type_id  = 5 -- WIP
and	mp.organization_id               = wt.organization_id
and	oap.acct_period_id               = wt.acct_period_id
and	ml1.lookup_type                  = 'WIP_TRANSACTION_TYPE_SHORT'
and	ml1.lookup_code                  = wt.transaction_type
and	ml2.lookup_type                  = 'BOM_RESOURCE_TYPE'
and	ml2.lookup_code                  = br.resource_type
and	ml3.lookup_type                  = 'BOM_AUTOCHARGE_TYPE'
and	ml3.lookup_code                  = br.autocharge_type
and	ml4.lookup_type                  = 'CST_BASIS'
and	ml4.lookup_code                  = br.default_basis_type
and	ml5.lookup_type                  = 'SYS_YES_NO'
and	ml5.lookup_code                  = br.allow_costs_flag
and	ml6.lookup_type                  = 'SYS_YES_NO'
and	ml6.lookup_code                  = br.standard_rate_flag
and	ml7.lookup_type                  = 'WIP_ENTITY'
and	ml7.lookup_code                  = we.entity_type
-- ===================================================================
-- using the base tables to avoid the performance issues
-- with org_organization_definitions and hr_operating_units
-- ===================================================================
and	hoi.org_information_context      = 'Accounting Information'
and	hoi.organization_id              = mp.organization_id
and	hoi.organization_id              = haou.organization_id   -- this gets the organization name
and	haou2.organization_id            = to_number(hoi.org_information3) -- this gets the operating unit id
and	gl.ledger_id                     = to_number(hoi.org_information1) -- get the ledger_id
-- ========================================================
-- Find missing accounting entries
-- ========================================================
and	wt.resource_id is not null
and not exists
	(select 'x'
	 from	wip_transaction_accounts wta
	 where	wt.transaction_id   = wta.transaction_id)
and	1=1                             -- p_trx_date_from, p_trx_date_to, p_org_code, p_operating_unit, p_ledger
and	2=2                             -- only_costed_resources, nvl(br.allow_costs_flag,2) = 1
group by
	nvl(gl.short_name, gl.name), -- Ledger
	haou2.name, -- Operating Unit  
	mp.organization_code,
	oap.period_name,
	mtst.transaction_source_type_name,
	ml1.meaning, -- WIP transaction type
	wt.transaction_id,
	wt.transaction_date,
	wt.creation_date,
	ml7.meaning, -- WIP Type
	we.wip_entity_name, -- WIP Job
	wt.operation_seq_num, -- Operation Seq Number
	wt.resource_seq_num, -- Resource Seq Number
	br.resource_code, -- Resource Code
	br.description, -- Resource Description
	cce.cost_element, -- Cost Element
	msiv.concatenated_segments, -- Outside Processing Item
	msiv.description, -- Outside Processing Description
	ml2.meaning, -- Resource Type
	ml3.meaning, -- Charge Type
	ml4.meaning, -- Basis Type
	ml5.meaning, -- Allow Costs
	br.unit_of_measure, -- UOM Code
	wt.primary_quantity, -- Primary Quantity
	ml6.meaning, -- Standard Rate Flag
	gl.currency_code, -- Currency Code
	wt.actual_resource_rate, -- Actual Resource Rate
	wt.standard_resource_rate, -- Standard Resource Rate
	wt.usage_rate_or_amount, -- Usage Rate or Amount
	decode(br.standard_rate_flag,
		1, round(nvl(wt.primary_quantity,0) * nvl(wt.usage_rate_or_amount,0) * nvl(wt.standard_resource_rate,0),2),
		2, round(nvl(wt.primary_quantity,0) * nvl(wt.usage_rate_or_amount,0) * nvl(wt.actual_resource_rate,0),2)
	      ), -- Extended WIP Amount
	wt.organization_id,
	mp.primary_cost_method,
	wt.resource_id,
	mp.avg_rates_cost_type_id,
	br.standard_rate_flag
having 	abs(decode(br.standard_rate_flag,
			1, round(nvl(wt.primary_quantity,0) * nvl(wt.usage_rate_or_amount,0) * nvl(wt.standard_resource_rate,0),2),
			2, round(nvl(wt.primary_quantity,0) * nvl(wt.usage_rate_or_amount,0) * nvl(wt.actual_resource_rate,0),2)
		  )
	   ) >= :p_minimum_amount -- Extended_Material_Amount
union all
-- ================================================================
-- Then get the cost update, variance and period close transactions
-- ================================================================
select	nvl(gl.short_name, gl.name) Ledger,
	haou2.name Operating_Unit,
	mp.organization_code Org_Code,
	oap.period_name Period_Name,
	mtst.transaction_source_type_name Transaction_Source,
	ml1.meaning Transaction_Type,
	wt.transaction_id Transaction_Id,
	wt.transaction_date Transaction_Date,
	wt.creation_date Creation_Date,
	ml7.meaning WIP_Type,
	we.wip_entity_name WIP_Job,
	wt.operation_seq_num Operation_Seq_Number,
	wt.resource_seq_num Resource_Seq_Number,
	null Resource_Code,
	null Resource_Description,
	null Cost_Element,
	null Outside_Processing_Item,
	null Outside_Processing_Description,
	null Resource_Type,
	null Charge_Type,
	null Basis_Type,
	null Allow_Costs,
	null UOM_Code,
	wt.primary_quantity Primary_Quantity,
	null  Standard_Rate,
	gl.currency_code Currency_Code,
	wt.actual_resource_rate Actual_Resource_Rate,
	wt.standard_resource_rate Standard_Resource_Rate,
	wt.usage_rate_or_amount Usage_Rate_or_Amount,
	round(nvl(wt.primary_quantity,0) * nvl(wt.usage_rate_or_amount,0) * nvl(wt.standard_resource_rate,0),2) Extended_WIP_Amount,
	(select	cct.cost_type
	 from	cst_cost_types cct
	 where	cct.cost_type_id = mp.primary_cost_method) Cost_Method,
	(select	crc.resource_rate
	 from	cst_resource_costs crc
	 where	crc.resource_id       = wt.resource_id
	 and	crc.organization_id   = wt.organization_id
	 and	crc.cost_type_id      = decode(mp.primary_cost_method, 1,1, avg_rates_cost_type_id)) Current_Resource_Cost
from	wip_transactions wt,
	wip_entities we,
	mtl_txn_source_types mtst,
	org_acct_periods oap,
	mtl_parameters mp,
	mfg_lookups ml1, -- WIP Transaction Type
	mfg_lookups ml7, -- WIP Entity Type
	hr_organization_information hoi,
	hr_all_organization_units_vl haou,  -- inv_organization_id
	hr_all_organization_units_vl haou2, -- operating unit
	gl_ledgers gl
-- ========================================================
-- WIP Transaction, org and item joins
-- ========================================================
where	we.wip_entity_id                 = wt.wip_entity_id
and	mtst.transaction_source_type_id  = 5 -- WIP
and	mp.organization_id               = wt.organization_id
and	oap.acct_period_id               = wt.acct_period_id
and	ml1.lookup_type                  = 'WIP_TRANSACTION_TYPE_SHORT'
and	ml1.lookup_code                  = wt.transaction_type
and	ml7.lookup_type                  = 'WIP_ENTITY'
and	ml7.lookup_code                  = we.entity_type
-- ===================================================================
-- using the base tables to avoid the performance issues
-- with org_organization_definitions and hr_operating_units
-- ===================================================================
and	hoi.org_information_context      = 'Accounting Information'
and	hoi.organization_id              = mp.organization_id
and	hoi.organization_id              = haou.organization_id   -- this gets the organization name
and	haou2.organization_id            = to_number(hoi.org_information3) -- this gets the operating unit id
and	gl.ledger_id                     = to_number(hoi.org_information1) -- get the ledger_id
-- ========================================================
-- Find missing accounting entries
-- ========================================================
and	wt.resource_id is null
and not exists
	(select 'x'
	 from	wip_transaction_accounts wta
	 where	wt.transaction_id   = wta.transaction_id)
and	1=1                             -- p_trx_date_from, p_trx_date_to, p_org_code, p_operating_unit, p_ledger
group by 
	nvl(gl.short_name, gl.name), -- Ledger
	haou2.name, -- Operating Unit  
	mp.organization_code,
	oap.period_name,
	mtst.transaction_source_type_name,
	ml1.meaning, -- WIP transaction type
	wt.transaction_id,
	wt.transaction_date,
	wt.creation_date,
	ml7.meaning, -- WIP Type
	we.wip_entity_name, -- WIP Job
	wt.operation_seq_num, -- Operation Seq Number
	wt.resource_seq_num, -- Resource Seq Number
	null, -- Resource Code
	null, -- Resource Description
	null, -- Cost Element
	null, -- Outside Processing Item
	null, -- Outside Processing Description
	null, -- Resource Type
	null, -- Charge Type
	null, -- Basis Type
	null, -- Allow Costs
	null, -- UOM Code
	wt.primary_quantity, -- Primary Quantity
	null, -- Standard Rate Flag
	gl.currency_code, -- Currency Code
	wt.actual_resource_rate, -- Actual Resource Rate
	wt.standard_resource_rate, -- Standard Resource Rate
	wt.usage_rate_or_amount, -- Usage Rate or Amount
	round(nvl(wt.primary_quantity,0) * nvl(wt.usage_rate_or_amount,0),2), -- Extended WIP Amount
	wt.organization_id,
	mp.primary_cost_method,
	wt.resource_id,
	mp.avg_rates_cost_type_id,
	null
having 	abs(round(nvl(wt.primary_quantity,0) * nvl(wt.usage_rate_or_amount,0) * nvl(wt.standard_resource_rate,0),2)
	   ) >= :p_minimum_amount -- Extended_Material_Amount
order by 1,2,3,4,6,8
Parameter Name SQL text Validation
Transaction Date From 
wt.transaction_date >= :p_trx_date_from
 Date
Transaction Date To 
wt.transaction_date < :p_trx_date_to + 1
 Date
Minimum Transaction Amount 
 
 Number
Only Costed Resources 
nvl(br.allow_costs_flag,2) = 1
 LOV Oracle
Organization Code 
mp.organization_code = :p_org_code
 LOV
Operating Unit 
haou2.name = :p_operating_unit
 LOV
Ledger 
gl.name = :p_ledger
 LOV
Download
 
Related Blitz Reports
CAC Material Account Detail CAC ICP PII Material Account Detail CAC Material Account Summary CAC Manufacturing Variance Detail CAC ICP PII Material Account Summary CAC Manufacturing Variance Summary CAC ICP PII WIP Material Usage Variance GL Account Analysis GL Account Analysis (Distributions)
Blitz Report™

Blitz Report™ provides multiple benefits: