CAC Receiving Account Summary

Description
Categories: Cost Accounting - Assessment, Cost Accounting - Transactions, Enginatics, Toolkit - Cost Accounting
Repository: Github
Report to get the receiving accounting distributions, in summary, by item, purchase order, purchase order line, release and project number. With the Show SLA Accounting parameter you can choose to use the Release 12 Subledger Accounting (Create Accounting) account setups by selecting Yes. And if you have not modified your SLA accounting rules, select No to allow this report to run a bit faster.  ...  Report to get the receiving accounting distributions, in summary, by item, purchase order, purchase order line, release and project number. With the Show SLA Accounting parameter you can choose to use the Release 12 Subledger Accounting (Create Accounting) account setups by selecting Yes. And if you have not modified your SLA accounting rules, select No to allow this report to run a bit faster. With parameters to limit the report size, to display or not display purchase information (purchase order, line, release) and WIP outside processing information (WIP job and OSP resource code). And if you accrue expense receipts at time of receipt, for expense destinations when there is no item number, this report will get the expense category information and put it into the columns for the first category set.

(Note: this report has not been tested with encumbrance entries.)

Parameters:
===========
Transaction Date From: enter the starting transaction date (mandatory).
Transaction Date To: enter the starting transaction date (mandatory).
Show SLA Accounting: enter Yes to use the Subledger Accounting rules for your accounting information.
Show Purchase Orders: display the purchase order, line and release information. Enter Yes or No, use to limit the report size. (mandatory).
Show WIP Outside Processing: display the WIP job and outside processing resource. Enter Yes or No, use to limit the report size. (mandatory).
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).
Supplier Name: enter the specific supplier you wish to report (optional).
PO Number: enter the specific purchase order number you wish to report (optional).
Destination Code: enter the purchase order destination type you wish to report (optional). You can choose Inventory, Expense or Shop Floor (WIP outside processing).
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 2009- 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.
-- +=============================================================================+
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 06 Nov 2009 Douglas Volz Initial Coding
-- | 1.14 23 May 2020 Douglas Volz Use multi-language table for UOM Code, item
-- | master and hr organization names. Removed
-- | group by for inner queries, not needed.
-- | For expense destinations, if there is no item
-- | number, get the PO Category from the PO Line.
-- | 1.15 17 Sep 2021 Douglas Volz Parameter changes, adding item number, PO number.
-- | 1.16 08 Jul 2022 Douglas Volz Subledger Accounting performance improvements
-- | and parameter to show or not show PO information.
-- | 1.17 14 Aug 2022 Douglas Volz Revision for expense/no item receipts.
-- | Combine SLA and Non-SLA Receiving Acct Reports. Make
-- | Show SLA, Show PO and Show WIP OSP dynamic SQL parameters.
-- +=============================================================================+*/
   more
Run CAC Receiving Account Summary and other Oracle EBS reports with Blitz Report™ on our demo environment
select	nvl(gl.short_name, gl.name) Ledger,
	haou2.name Operating_Unit,
	rcv_acct.organization_code Org_Code,
	oap.period_name Period_Name,
	&segment_columns
	rcv_acct.item_number Item_Number,
	rcv_acct.item_description Item_Description,
	-- Revision for version 1.13
	rcv_acct.item_type Item_Type,
         -- Revision for version 1.17
	-- If the item does not exist, get the expense category information
&category_columns
	rcv_acct.accounting_line_type Accounting_Line_Type,
	flv.meaning Transaction_Type,
	-- Revision for version 1.16
	rcv_acct.vendor_name Supplier_Name,
	pl.displayed_field Destination_Type,
	-- Revision for version 1.17
	&show_purchase_order
	-- End fix for version 1.9 and 1.16
	-- Revision for version 1.11
	pp.segment1 Project_Number,
	-- Fix for version 1.9 and 1.17
	&show_wip_osp
	-- End fix for version 1.9
	-- Revision for version 1.17, preventing Expense Receipts to be reported
	-- rcv_acct.primary_uom_code UOM_Code,
	(select	muomv.uom_code
	 from	apps.mtl_units_of_measure_vl muomv
	 where	muomv.uom_code    = rcv_acct.primary_uom_code) UOM_Code,
	-- End revision for version 1.17
	sum(rcv_acct.primary_quantity) Quantity,
	gl.currency_code Currency_Code,
	sum(rcv_acct.amount) Amount
from	org_acct_periods oap,
	pa_projects_all pp,
	gl_code_combinations gcc,
	fnd_lookup_values flv, 
	hr_organization_information hoi,
	hr_all_organization_units_vl haou, -- inv_organization_id
	hr_all_organization_units_vl haou2, -- operating unit
	gl_ledgers gl,
	po_lookup_codes pl,
	-- Revision for version 1.17
	&rcv_sla_tables
	-- ==========================================================================
	-- Use this inline table to fetch the receiving transactions
	-- ==========================================================================
	(select	mp.organization_code organization_code,
		rt.organization_id organization_id,
		rrsl.period_name period_name,
		rrsl.code_combination_id code_combination_id,
		rrsl.rcv_sub_ledger_id rcv_sub_ledger_id,
		msiv.concatenated_segments item_number,
		msiv.description item_description,
		-- Revision for version 1.13
		fcl.meaning item_type,
		-- Revision for version 1.17
		rsl.item_id,
		rsl.to_organization_id,
		pol.category_id,
		-- End revision for version 1.17
		rrsl.accounting_line_type accounting_line_type,
		rt.transaction_type transaction_type,
		-- Revision for version 1.16
		pov.vendor_name,
		pod.destination_type_code destination_type_code,
		-- Fix for version 1.9 and 1.17
		-- poh.segment1 po_num,
		case
		   when :p_show_purchase_order = 'Y' then poh.segment1
		   else null
		end po_num,
		-- pol.line_num po_line,
		case
		   when :p_show_purchase_order = 'Y' then pol.line_num
		   else null
		end po_line,
		-- End of revision for version 1.17
		(select	pr.release_num
		 from	po_releases_all pr
		 where	pr.po_release_id  = rt.po_release_id
		 -- Revision for version 1.16
		 and	:p_show_purchase_order = 'Y') release_num,                                      -- p_show_purchase_order
		-- End fix for version 1.9
		pod.project_id project_id,
		-- Fix for version 1.9
		(select	we.wip_entity_name
		 from	wip_entities we
		 where	we.wip_entity_id  = rt.wip_entity_id
		 -- Revision for version 1.16
		 and	:p_show_wip_osp = 'Y') wip_job,                                                 -- p_show_wip_osp
		(select	br.resource_code
		 from	bom_resources br
		 where	br.resource_id    = rt.bom_resource_id
		 -- Revision for version 1.16
		 and	:p_show_wip_osp = 'Y') bom_resource,                                            -- p_show_wip_osp
		-- End fix for version 1.9
		-- Revision for version 1.14 and 1.17
		-- muomv.uom_code primary_uom_code,
		msiv.primary_uom_code primary_uom_code,
		-- End revision for version 1.17
		decode(sign(nvl(rrsl.accounted_dr,0) - nvl(rrsl.accounted_cr,0)),
		-- =============================================
		-- Fix for version 1.8
		-- SIGN of qty incorrect on CORRECTION transactions
		-- =============================================
		--		 1, rt.primary_quantity, 
		--		-1, -1 * rt.primary_quantity,
				 1,  1 * abs(rt.primary_quantity), 
				-1, -1 * abs(rt.primary_quantity),
		-- End fix for version 1.8
		-- =============================================
				    rt.primary_quantity
			  ) primary_quantity,
		nvl(rrsl.accounted_dr,0) - nvl(rrsl.accounted_cr,0) amount
	 from	rcv_receiving_sub_ledger rrsl,
		-- Fix for version 1.10
		rcv_accounting_events rae,
		rcv_transactions rt,
		rcv_shipment_lines rsl,
		-- Fix for version 1.9
		po_headers_all poh,
		po_lines_all pol,
		-- Fix for version 1.9
		po_distributions_all pod,
		mtl_system_items_vl msiv,
		-- Revision for version 1.17, preventing Expense Receipts to be reported
		-- mtl_units_of_measure_vl muomv,
		mtl_parameters mp,
		-- Revision for version 1.13
		fnd_common_lookups fcl,
		-- Revision for version 1.16
		po_vendors pov
	 -- ========================================================
	 -- Material Transaction, org and item joins
	 -- ========================================================
	 where	rrsl.rcv_transaction_id = rt.transaction_id
	-- Fix for version 1.10
	 and	rae.accounting_event_id = rrsl.accounting_event_id
	 and	rae.rcv_transaction_id  = rt.transaction_id
	-- End fix for version 1.10
	 and	rt.shipment_line_id     = rsl.shipment_line_id
	 -- Expense destinations may not always have an item_id
	 and	rsl.item_id             = msiv.inventory_item_id (+) 
	 and	rsl.to_organization_id  = msiv.organization_id (+)
	 -- Revision for version 1.17, preventing Expense Receipts to be reported
	 -- and	muomv.uom_code             = nvl(msiv.primary_uom_code, rt.uom_code)
	 and	pod.po_distribution_id  = nvl(rt.po_distribution_id, to_number(rrsl.reference3))
	 and	mp.organization_id      = rt.organization_id
	 -- ========================================================
	 -- Fix for version 1.9
	 -- ========================================================
	 and	rt.po_header_id         = poh.po_header_id
	 and	rt.po_line_id           = pol.po_line_id
	 -- ========================================================
	 -- Receiving Transaction date joins
	 -- Fix for version 1.7 and 1.10
	 -- ========================================================
	 and	4=4                       -- p_trx_date_from, p_trx_date_to, p_dest_type_code, p_item_number, p_po_number, p_org_code
	 -- ========================================================
	 -- For Item Type
	 -- Revision for version 1.13
	 -- ========================================================
	 and	fcl.lookup_code  (+)   = msiv.item_type
	 and	fcl.lookup_type  (+)   = 'ITEM_TYPE'
	 -- Revision for version 1.16
	 and	pov.vendor_id        = poh.vendor_id
	) rcv_acct
-- ========================================================
-- Inventory Org accounting period joins
-- ========================================================
-- Revision for version 1.13
where	oap.period_name                  = rcv_acct.period_name
and	oap.organization_id              = rcv_acct.organization_id
-- ========================================================
-- Version 1.3, added lookup values to see more detail
-- ========================================================
and	flv.lookup_type                  = 'RCV TRANSACTION TYPE'
and	flv.lookup_code                  = rcv_acct.transaction_type
and	flv.language                     = userenv('lang')
and	pl.lookup_type                   = 'DESTINATION TYPE'
and	pl.lookup_code                   = rcv_acct.destination_type_code
-- ========================================================
-- Project number join
-- ========================================================
and	rcv_acct.project_id              = pp.project_id (+)
-- ========================================================
-- using the base tables to avoid using
-- org_organization_definitions and hr_operating_units
-- ========================================================
and	hoi.org_information_context      = 'Accounting Information'
and	hoi.organization_id              = rcv_acct.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
and	1=1				 -- p_dest_type_code, p_operating_unit, p_ledger
-- ========================================================
-- Revision for version 1.17, SLA and Non-SLA joins.
-- ========================================================
&rcv_sla_table_joins
&rcv_non_sla_table_joins
-- ==========================================================
group by 
	nvl(gl.short_name, gl.name),
	haou2.name,
	rcv_acct.organization_code,
	oap.period_name,
	&segment_columns_grp
	rcv_acct.item_number,
	rcv_acct.item_description,
	-- Revision for version 1.13
	rcv_acct.item_type,
	-- Revision for version 1.17, for category subquery
	rcv_acct.item_id,
	rcv_acct.to_organization_id,
	rcv_acct.category_id,
         -- End revision for version 1.17
	rcv_acct.accounting_line_type,
	flv.meaning, -- Transaction Type
	-- Revision for version 1.16
	rcv_acct.vendor_name, -- Supplier_Name,
	pl.displayed_field, -- Destination Type
	-- Fix for version 1.9
	rcv_acct.po_num,
	rcv_acct.po_line,
	rcv_acct.release_num,
	-- End fix for version 1.9
	pp.segment1,
	-- Fix for version 1.9
	rcv_acct.wip_job,
	rcv_acct.bom_resource,
	-- End fix for version 1.9
	rcv_acct.primary_uom_code,
	gl.currency_code
-- Revision for version 1.13
-- order by 1,2,3,4,5,6,7,8,9,10,15,16,17,18,19,20,21,22
order by
	nvl(gl.short_name, gl.name), -- Ledger
	haou2.name, -- Operating_Unit
	rcv_acct.organization_code, -- Org_Code
	oap.period_name, -- Period_Name
	&segment_columns_grp
	rcv_acct.item_number, -- Item_Number
	rcv_acct.accounting_line_type, -- Accounting Line Type
	flv.meaning, -- Transaction_Type
	pl.displayed_field
	&order_by_purchase_order
	&order_by_wip_osp
Parameter Name SQL text Validation
Transaction Date From
rae.transaction_date >= :p_trx_date_from and
rrsl.transaction_date >= :p_trx_date_from
Date
Transaction Date To
rae.transaction_date < :p_trx_date_to + 1 and
rrsl.transaction_date < :p_trx_date_to + 1
Date
Show SLA Accounting
xla_distribution_links xdl,
	xla_ae_headers ah,
	xla_ae_lines al,
LOV Oracle
Show Purchase Orders
	decode(:p_show_purchase_order,                                                            -- p_show_purchase_order
			'N', null,
			rcv_acct.po_num) PO_Number,
	decode(:p_show_purchase_order,                                                            -- p_show_purchase_order
			'N', null,
			rcv_acct.po_line) PO_Line,
	decode(:p_show_purchase_order,                                                            -- p_show_purchase_order
			'N', null,
			rcv_acct.release_num) PO_Release,
LOV Oracle
Show WIP Outside Processing
	rcv_acct.wip_job WIP_Job,
	rcv_acct.bom_resource Resource_Code,
LOV Oracle
Category Set 1
select x.* from (
select
'(select min(mcb.'||lower(fifsv.application_column_name)||') keep (dense_rank first order by mcb.category_id) from mtl_item_categories mic, mtl_categories_b mcb where 
  ((mic.category_set_id='||mcsv.category_set_id||' and rcv_acct.to_organization_id=mic.organization_id and rcv_acct.item_id=mic.inventory_item_id and mic.category_id=mcb.category_id)
    or 
   (rcv_acct.category_id=mcb.category_id and rcv_acct.item_id is null)
  )
 ) "'||substr(fifsv.form_left_prompt,1,30)||'",' sql_text
from
mtl_category_sets_vl mcsv,
fnd_id_flex_segments_vl fifsv
where
mcsv.category_set_name=:category_set_name1 and
mcsv.structure_id=fifsv.id_flex_num and
fifsv.application_id=401 and
fifsv.id_flex_code='MCAT'
order by
fifsv.id_flex_num,
fifsv.segment_num
) x
union all
select
'(select min(mct.description) keep (dense_rank first order by mct.category_id) from mtl_item_categories mic, mtl_categories_tl mct where 
  ((mic.category_set_id='||mcsv.category_set_id||' and rcv_acct.to_organization_id=mic.organization_id and rcv_acct.item_id=mic.inventory_item_id and mic.category_id=mct.category_id and mct.language=userenv(''lang''))
    or
   (rcv_acct.category_id=mct.category_id and mct.language=userenv(''lang'')and rcv_acct.item_id is null)
  )
) "'||substr(mcsv.category_set_name||' Description',1,30)||'",' sql_text
from
mtl_category_sets_vl mcsv
where
mcsv.category_set_name=:category_set_name1
LOV
Category Set 2
select x.* from (
select
'(select min(mcb.'||lower(fifsv.application_column_name)||') keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_b mcb where mic.category_set_id='||mcsv.category_set_id||' and rcv_acct.to_organization_id=mic.organization_id and rcv_acct.item_id=mic.inventory_item_id and mic.category_id=mcb.category_id) "'||substr(fifsv.form_left_prompt,1,30)||'",' sql_text
from
mtl_category_sets_vl mcsv,
fnd_id_flex_segments_vl fifsv
where
mcsv.category_set_name=:category_set_name2 and
mcsv.structure_id=fifsv.id_flex_num and
fifsv.application_id=401 and
fifsv.id_flex_code='MCAT'
order by
fifsv.id_flex_num,
fifsv.segment_num
) x
union all
select
'(select min(mct.description) keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_tl mct where mic.category_set_id='||mcsv.category_set_id||' and rcv_acct.to_organization_id=mic.organization_id and rcv_acct.item_id=mic.inventory_item_id and mic.category_id=mct.category_id and mct.language=userenv(''lang'')) "'||substr(mcsv.category_set_name||' Description',1,30)||'",' sql_text
from
mtl_category_sets_vl mcsv
where
mcsv.category_set_name=:category_set_name2
LOV
Supplier Name
pov.vendor_name = :p_supplier_name
LOV
PO Number
poh.segment1 = :p_po_number
LOV
Destination Code
pl.displayed_field = :p_dest_type_code
LOV
Item Number
msiv.concatenated_segments = :p_item_number
LOV
Organization Code
mp.organization_code = :p_org_code
LOV
Operating Unit
haou2.name = :p_operating_unit
LOV
Ledger
gl.name = :p_ledger
LOV
Blitz Report™